# Configure SQL Mirroring sharepoint

- Link: https://sharepointtechnicalsupport.com/2015/05/02/configure-sql-mirroring/
- Published: 2015-05-02T14:22:39+05:30
- Author: Deviprasad Panda

There are 3 different types of SQL Mirroring options available for use:

 1. High Performance – Asynchronous (this requires the Enterprise Edition)
 2. High Safety without Automatic Failover
 3. High Safety with Automatic Failover (this requires a witness server running the
    same SQL version)

All three require roughly the same steps:

a.Ensure the same login accounts are present on the Principal and the Mirror
 b.
Backup the Principal DB and Logs c. Restore on the Mirror d. Configure the Mirroring
Endpoints e. Set the partner on the Mirror f. Set the partner on the Principal (
and the witness if necessary)

So lets work through the steps above……

#### **Get our login’s in order**

To get our accounts in order between our two servers we can run the following SQL
script to give us an output to run on our mirror.

SELECT ‘create login [‘ + p.name + ‘] ‘ + case when p.type in(‘U’,’G’) then ‘from
windows ‘ else ” end + ‘with ‘ + case when p.type = ‘S’ then ‘password = ‘ + master.
sys.fn_varbintohexstr(l.password_hash) + ‘ hashed, ‘ + ‘sid = ‘ + master.sys.fn_varbintohexstr(
l.sid) + ‘, check_expiration = ‘ + case when l.is_policy_checked > 0 then ‘ON, ‘
else ‘OFF, ‘ end + ‘check_policy = ‘ + case when l.is_expiration_checked > 0 then‘
ON, ‘ else ‘OFF, ‘ end + case when l.credential_id > 0 then ‘credential = ‘ + c.
name + ‘, ‘ else ” end else ” end + ‘default_database = ‘ + p.default_database_name
+ case when len(p.default_language_name) > 0 then ‘, default_language = ‘ + p.default_language_name
else ” end FROM sys.server_principals p LEFT JOIN sys.sql_logins l ON p.principal_id
= l.principal_id LEFT JOIN sys.credentials c ON  l.credential_id = c.credential_id
WHERE p.type in(‘S’,’U’,’G’) AND p.name <> ‘sa’ AND p.name NOT LIKE ‘##%’

Once executed run the output on the mirror server to create all the logins.

#### **Backup the Primary and Restore on the mirror**

We can run the below script on primary to take a file and log backup of all our 
user databases

DECLARE @name VARCHAR(4000) — database name DECLARE @path VARCHAR(4000) — path for
backup files  DECLARE @fileName VARCHAR(4000) — filename for backup  DECLARE @logfileName
VARCHAR(4000) — logfilename for backup  DECLARE @fileDate VARCHAR(20) — used for
file name declare @sql nvarchar(4000)     SET @path = ‘C:SQLbackups’

SELECT @fileDate = ‘InitialMirror’

DECLARE db_cursor CURSOR FOR  SELECT name FROM master.dbo.sysdatabases WHERE name
NOT IN (‘master’,’model’,’msdb’,’tempdb’)  –WHERE name NOT IN (‘master’,’model’,’
msdb’,’tempdb’,’INCLUDE YOUR DB’s)’

CREATE TABLE #TEMPRESTORE ( CMD VARCHAR(400) ) OPEN db_cursor   FETCH NEXT FROM 
db_cursor INTO @name;        WHILE @@FETCH_STATUS = 0   BEGIN          SET @fileName
= @path + @name + ‘_’ + @fileDate + ‘.BAK’         SET @logfileName = @path + @name
+ ‘_’ + @fileDate + ‘_Log.BAK’                              SET @sql = ‘ALTER DATABASE“‘
+ @name + ‘” SET RECOVERY FULL’         exec sys.sp_executesql @sql;

BACKUP DATABASE @name TO DISK = @fileName with format;         BACKUP log @name 
TO DISK = @logfileName with format;                 INSERT INTO #TEMPRESTORE VALUES(‘
RESTORE DATABASE “‘+@name+’”        FROM DISK = ”’+@fileName+”’ WITH NORECOVERY’)
INSERT INTO #TEMPRESTORE VALUES (‘go’)        INSERT INTO #TEMPRESTORE VALUES (‘
RESTORE LOG “‘+@name+’”        FROM DISK = ”’+@logfileName+”’ WITH NORECOVERY’) 
INSERT INTO #TEMPRESTORE VALUES (‘go’)        FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor   DEALLOCATE db_cursor SELECT * FROM #TEMPRESTORE DROP TABLE #TEMPRESTORE

