Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Scripted Setup for Mirroring from the Principal

Author  Topic 

Julien.Crawford
Starting Member

21 Posts

Posted - 2011-09-13 : 19:21:43
So, I wanted to script the setup of mirroring in our Dev/QA environments so I could build it as needed.
This in turn lead me to want to apply the same script in production - so everything matches. Fine.

I was really annoyed that the SQL Server Managment Studio GUI means did not allow me to capture the SQL with their 'script to' feature.

So, after failing a lot and breaking things a lot, I finally managed to build a script that is 'mostly' generic. There are still some points where its hard coded and if you use this, you will need to
tweak these to use your user name etc.

This runs Only on the principal (or the machine that will be the principal).
Fill in the sections at the beginning with your data.

DISCLAIMER :: This is not a complete generic tool for you to use 'out of the box'. Its just what I use and I felt it had lots of useful things in it.



-- *
-- * Just to prevent accidental F5's
-- *
RAISERROR('*** Explicit Disconnect - Careful what you run.', 20, 0) with log
go

-----------------------------------------------------------------------------------------
-- Start of Data Entry Section.
-----------------------------------------------------------------------------------------
-- *
-- * Specify Principal, Mirror and Witness and store in TEMPDB.
-- *
if (object_id('tempdb..#mirroring_info') is not NULL)
drop table #mirroring_info
go
select 'WIN2008R2' principal_host, 'WIN2008R2\SMRT01' principal_instance
, 'WIN2008' mirror_host, 'WIN2008\SMRT02' mirror_instance
, 'WIN2008R2' witness_host, 'WIN2008R2\smart02witness' witness_instance
, 'E:\SMART02\Data' mirror_data
, 'E:\SMART02\Log' mirror_log
, '\\WIN2008\C$\Temp\SMRT01' backup_to
, 'C:\Temp\SMRT01' backup_from
into #mirroring_info
go

-- *
-- * The Databases to
-- *
if (object_id('tempdb..#databases') is not NULL)
drop table #databases
go
create table #databases (name sysname)
go
insert into #databases select 'dba'
insert into #databases select 'dwpd'
insert into #databases select 'dwpd_2011'
insert into #databases select 'speed_router'
insert into #databases select 'stg2'
go

-----------------------------------------------------------------------------------------
-- End of Data Entry Section.
-----------------------------------------------------------------------------------------
if (object_id('tempdb..#filelist') is not NULL)
drop table #filelist
go
CREATE TABLE #filelist
(
LogicalName NVARCHAR (128),
PhysicalName NVARCHAR (260),
Type NCHAR (1),
FileGroupName NVARCHAR (128),
Size BIGINT,
MaxSize BIGINT,
FileId BIGINT,
CreateLSN NUMERIC (25, 0),
DropLSN NUMERIC (25, 0),
UniqueId UNIQUEIDENTIFIER,
ReadOnlyLSN NUMERIC (25, 0),
ReadWriteLSN NUMERIC (25, 0),
BackupSizeInBytes BIGINT,
SourceBlockSize INT,
FileGroupId INT,
LogGroupGUID UNIQUEIDENTIFIER,
DifferentialBaseLSN NUMERIC (25, 0),
DifferentialBaseGUID UNIQUEIDENTIFIER,
IsReadOnly BIT,
IsPresent BIT,
TDEThumbprint VARBINARY,
id int identity
)
go


