SQL Mirroring Setup Made Easy
In an ongoing effort to simplify mirroring I've decided to post the scripts I use to setup mirroring for myself. There are 9 easy steps to execute. The trick with most of these commands is that they output a script that you will need to copy into the query window and execute on the appropriate server. The scripts assume that the entire SQL server is dedicated to SharePoint and will create a command for every database on the server except for the system databases (Master, Model, MSDB, and Temp... All databases with an ID > 4)
Make sure to replace the variables where necessary. Read the comments throughout for guidance.
--
-- Step 1 -- Run this command on the principal, mirror, and witness
--
-- Create the endpoint
CREATE ENDPOINT Endpoint_Mirroring -- name
STATE=STARTED -- Endpoint will be started and ready
AS TCP(LISTENER_PORT=5022 -- Endpoint will use port 5022
, LISTENER_IP=ALL) -- Endpoint will listen on all IP addresses
FOR DATABASE_MIRRORING -- Specifies mirroring as the endpoint purpose
(AUTHENTICATION = WINDOWS[Negotiate] -- Will negotiate NTLM or Kerberos for authentication
, ENCRYPTION = SUPPORTED, -- Mirroring traffic will be encrypted
ROLE=ALL); -- This endpoint can be principal, mirror, or a witness
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
-- Step 2 -- Run on Principal
--
-- Set all the databases to use full recovery.
-- Caution - Full recovery requires additional logging space
-- Run this on the principal BEFORE backup
-- Select the output of this query, paste, and run in the query window to execute.
-- this query will set all databases to use full recovery except system databases (master, temp, msdb, and model)
Select 'USE MASTER; ALTER DATABASE [' + name + '] SET RECOVERY FULL;' from sys.databases where database_id > 4
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
-- Step 3 -- Run on principal
--
-- Backup all databases
-- Select the output of this query, paste, and run in the query window to execute.
-- This will backup all databases on the server except the system databases (master, temp, msdb, and model)
-- Make sure all databases are in full recovery mode before backup
-- Replace <BackupLocation> with the appropriate local backup path. Format C:\dir\
SELECT 'BACKUP DATABASE [' + name + '] TO DISK = ''<BackupLocation>' + name + '.bak'' WITH FORMAT;' from sys.databases where database_id > 4
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
-- Step 4 -- Run on principal
--
-- Backup logs
-- Select the output of this query, paste, and run in the query window to execute.
-- This will backup the log for all databases on the server except the system databases (master, temp, msdb, and model)
-- Replace <BackupLocation> with the appropriate local backup path. Format C:\dir\
SELECT 'BACKUP LOG [' + name + '] TO DISK = ''<BackupLocation>log_' + name + '.bak'' WITH FORMAT;' from sys.databases where database_id > 4
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
-- Step 5 -- Run on the query generator on the principal
-- Run the actual queries on the mirror server
-- Make sure to copy the backups to the mirror before restoring.
--Restore Databases
-- Select the output of this query, paste, and run in the query window to execute.
-- This will restore all databases from the principal.
-- Replace <BackupLocation> with the appropriate local backup path. Format C:\dir\
SELECT 'RESTORE DATABASE [' + name + '] FROM DISK = ''<BackupLocation>' + name + '.bak'' WITH NORECOVERY;' from sys.databases where database_id > 4
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
-- Step 6 -- Run on the mirror server
-- Make sure to copy the log backups to the mirror before restoring.
--Restore logs
-- Select the output of this query, paste, and run in the query window to execute.
-- This will restore all logs from the principal.
-- Replace <BackupLocation> with the appropriate local backup path. Format C:\dir\
SELECT 'RESTORE LOG [' + name + '] FROM DISK = ''<BackupLocation>log_' + name + '.bak'' WITH NORECOVERY;' from sys.databases where database_id > 4
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
-- Step 7 -- Run on the mirror server FIRST!
--
-- Replace <principalserverfqdn> with the fully qualified domain name of the principal server
-- Select the output of this query, paste, and run in the query window to execute.
SELECT 'ALTER DATABASE [' + name + '] SET PARTNER = 'TCP://<principalserverfqdn>:5022';' from sys.databases where database_id > 4
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
-- Step 8 -- Run on the principal server. Run After Step 7!
--
-- Replace <mirrorserverfqdn> with the fully qualified domain name of the mirror server
-- Select the output of this query, paste, and run in the query window to execute.
SELECT 'ALTER DATABASE [' + name + '] SET PARTNER = 'TCP://<mirrorserverfqdn>:5022';' from sys.databases where database_id > 4
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
-- Step 9 -- Run on the principal server, Run After Step 8!
--
-- Replace <witnessserverfqdn> with the fully qualified domain name of the witness server
-- Select the output of this query, paste, and run in the query window to execute.
SELECT 'ALTER DATABASE [' + name + '] SET WITNESS = 'TCP://<witnessserverfqdn>:5022';' from sys.databases where database_id > 4
-- That's it!
-- High Availability mirroring is now setup.
Comments
Anonymous
July 30, 2008
PingBack from http://www.travel-hilarity.com/airline_travel/?p=6104Anonymous
July 30, 2008
In an ongoing effort to simplify mirroring I've decided to post the scripts I use to setup mirroringAnonymous
January 22, 2010
The comment has been removed