The output of this script will provide you with the restore commands to run on your
mirror.  Just make sure you copy all the SQL backup files from the location specifed
at the top of the script to the exact same location on your mirror.

#### **Configure the mirror endpoints and setting up the mirror**

Right click on a database you want to mirror

[⌊sql tasks mirror⌉⌊sql tasks mirror⌉[

sql tasks mirror

[⌊configuring database mirroring secirity wizard⌉⌊configuring database mirroring
secirity wizard⌉[

configuring database mirroring secirity wizard

[⌊include withness server Database Mirroring⌉⌊include withness server Database Mirroring⌉[

include withness server Database Mirroring

[⌊choose server to configure sqlmirror⌉⌊choose server to configure sqlmirror⌉[

choose server to configure sqlmirror

[⌊principal server instance sqlmirror⌉⌊principal server instance sqlmirror⌉[

principal server instance sqlmirror

[⌊mirror server instance sqlmirror⌉⌊mirror server instance sqlmirror⌉[

mirror server instance sqlmirror

[⌊withness server instance sqlmirror⌉⌊withness server instance sqlmirror⌉[

withness server instance sqlmirror

[⌊service account sqlmirror mirror⌉⌊service account sqlmirror mirror⌉[

service account sqlmirror mirror

[⌊configuring endpoints sqlmirror⌉⌊configuring endpoints sqlmirror⌉[

configuring endpoints sqlmirror

[⌊finish sqlmirror⌉⌊finish sqlmirror⌉[

finish sqlmirror

#### **Configure further databases to mirror**

You can either carryout the wizard above again or run the following script on the
mirror.

DECLARE @name VARCHAR(4000) — database name  DECLARE @sql nvarchar(4000) DECLARE@
mirrorendpoint varchar(4000)

SET @mirrorendpoint =’TCP://primaryserverFQDN:5022′ SET @name = ‘DatabaseNameToMirror’

SET @sql = ‘ALTER DATABASE “‘ + @name + ‘” SET PARTNER = ”’+@mirrorendpoint+”” exec
sys.sp_executesql @sql;

then run this on the primary

DECLARE @name VARCHAR(4000) — database name

DECLARE @sql nvarchar(4000)

DECLARE @mirrorendpoint varchar(4000)

DECLARE @witnessendpoint varchar(4000)

SET @mirrorendpoint =’TCP://yourmirrorFQDN:5022′

SET @witnessendpoint =’TCP://yourwitnessFQDN:5022′

SET @name = ‘DatabaseNameToMirror’

SET @sql = ‘ALTER DATABASE “‘ + @name + ‘” SET PARTNER = ”’+@mirrorendpoint+”” exec
sys.sp_executesql @sql; SET @sql = ‘ALTER DATABASE “‘ + @name + ‘” SET WITNESS =”’
+@witnessendpoint+”” exec sys.sp_executesql @sql;

### Share this:

 * [  X ](https://sharepointtechnicalsupport.com/2015/05/02/configure-sql-mirroring/?share=twitter)
 * [  Facebook ](https://sharepointtechnicalsupport.com/2015/05/02/configure-sql-mirroring/?share=facebook)
 * [  WhatsApp ](https://sharepointtechnicalsupport.com/2015/05/02/configure-sql-mirroring/?share=jetpack-whatsapp)
 * [  Email ](https://sharepointtechnicalsupport.com/2015/05/02/configure-sql-mirroring/?subject=%5BShared%20Post%5D%20Configure%20SQL%20Mirroring%20sharepoint&body=https%3A%2F%2Fsharepointtechnicalsupport.com%2F2015%2F05%2F02%2Fconfigure-sql-mirroring%2F&share=email)
 * [  Telegram ](https://sharepointtechnicalsupport.com/2015/05/02/configure-sql-mirroring/?share=telegram)
 * [  LinkedIn ](https://sharepointtechnicalsupport.com/2015/05/02/configure-sql-mirroring/?share=linkedin)
 * [  Tumblr ](https://sharepointtechnicalsupport.com/2015/05/02/configure-sql-mirroring/?share=tumblr)
 * [  Pinterest ](https://sharepointtechnicalsupport.com/2015/05/02/configure-sql-mirroring/?share=pinterest)

### Like this:

Like Loading…

### _Related_