Agent Jobs

Automatically Create Instance Names

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:
SQLSHARE01
SQLSHARE02
SQLSHARE03
SQLSHARE04
SQLSHARE05…

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.

mikesdatawork_multi_instance_sql_server

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

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s