-- *
-- * If we are NOT connected to the principal then stop (ie: Disconnect).
-- *
if @@servername <> (select principal_instance from #mirroring_info)
begin
RAISERROR('Explicit Disconnect - You must be connected to the PRINCIPAL for this operation.', 20, 0) with log
end
go
USE master
go

-- *
-- * If there is No linked server, create it now.
-- *
if not exists (select * from master..sysservers where srvname = 'SOR_MIRROR')
begin
declare @provstr_s nvarchar(512)
select @provstr_s = 'DRIVER={SQL Server};SERVER='+mirror_instance+';Integrated Security=SSPI;'
from #mirroring_info

EXEC sp_addlinkedserver @server = 'SOR_MIRROR', @srvproduct = '', @provider = 'MSDASQL', @provstr = @provstr_s
end
GO

if not exists (select * from master..sysservers where srvname = 'SOR_WITNESS')
begin
declare @provstr_s nvarchar(512)
select @provstr_s = 'DRIVER={SQL Server};SERVER='+witness_instance+';Integrated Security=SSPI;'
from #mirroring_info

EXEC sp_addlinkedserver @server = 'SOR_WITNESS', @srvproduct = '', @provider = 'MSDASQL', @provstr = @provstr_s
end
GO


-- *
-- * Remove Any mirroring and Restoring status from the principal!
-- *
declare @dbname sysname
set @dbname = ''
while 1=1
begin
select @dbname = min(name) from #databases where name > @dbname
if @dbname is null
break

if exists (select * from master.sys.database_mirroring where database_id = db_id(@dbname) and mirroring_witness_name is not null)
exec ('ALTER DATABASE ['+@dbname+'] SET WITNESS OFF')

if exists (select * from master.sys.database_mirroring where database_id = db_id(@dbname) and mirroring_partner_name is not null)
exec ('ALTER DATABASE ['+@dbname+'] SET PARTNER OFF')

end
go

declare @dbname sysname
set @dbname = ''
while 1=1
begin
select @dbname = min(name) from #databases where name > @dbname
if @dbname is null
break

if exists (select * from master.sys.databases where database_id = db_id(@dbname) and state_desc = 'RESTORING')
exec ('RESTORE LOG ['+@dbname+'] WITH RECOVERY')
end
go



-- *
-- * On Principal
-- * backup databases and logs directly to the MIRROR host, ready for recovery!
-- *
declare @filename nvarchar(512)
declare @dbname sysname
set @dbname = ''
while 1=1
begin
select @dbname = min(name) from #databases where name > @dbname
if @dbname is null
break

select @filename = backup_to + '\\' + @dbname + '.bak' from #mirroring_info

exec ('BACKUP DATABASE ['+@dbname+'] TO DISK = '''+@filename+''' WITH NOFORMAT, INIT, NAME = N'''+@dbname+'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10')
exec ('BACKUP LOG ['+@dbname+'] TO DISK = '''+@filename+''' WITH NOFORMAT, NOINIT,NAME = N'''+@dbname+'-Tran Log Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10')
end
go

-- *
-- * On Mirror
-- * turn off mirroring on databases
-- * drop databases
-- *
declare @filename nvarchar(512)
declare @dbname sysname
set @dbname = ''
while 1=1
begin
select @dbname = min(name) from #databases where name > @dbname
if @dbname is null
break

-- Can not drop the database if its busy mirroring!
exec ('SELECT * FROM OPENQUERY([SOR_MIRROR], ''if exists (select * from master.sys.database_mirroring where database_id = db_id('''+@dbname+''') and mirroring_partner_name is not null) ALTER DATABASE ['+@dbname+'] SET PARTNER OFF; select @@error'')')
exec ('SELECT * FROM OPENQUERY([SOR_MIRROR], ''DROP DATABASE ['+@dbname+']; select @@error'')')
end
go



-- *
-- * restore databases and log of databases -- Ensuring any File Moves are kept!
-- *
declare @sql varchar(8000)
declare @dbname sysname
declare @backupfrom sysname
declare @mirror_data sysname
declare @mirror_log sysname
set @dbname = ''
while 1=1
begin
select @dbname = min(name) from #databases where name > @dbname
if @@rowcount = 0 or @dbname is null
break

-- Build the table of files needed for all the MOVE's
select @backupfrom = backup_from
, @mirror_data = mirror_data
, @mirror_log = mirror_log
from #mirroring_info

select @sql = 'RESTORE FILELISTONLY FROM DISK = '''+@backupfrom+'\'+@dbname+'.bak'''

truncate table #filelist
insert into #filelist exec (@sql)
-- select * from #filelist

-- Now start building the command.
select @sql = 'RESTORE DATABASE ['+@dbname+'] FROM DISK = N'''+@backupfrom+'\'+@dbname+'.bak'' WITH FILE = 1'

-- Now loop through the Files.
declare @fileId bigint
declare @PhysicalName nvarchar(260)
declare @LogicalName nvarchar(260)
declare @Type nchar(1)
declare @id int
set @id = 1
while 1=1
begin
select @fileId = fileId
, @PhysicalName = PhysicalName
, @LogicalName = LogicalName
, @Type = Type
from #filelist
where id = @id

if (@@rowcount = 0 or @fileId is null)
break

-- Just the name of the file without the path please.
set @PhysicalName = reverse(@PhysicalName)
set @PhysicalName = substring(@PhysicalName, 0, charindex('\', @PhysicalName, 1))
set @PhysicalName = reverse(@PhysicalName)

if (@Type = 'D')
set @sql = @sql + ', MOVE N'''+@LogicalName+''' TO N'''+@mirror_data+'\'+@PhysicalName+''''
else
set @sql = @sql + ', MOVE N'''+@LogicalName+''' TO N'''+@mirror_log+'\'+@PhysicalName+''''

set @id = @id + 1
end
set @sql = @sql + ', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10'


set @sql = 'SELECT * FROM OPENQUERY([SOR_MIRROR], ''' + replace(@sql,'''', '''''') + '; select @@error'')'
print @sql
exec (@sql)

set @sql = 'SELECT * FROM OPENQUERY([SOR_MIRROR], ''RESTORE LOG ['+@dbname+'] FROM DISK = N'''''+@backupfrom+'\'+@dbname+'.bak'''' WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 10; select @@error'')'
print @sql
exec (@sql)

end
go

-- *
-- * Drop all the EndPoints so we can re-create them.
-- *
declare @MirrorName varchar(255)

SELECT @MirrorName = name FROM OPENQUERY([SOR_MIRROR], 'select name from sys.tcp_endpoints where type_desc = ''DATABASE_MIRRORING''')
if (@MirrorName is not null)
exec ('SELECT * FROM OPENQUERY([SOR_MIRROR], ''DROP ENDPOINT ['+@MirrorName+']; select @@error'')')

SELECT @MirrorName = name FROM OPENQUERY([SOR_WITNESS], 'select name from sys.tcp_endpoints where type_desc = ''DATABASE_MIRRORING''')
if (@MirrorName is not null)
exec ('SELECT * FROM OPENQUERY([SOR_WITNESS], ''DROP ENDPOINT ['+@MirrorName+']; select @@error'')')

SELECT @MirrorName = name from sys.tcp_endpoints where type_desc = 'DATABASE_MIRRORING'
if (@MirrorName is not null)
exec ('DROP ENDPOINT ['+@MirrorName+']')
go

-- *
-- * Create the EndPoints (if necessary)
-- * 1st on the mirror.
declare @sql varchar(8000)
declare @EndPointName varchar(100)
select @EndPointName = name from [SOR_MIRROR].master.sys.endpoints where type_desc = 'DATABASE_MIRRORING'
if (@EndPointName is null)
begin
print '[SOR_MIRROR] : CREATE ENDPOINT [Mirroring]'
set @sql = 'SELECT * FROM OPENQUERY([SOR_MIRROR],''CREATE ENDPOINT [Mirroring] AUTHORIZATION [ASIAPAC\jxc] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4); select @@error'')'
exec (@sql)
set @sql = 'SELECT * FROM OPENQUERY([SOR_MIRROR],''GRANT TAKE OWNERSHIP ON ENDPOINT::Mirroring TO [ASIAPAC\ap-sqldbeng] WITH GRANT OPTION; select @@error'')'
exec (@sql)
set @sql = 'SELECT * FROM OPENQUERY([SOR_MIRROR],''GRANT CONNECT ON ENDPOINT::[Mirroring] TO [asiapac\ap-sqldbeng]; select @@error'')'
exec (@sql)
end

-- * 2nd on the witness.
select @EndPointName = name from [SOR_WITNESS].master.sys.endpoints where type_desc = 'DATABASE_MIRRORING'
if (@EndPointName is null)
begin
print '[SOR_WITNESS] : CREATE ENDPOINT [Mirroring]'
exec ('SELECT * FROM OPENQUERY([SOR_WITNESS],''CREATE ENDPOINT [Mirroring] AUTHORIZATION [ASIAPAC\jxc] STATE=STARTED AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = WITNESS, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4); select @@error'')')
exec ('SELECT * FROM OPENQUERY([SOR_WITNESS],''GRANT TAKE OWNERSHIP ON ENDPOINT::Mirroring TO [ASIAPAC\ap-sqldbeng] WITH GRANT OPTION; select @@error'')')
exec ('SELECT * FROM OPENQUERY([SOR_WITNESS],''GRANT CONNECT ON ENDPOINT::[Mirroring] TO [asiapac\ap-sqldbeng]; select @@error'')')
end

-- * 3rd on the principal.
if not exists (select * from master.sys.endpoints where type_desc = 'DATABASE_MIRRORING')
begin
print 'principal : CREATE ENDPOINT [Mirroring]'
exec ('CREATE ENDPOINT [Mirroring] AUTHORIZATION [ASIAPAC\jxc] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)')
exec ('GRANT TAKE OWNERSHIP ON ENDPOINT::Mirroring TO [ASIAPAC\ap-sqldbeng] WITH GRANT OPTION')
exec ('GRANT CONNECT ON ENDPOINT::[Mirroring] TO [asiapac\ap-sqldbeng]')
end
go


-- *
-- * Configure MIRRORing!
-- * On mirror 1st!!! This is important!
-- *
declare @domain nvarchar(512)
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\Tcpip\Parameters','Domain', @domain OUTPUT

declare @principal_host varchar(512)
select @principal_host = principal_host from #mirroring_info

declare @partner nvarchar(512)
SELECT @partner = 'TCP://' + @principal_host + '.' + @domain
+ ':' + convert(varchar(10),port)
FROM master.sys.tcp_endpoints where type_desc = 'DATABASE_MIRRORING'
-- select @partner


declare @sql varchar(1024)
declare @dbname sysname
set @dbname = ''
while 1=1
begin
select @dbname = min(name) from #databases where name > @dbname
if @dbname is null
break

set @sql = 'SELECT * FROM OPENQUERY([SOR_MIRROR], ''ALTER DATABASE ['+@dbname+'] SET PARTNER = '''''+@partner+'''''; select @@error'')'
print @sql
exec (@sql)
end
go


-- *
-- * Configure MIRRORing!
-- * On principal 2nd!! This is important!
-- *
declare @domain nvarchar(512)
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\Tcpip\Parameters','Domain', @domain OUTPUT

declare @mirror_host nvarchar(512)
declare @witness_host nvarchar(512)
select @mirror_host = mirror_host, @witness_host = witness_host from #mirroring_info

declare @mirror nvarchar(512)
SELECT @mirror = 'TCP://' + @mirror_host + '.' + @domain + ':' + convert(varchar(10),port)
FROM [SOR_MIRROR].master.sys.tcp_endpoints where type_desc = 'DATABASE_MIRRORING'


declare @witness nvarchar(512)
SELECT @witness = 'TCP://' + @witness_host + '.' + @domain + ':' + convert(varchar(10),port)
FROM [SOR_WITNESS].master.sys.tcp_endpoints where type_desc = 'DATABASE_MIRRORING'
-- select @mirror, @witness


declare @sql varchar(1024)
declare @dbname sysname
set @dbname = ''
while 1=1
begin
select @dbname = min(name) from #databases where name > @dbname
if @dbname is null
break

set @sql = 'ALTER DATABASE ['+@dbname+'] SET PARTNER = ''' + @mirror + ''''
print @sql
exec (@sql)

set @sql = 'ALTER DATABASE ['+@dbname+'] SET WITNESS = ''' + @witness + ''''
print @sql
exec (@sql)
end
go


sqldba23
Starting Member

1 Post

Posted - 2015-03-10 : 04:26:00
Hi Julien, thanks for the above code. When trying to use your script, I get : Msg 11527, Level 16, State 1, Procedure sp_describe_first_result_set, Line 280
The metadata could not be determined because statement 'RESTORE DATABASE [_XYZ] FROM DISK = N'\\dummy-sql-1\SQL_Backups\vs-sql-admin\_XYZ.bak' WITH FILE = 1' does not support metadata discovery. Any ideas how to resolve this error?
Go to Top of Page
   

- Advertisement -