Basics of Database Mirroring? Step by Step for Configuring Database Mirroring in SQL Server
- What versions of SQL Server is database mirroring available in?
- SQL Server 2005, 2008, 2008R2, and 2012
- What editions of SQL Server is high safety (synchronous) available in?
(Synchronous mirroring by SQL Server edition)
- What editions of SQL Server is high performance (asynchronous) available in?
(Asynchronous mirroring by SQL Server edition)
- Do the principal and the mirror need to be on the same version?
- You can have the principal be one version – say 2008R2 – and the
mirror another – say 2012. This is how an upgrade with minimal downtime
can be accomplished using mirroring! However, once you fail over to the
mirror, you can no longer fail back.
- Do the principal and the mirror need to be on the same edition?
- To be fully supported by Microsoft, yes.
- Can multiple databases on the same instance be in mirroring sessions?
- Yes, but there are limitations, based on the hardware of your servers.
- How do I mirror the system database (master, model, msdb, tempdb)?
- You can’t! This isn’t supported.
- If I set up mirroring, do I still have to take full, differential, or transaction log backups?
- Yes! Mirroring increases the availability of your databases. It is not a substitute for regular backups, however.
- How does index maintenance (rebuilds) affect the mirror?
- Transactions that fill up the log can affect the performance of
mirroring. When the amount of information in the log increases, the
amount of information that needs to be sent to and committed to the
mirror increases also. If performance is crucial, you may want to do
index maintenance more frequently, so it takes less time. If large
transactions such as batch inserts are affecting performance, break
those into smaller transactions.
Step by Step Demo:
I am going to outline my environment and then walk through the
process of setting up Database Mirroring. This will include the
configurations, backups, restores and verification process. Let's jump
in.
My test environment consists of two separate VM's running
VM Workstation with Windows 2008 R2 Datacenter Edition and SQL Server
2008 R2 Enterprise named appropriately Principal and Mirror. The SQL
Server and SQL Server Agent Services accounts are running as domain
users (DOMAIN\User). Windows Firewall is OFF for the sake of this
example.
I created a database on the Principal SQL Server instance and named it TestMirror. The recovery model is set to FULL RECOVERY.
1st step: Issue a full backup of the database
.
BACKUP DATABASE TestMirror TO DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Backup.bak';
2nd step: Issue a transaction log backup of the database.
BACKUP LOG TestMirror TO DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Backup.trn';
Below are the two files in the file system:
3rd step: Assuming you have the backup folder shared on the Principal
Server and you can access it from the Mirror Server, you will need to
restore the full backup to the Mirror server with the NORECOVERY option.
RESTORE DATABASE TestMirror FROM DISK = N'\\Principal\Backup\Backup.bak'
WITH FILE = 1, MOVE N'TestMirror_log' TO
N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestMirror_1.ldf',
NORECOVERY, NOUNLOAD, STATS = 10;
4th step: Restore log backup also with the NORECOVERY option
.
RESTORE LOG TestMirror FROM DISK = N'\\Principal\Backup\Backup.trn'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10;
Now it's time to dig down and configure Database Mirroring. From the
Principal server, right click the database and choose "Tasks" | "Mirror"
or choose "Properties" | "Mirroring".
Click the "Configure Security" button and click "Next >" if the
Configure Database Mirroring Security Wizard intro screen appears. The
next screen should be the Include Witness Server screen:
This is where you would configure a witness server for your
mirroring, but since we're just configuring a basic mirror we will skip
this part. However, if you are configuring mirroring in an Enterprise
environment it is recommended you configure a witness server because
without one you will not have synchronous automatic failover option.
Select "No", then click "Next >" to continue the process.
The next screen will give you options to configure the Principal Server Instance:
Here we will be creating our endpoint, which is a SQL Server object
that allows SQL Server to communicate over the network. We will name it
Mirroring with a Listener Port of 5022.
Click the "Next >" button to continue.
The next screen will give you options to configure the Mirror Server Instance:
To connect to the Mirror server instance we will need to click the
"Connect..." button then select the mirror server and provide the
correct credentials:
Once connected, we also notice our endpoint name is Mirroring and we are listening on port 5022.
Click "Next >" and you'll see the Service Accounts screen.
When using Windows Authentication, if the server instances use
different accounts, specify the service accounts for SQL Server. These
service accounts must all be domain accounts (in the same or trusted
domains).
If all the server instances use the same domain account or use certificate-based authentication, leave the fields blank.
Since my service accounts are using the same domain account, I'll leave this blank.
Click "Finish" and you'll see a Complete the Wizard screen that
summarizes what we just configured. Click "Finish" one more time.
If you see the big green check mark that means Database Mirroring has
been configured correctly. However, just because it is configured
correctly doesn't mean that database mirroring is going to start...
Next screen that pops up should be the Start/Do Not Start Mirroring screen:
We're going to click Do Not Start Mirroring just so we can look at the Operating Modes we can use:
Since we didn't specify a witness server we will not get the High
Safety with automatic failover option, but we still get the High
Performance and High Safety without automatic failover options.
For this example, we'll stick with synchronous high safety without
automatic failover so changes on both servers will be synchronized.
Next, click "Start Mirroring" as shown below.
If everything turned out right, Database Mirroring has been started successfully and we are fully synchronized.
If Database mirroring did not start successfully or you received an error here are a few scripts to troubleshoot the situation:
Both servers should be listening on the same port. To verify this, run the following command:
SELECT type_desc, port
FROM sys.tcp_endpoints;
We are listening on port 5022. This should be the same on the Principal and Mirror servers:
Database mirroring should be started on both servers. To verify this, run the following command:
SELECT state_desc
FROM sys.database_mirroring_endpoints;
The state_desc column on both the Principal and Mirror server should be started:
To start an Endpoint, run the following:
ALTER ENDPOINT <Endpoint Name>
STATE = STARTED
AS TCP (LISTENER_PORT = <port number>)
FOR database_mirroring (ROLE = ALL);
ROLES should be the same on both the Principal and Mirror Server, to verify this run:
SELECT role
FROM sys.database_mirroring_endpoints;
To verify the login from the other server has CONNECT permissions run the following:
SELECT EP.name, SP.STATE,
CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
AS GRANTOR,
SP.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
AS GRANTEE
FROM sys.server_permissions SP , sys.endpoints EP
WHERE SP.major_id = EP.endpoint_id
ORDER BY Permission,grantor, grantee;
You can see here from the State and Permissions column that the user has been Granted Connect permissions.
Database Mirroring Questions:
1) What is default port of Database Mirroring Endpoint?
Ans : 5022
2) Database Mirroring comes with which edition?
Ans: SQL Server 2005 SP1. Or SQL Server 2005 RTM with trace flag 1400
3) When I configure mirroring I’m receiving the below errror,
One or more of the server network addresses lacks a fully qualified
domain name (FQDN). Specify the FQDN for each server, and click start
mirroring again
Ans: The fully qualified computer name of each server can be found running the following from the command prompt:
IPCONFIG /ALL
Concatenate the “Host Name” and “Primary DNS Suffix”
Host Name . . . . . . . . . . . . : A
Primary Dns Suffix . . . . . . . :
corp.mycompany.com
Then FQDN of your computer name is just
A.corp.mycompany.com.
4) How to enable mirroring by Script ?
Ans: – Specify the partner from the mirror server
ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://A.corp.mycompany.com:5022′;
– Specify the partner from the principal server
ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://B.corp.mycompany.com:5022′;
Note: Replace the dbname before using the above script
5) How to disable mirroring by script?
Ans: ALTER DATABASE [AdventureWorks] SET PARTNER OFF
Note: Replace the dbname before using the above script
6) How to do manual failover to Mirror when principle is working fine?
Ans: ALTER DATABASE <DB Name> SET PARTNER FAILOVER
7) Why I’m getting the below error message while configuring database mirroring?
Msg 1416, Level 16, State 31, Line 3. Database is not configured for database mirroring
Ans : You need to restore the Full backup from principal server using
With NoRecovery option and also one transactional log backup from
principal server using With NoRecovery option and then start configuring
mirroring.
8) Can we configure mirroring between Standard Edition & Enterprise Edition or Vice Versa?
Ans: Nope its not possible, both principal and mirror should have same edition
9) Is it possible to take backup of mirrored database in mirror server?
Ans: No
10) Is it possible to perform readonly operation at mirrored database in mirror server?
Ans: Yes, You can create database snapshot for the same
11) Can I create multiple endpoints for configuring different
databases for mirroring and point each database to unique endpoint.
Ans: No
12) Can I configure a single database to be mirrored to more than one
server. i.e) One source & many destination like logshipping
Ans: No
13) How can I increase Heartbeat time between principal and mirror server?? By default its 10 sec.
Ans: ALTER DATABASE AdventureWorks SET PARTNER TIMEOUT 30
Note: Before using the script change the dbname.
14) What status of mirroring has if secondary is down?
Ans: If secondary is down principle or Mirror show status disconnected
15) What status of mirroring has if principle is down?
Ans: If principle is down mirror will be disconnected with in recovery instead of synchronized with restoring
16) What status of mirroring has if mirroring is paused?
Ans: Is mirroring is set to paused from principle then then both principle & mirror in suspending
17) How to bring mirror DB online if Principle is down?
Ans: ALTER DATABASE <DB Name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
If you try to do failover like normal situation when principle is
online [ALTER DATABASE <DB Name> SET PARTNER FAILOVER] then you
will receive below error.
ALTER DATABASE <DB Name> SET PARTNER FAILOVER
Msg 1404, Level 16, State 10, Line 1
The command failed because the database mirror is busy. Reissue the command later.
18) System Store Procedure to monitor Mirroring?
Ans:
MSDB.SYS.SP_DBMMONITORRESULTS
19) What are different possible Mirroring Stats?
Ans:
SYNCHRONIZING
SYNCHRONIZED
SUSPENDED
PENDING FAILOVER
DISCONNECTED
20)What is Database Mirroring End Points? Usage of End Points? More info about End Points in DATABASE MIRRORING
Usage of Endpoints:
Endpoints on both the principal, mirror and witness (if being used)
which allows the SQL server instances to communicate with each other.
Database mirroring is a very handy HA(High Availability) solution
available per database for SQL 2005/2008 and is available in both
Enterprise and Standard edition. This solution also provides both
automatic and manual failover. Therefore it is a HA solution available
to almost all SQL server users.
Database mirroring overview is
made up of a principal server role and a mirroring server role plus an
optional witness server. The principal database is available for serving
client and user connections. When transactions are applied to the
principal database these transactions are then submitted to the mirror
servers database, however the mirror server database cannot serve user
requests as the mirror database is left in a recovering state.
The general steps for configuring database mirroring involves the following steps: –
1. Create Endpoints on both the principal, mirror and
witness (if being used) which allows the SQL server instances to
communicate with each other.
2. Create a copy of the principal database on the
mirror server by restoring a backup of the database from the prinicpal
server to the mirror server with the NO RECOVERY option.
3. Start the mirroring session to tell SQL server which endpoints are connected to which partner and witness server.
It seems that the most often overlooked or
misconfigured components are the endpoints. Since all mirroring
information is done over the endpoints it is obviously a critical
component to check if things are not working.
Many people seem to believe that the endpoints are
stored in the principal, mirror and witness databases. Why each
component involved in the mirroring communication requires an endpoint
to actual endpoint configuration is stored in the master database of the
instance. Therefore it is possible to check the configuration on all
nodes in the mirroring configuration.
The following T-SQL code will output information for
the endpoints and if you are having issues with your new mirroring
session you should run this to check the endpoints are configured
correctly on your principal, mirror and witness (if being used).
USE master
GO
SELECT perm.class_desc, perm.permission_name, endpoint_name = e.name, e.state_desc, e.type_desc, t.port, perm.state_desc, grantor = prin1.name, grantee = prin2.name
FROM master.sys.server_permissions perm
INNER JOIN master.sys.server_principals prin1 ON perm.grantor_principal_id = prin1.principal_id
INNER JOIN master.sys.server_principals prin2 ON perm.grantee_principal_id = prin2.principal_id
LEFT JOIN master.sys.endpoints e ON perm.major_id = e.endpoint_id
LEFT JOIN master.sys.tcp_endpoints t on t.endpoint_id = e.endpoint_id
WHERE perm.class_desc = 'ENDPOINT' AND e.type_desc = 'DATABASE_MIRRORING'
order by endpoint_name ASC
This will provide you with very useful information
such as the endpoint state (started, stopped, etc), TCP port that the
endpoint is using and permissions that have been assigned to the
endpoint along with which user has been granted connect permissions to
the endpoint. You should execute this on each of the mirror instances
such as the principal, mirror and witness.
Obviously with this information you can confirm that
the SQL servers can communicate over the TCP ports. You can confirm this
via telnet <servername> <PORTNUMBER>. If there is a firewall issue, etc this simple check and test will let you know.
You should also pay particular attention to which
grantee has been assigned connect permissions to which endpoint. Your
designated user from the principal instance needs connect permissions on
the endpoint in the mirror instance and the mirror instance user
requires connect permissions to the endpoint on the principal instance.
Ref:
https://www.mssqltips.com/sqlservertip/2464/configure-sql-server-database-mirroring-using-ssms/
