Archive | June, 2012

Syncing SQL Server databases ready for DB Mirroring

Whilst Database mirroring is now deprecated with the release of SQL Server 2012 (http://msdn.microsoft.com/en-us/library/ms143729%28v=sql.110%29.aspx) it’s still something I’m going to be using heavily with 2008 and 2008R2 as it’s a simple way of implementing failover between discrete clusters or instances. They’ll move to Availability groups eventually, but not in the short term as there’s no direct way to do this without building a new Windows cluster that contains the Fallover Clusters

However, with a database with frequent transaction log backups (and if it’s important enough to mirror, it’s almost certainly important enough to deserver frequent log backups) it can take a bit of work to get everything synced up again. Or catching back up if you’ve had to break the mirror for any reason.

The first script is to sync up from scratch. It connects back to the primary server to get the back information. It assumes that:

  • You’re running it as an account that exists on both instances with sysadmin role granted
  • You don’t already have a linked server with the name of the primary instance on the secondary
  • The file structure is identical on the 2 instance
  • All backup files (log and db) are available in their main location (ie; not taken to tape and deleted)

declare @last_lsn numeric(25,0), @first_lsn numeric(25,0), @@tmp_lsn numeric(25,0) declare @dbname varchar(50), @restorefile varchar(255), @oserver nvarchar(50), @lserver nvarchar(50)

--Set server name twice. First is used for building db links, 2nd is used in the actual query to hide special characters set @oserver='server1\instance' set @lserver='[server1\instance]' set @dbname='bigdb1'

EXEC master.dbo.sp_addlinkedserver @oserver, @srvproduct=@oserver, @provider=N'SQLNCLI', @datasrc=@oserver EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@oserver,@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

exec(N'select c.physical_device_name, a.last_lsn, a.first_lsn into ##mirror from '+@lserver+'.msdb.dbo.backupset a, '+@lserver+'.msdb.dbo.backupmediaset b, '+@lserver+'.msdb.dbo.backupmediafamily c where a.database_name='''+@dbname+''' and a.media_set_id=b.media_set_id and b.media_set_id=c.media_set_id and a.last_lsn > (select max(first_lsn) from '+@lserver+'.msdb.dbo.backupset where database_name='''+@dbname+''' and type=''D'') order by last_lsn asc') declare restore_cursor cursor for select physical_device_name, last_lsn, first_lsn from ##mirror order by last_lsn asc open restore_cursor fetch next from restore_cursor into @restorefile, @last_lsn ,@first_lsn while @@FETCH_STATUS = 0 begin --comment out the exec line and uncomment the print line if you want to check the restore commands first

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike