Here’s some SQL Logic to get a list of all instance names, and automatically create the next instance name.
In this environment I have several installations of SQL Server. I want to get the next instance name automatically so I can throw that into a build script and run that from a Job which installs and configures the next instance.
Here’s a list of the existing SQL Instances. I’m using the SQLSHARE* as for all the instances in the multi-instance database server.
I have a default instance already for SQL Server: MSSQLSERVER, then I have several more instances called:
What I want to do is get the next Instance Name automatically so I use this little piece of logic to produce the next name in the list.
use master; set nocount on -- create table to hold instance names declare @sql_instances table ( [id] int identity(0,1) , [rootkey] nvarchar(255) , [sql_instances] nvarchar(255) , [value] nvarchar(255) ) -- get instances names using xp_regread insert into @sql_instances ([rootkey], [sql_instances], [value]) execute xp_regread @rootkey = 'hkey_local_machine' , @key = 'software\microsoft\microsoft sql server' , @value_name = 'installedinstances' select [sql_instances] from @sql_instances -- set prefix name for multi-instance environment aka: Shared SQL Environment "SQLSHARE" -- produce the next instance name. declare @prefix varchar(255) = 'SQLSHARE' declare @next_instance varchar(255) = ( select case when cast(right(max([sql_instances]), 2) as int) < 10 then @prefix + '0' + cast(cast(right(max([sql_instances]), 2) as int) + 1 as varchar) else @prefix + cast(cast(right(max([sql_instances]), 2) as int) + 1 as varchar) end as [sql_instances] from @sql_instances si where si.[id] > 0 ) select (@next_instance)
Here’s the result:
SQLSHARE06 naturally represents the next in-line Instance Name. I’ll work this into a build script so this can be carried out automatically by running a Job.
Categories: Agent Jobs, Alerts, AlwaysOn, Automations, Backup & Restore, Big Data, Cluster, Command Prompt, Configuration, Database Mail, Database Mirroring, ELI5, Error Solutions, Failover, Failover Cluster, Featured, Folder System, Forensics & Auditing, High Availability, How To, Installations, Long Scripts, Management Studio, Mirroring, Notification, Powershell, Quick Fix, Scripts, Security, Short Scripts, SQL Stuff, Top, TSQL, Work Arounds