Database Mirroring? Step by Step demo also solving Microsoft SQL Server, Error: 1418 Issue and Questions and Answers

Database Mirroring? Step by Step demo

What is SQL Server database mirroring?

SQL Server database mirroring is a disaster recovery and high availability technique that involves two SQL Server instances on the same or different machines. One SQL Server instance acts as a primary instance called the principal, while the other is a mirrored instance called the mirror. In special cases, there can be a third SQL Server instance that acts as a witness

Database name should be same in principal and Mirror Server and witness server

Database name login should be sysadmin credentials existing with principal and Mirror Server and witness server 

Database name from Mirror should be in Restoring(with no recovery mode can't  read/write) only

and not accept any other mode like read-write and read only 

Using windows firewall SQL port 1433 and using port like 5022,5023 should be include in inbound/out bound rule it should allow these ports

SQL Server and SQL Server Agent services should be in same account with windows login or network service  from Run--> services. m s c

u can also see Run--> l u s r mgr.m s c (local user and group) to check which user is present )

if it is remote server then kindly check it remote login using Run--> m s t s c (remote desktop)

Implementation examples

One of the common mirroring configuration is the environment with two SQL Servers (S Q L Server-1 and S Q L Server-2), two instances (S Q L Instance-1 and S Q L Instance-2), and one mirrored database named S Q L DB-1

The second common configuration is the environment with one SQL Server machine, two SQL Server instances, and one mirrored database named S Q L DB-1. This solution has a major flaw because if S Q L Server-1 goes down, both instances will be unavailable

Operating modes

SQL Server database mirroring can be set to provide high availability or disaster recovery. Depending on the needs, a D B A can choose among three available modes

• High safety – Data is written and committed on the principal and mirror databases synchronously. Only after committing on both databases, the database application can continue with activity
  • Might produce delay and slower operation because transactions must be committed on both databases
  • If the principal database goes down, two options are available:
    • Do nothing – wait for the principal to become available again. During that time, the SQL Server instance is unavailable. Mirroring will continue where it has stopped
    • Force the SQL Server instance on the mirror database – the mirror database becomes the principal. Possible data loss due to committed transactions on the original principal database which are not yet committed on the mirror currently acting as the principal
• High safety with automatic fail over – Three servers are necessary. Data is written and must be committed synchronously both on the principal and mirror databases. Only after committing on both databases, the application can continue running
  • Might produce delay and slower operation because transactions must be committed on both databases
  • If the principal database goes down, only one option is available:
    • Let the automatic fail over process complete, the mirrored database becomes the principal
• High performance – the asynchronous communication, data is written and committed on the principal server, and later sent and committed to the mirror server. Automatic fail over isn’t possible and the witness server can’t be used
  • The high performance mode is only available in the Enterprise edition of SQL Server
  • If the principal database goes down, three options are available:
    • Do nothing – wait for the principal to become available again. The SQL Server is unavailable. Mirroring will continue where it has stopped
    • Force the SQL Server instance on the mirror database – the mirror database becomes the principal. Greater possibility for data loss, due to asynchronous communication between databases
    • Manual update – to reduce data loss, take the tail of the log backup if the failed server allows, remove mirroring and restore the tail of the log on the previously mirrored database

Advantages and disadvantages of using SQL Server database mirroring

Using SQL Server database mirroring has multiple benefits: a built-in SQL Server feature, relatively easy to set up, can provide automatic fail over in high safety mode, etc. Database mirroring can be combined with other disaster recovery options such as clustering, log shipping, and replication

Database mirroring will be removed from SQL Server in future versions in favor of Always On Availability Groups. Also, database mirroring is per database only solution, which means that logins and jobs from the principal SQL Server must be manually recreated on the mirror. There is also possibility for delay, which can only be reduced with better hardware

Setting up the database mirroring environment

The database mirroring feature is available in SQL Server 2005 version and greater. Availability of the operating modes depends on the SQL Server edition. Different SQL Server versions can be combined, but it’s not recommended

The database that needs to be mirrored must be in the full recovery model. System databases can’t be mirrored

A full database and transaction log backups of the database which will be mirrored must be created and restored on the SQL Server instance which will act as the mirror. The restore process must be executed using the WITH NO RECOVERY option

The database mirroring setup needs to be initiated from the principal server using the SQL Server Management Studio wizard or T-SQL code. At the beginning of the setup process, there’s an option for choosing a witness SQL Server instance which is only required if the high safety with automatic fail over mode is desired. SQL Server instances must be able to communicate which requires creation of so-called endpoints with the port and name specified. These setting are required both on the principal and mirror SQL Server instances

DEMO:

Principle Server: USER-PC\MS SQL 2012 SOURCE

Mirror Server: USER-PC\MS SQL 2012 D E S T SQL

Mirroring involve database: [Chinook db mirror test]

Kindly take Backup of Chinook database from Principle Server P F B (Please Find Below)

Don't forget to click Overwrite if  u want to do fresh or click append if you have already existing

db Backed up successfully

It is located on local drive see below

Now we have to do restore database from Mirror Server see below

choose located backup file see below

We need to click ... button option to overwrite/relocate file see below

it looks like m d f and l d f as see below

We should choose Restore with No Recovery

database restored Successfully on Mirror Server

From Mirror server Database now in Restoring mode see below

if we transaction happened on principle it will reflect in transaction log backup here new procedure created on principle server

We need to take it transaction log backup @ Principle server as see below

Now we need restore those transaction log backup @ mirror server as see below

need to check restore with no recovery option @ mirror server

it restored successfully @ Mirror Server

@ Mirror Server db is showing restoring see below

Now we can start database mirroring as see below

(or)

we can go to db properties as see below

Now click mirroring as see below

it will open database database mirroring wizard and now choose whether we need witness server or not

our case kindly click no option as see below

Now choose Principle server as see below

Now choose Mirror server as see below

Note:

Port should be different

We can give service account which run in Services @ SQL Server our case kindly put as [USER-PC\Administrator] or put as blank as see below

now  we complete wizard as see below

principle and mirror server is configuring End points as see below

after click submit it asks do we start mirroring or not mirroring as see below our case we can click do not mirroring 

we need to verify it on own as see below

Verifying @ Principle Server

USE [master]

GO

/****** Object:  Endpoint [Mirroring]    Script Date: 13-11-2016 23:13:32 ******/

CREATE ENDPOINT [Mirroring] 

STATE=STARTED

AS T C P (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE

, ENCRYPTION = DISABLED)

GO

Verifying @ Mirror Server

USE [master]

GO

/****** Object:  Endpoint [Mirroring]    Script Date: 13-11-2016 23:14:43 ******/

CREATE ENDPOINT [Mirroring] 

STATE=STARTED

AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE

, ENCRYPTION = DISABLED)

GO

after verification we can check it in T SQL as see below

SELECT type_desc, port FROM s y s. t c p_endpoints;

SELECT state_desc FROM [s y s. database_mirroring_endpoints]

SELECT role FROM s y s.database_mirroring_endpoints;

SELECT E P.name, SP.STATE, 

   CONVERT(n var char(38), s user_name(SP.grantor_principal_id)) 

      AS GRANTOR, 

   SP.TYPE AS PERMISSION,

   CONVERT(n var char(46),s user_name(SP.grantee_principal_id)) 

      AS GRANTEE 

   FROM s y s.server_permissions SP , s y s.endpoints E P

   WHERE SP.major_id = E P.endpoint_id

   ORDER BY Permission,grantor, grantee; 

GO

as see below 

@Principle Server:

@Mirror Server:

so it seems to be fine so now we are going to start mirroring button as see below

database mirroring is starting now

database mirroring Successfully configured as see below

When you configured it will show Error as 1418 as see below for more info see last section of this blog page(Solving Error 1418 Suggestions)

The server network address “T C P://S Q L Server:5023” cannot be reached or does not exist.
Check the network address name and that the ports for the local and remote endpoints are operational.
(Microsoft SQL Server, Error: 1418)

so we need to confirm in S Q L Server Services Running account should be windows login account in log on service from configuration manager

u can see Login exists on Principle Server as see below

u can see Login exists on Mirror Server as see below

Also we need to set in RUN-->W F. M S C  (windows firewall request) inbound/outbound  rule to allow port 1433 5022,5023 as see below

--Query to find out the databases involved in DB mirroring

select @@server name  Server Name database_id, db_name(database_id) [DB M i r r o i n g db name]  

from s y s.database_mirroring where mirroring_g u i d is not null

@Principle Server:

@Mirror Server:

What we can't do in database mirroring 

if we do we got errors see below

1) if database name not be same in mirror server as per principle server 

as see below 

Principal Server database name: Test db Source

Mirror Server database name: Test db d e s t n

 it through error in principal server as see below

2) database name should be in restoring mode(with no recovery) other wise it will give error see below

@mirror server it was in read only mode so it gives error see below

@mirror server it was in read-write mode so it gives error see below 

Remember it(again):

Database name should be same in principal and Mirror Server and witness server

Database name login should be sysadmin credentials existing with principal and Mirror Server and witness server 

Database name from Mirror should be in Restoring(with no recovery mode can't  read/write) only

and not accept any other mode like read-write and read only 

Using windows firewall SQL port 1433 and using port like 5022,5023 should be include in inbound/out bound rule it should allow these ports

SQL Server and SQL Server Agent services should be in same account with windows login or network service  from Run--> services. m s c

u can also see Run--> l u s r mgr.m s c (local user and group) to check which user is present )

if it is remote server then kindly check it remote login using Run--> m s t s c (remote desktop)

Useful scripts:

--To stop 

ALTER ENDPOINT mirroring STATE=STOPPED

--To start

ALTER ENDPOINT mirroring STATE=STARTED

exec s p_reset status 'test'

select s y s.database_mirroring_endpoints

GRANT CONNECT ON ENDPOINT::[Mirroring] TO [USER-PC\d b m]

restore database test d b m with recovery

ALTER DATABASE test d b m SET PARTNER OFF

SELECT type_desc, port FROM s y s.t c p_endpoints;

SELECT state_desc FROM s y s.database_mirroring_endpoints;

SELECT role FROM s y s.database_mirroring_endpoints;

SELECT E P.name, SP.STATE, 

   CONVERT(n var char(38), s user_name(SP.grantor_principal_id)) 

      AS GRANTOR, 

   SP.TYPE AS PERMISSION,

   CONVERT(n var char(46),s user_name(SP.grantee_principal_id)) 

      AS GRANTEE 

   FROM s y s.server_permissions SP , s y s.endpoints E P

   WHERE SP.major_id = E P.endpoint_id

   ORDER BY Permission,grantor, grantee; 

GO

--Query to find out the databases involved in DB mirroring

select database_id, db_name(database_id)

from s y s.database_mirroring where mirroring_g u id is not null

--Query to turn off the database mirroring

ALTER DATABASE SET PARTNER OFF​

Solving Error 1418 Suggestions:

Fix/Work Around/Solution: Try all the suggestions one by one.

Suggestion 1: Make sure that on Mirror Server the database is restored with NO RECOVERY option (This is the most common problem).

Suggestion 2: Make sure that from Principal the latest LOG backup is restored to mirror server. (Attempt this one more time even though the full backup has been restored recently).

Suggestion 3: Check if you can telnet to your ports using command TELNET Server Name Ports like “telnet S Q L Server Name 5023”.

Suggestion 4: Make sure your firewall is turned off.

Suggestion 5: Verify that the endpoints are started on the partners by using the state or state_desc column the of the s y s.database_mirroring_endpoints catalog view. You can start end point by executing an ALTER ENDPOINT statement.

Suggestion 6: Try the following command as one of the last options.

GRANT CONNECT ON ENDPOINT::Mirroring TO ALL

Suggestion 7: Delete the end points and recreate them.

If any of above solutions does not fix your problem, do leave comment here. Based on the comment, I will update this article with additional suggestions.

Please note that some of the above suggestions can be security threat to your system. Please use them responsibly and review your system with security expert in your company.

Working around with some values:

To see database mirroring monitor kindly go through below steps

it will open database mirroring monitor as see below

now insert some values to principal server as see below

when you try to see values @mirror it gives error as below because it was in restoring mode

we can check mirror server status as see below T SQL

SELECT DB_NAME(database_id),   
mirroring_role_desc,    
mirroring_state_desc    
FROM s y s.database_mirroring    
WHERE mirroring_g u id IS NOT NULL; 

@Mirror server status:

@Principal server status

How to Manually Fail over a SQL Server Database Mirroring Session:

Alter database database name set partner fail over

or 

we can do it manually as see below

you can see new mirror server info for db mirror db after fail over

you can see new principal server info for db mirror db after fail over

now we can see database mirror monitor for database level

@ DB mirror db status

@Chinook db mirror test db level status to know we have to go for principal server as see below

now you can see values are reflected on table when it was in mirror server. now we  can see after fail over to principal so we can able to see /querying as see below

by manual:

Transact-SQL:
To manually fail over (swap principal and mirror server roles) SQL Server database mirroring session when the database is in the SYNCHRONIZED state using T-SQL, run this code from the principal server:

USE master;
ALTER DATABASE [DB_Name] SET PARTNER FAIL OVER-->kindly remove space after fail

The mirror database becomes the principal and the principal database becomes the mirror, clients are disconnected from the former principal database and active transactions are rolled back:

“Non qualified transactions are being rolled back. Estimated rollback completion: 100%.”

If you try to manually fail over SQL Server database mirroring session when the database is NOT in the SYNCHRONIZED state, you will get an error message:

M s g 1422, Level 16, State 2, Line 2
The mirror server instance is not caught up to the recent changes to database “Database_Name”. Unable to fail over.

How can i bring Mirror Database online after principal server is down?

Safety FULL with Witness :

Well the answer for this ‘depends on the mode in which mirroring is configured’. If mirroring is configured in High Availability mode (Full safety) then we don’t need to worry about fail over as the mirror server will form a quorum with witness and will initiate an automatic fail over. The safety level can be set using the below command,

ALTER DATABASE db_name SET SAFETY FULL

ALTER DATABASE db_name SET SAFETY OFF

Safety FULL without Witness :

This scenario provides high safety, but automatic fail over is not allowed. This mode is called as High Protection mode. In the event of failure of the principal, the database service becomes unavailable. You need manual intervention to make the database service available. You must break the mirroring session and then recover the mirror database.

For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available

ALTER DATABASE db_name SET PARTNER OFF

RESTORE DATABASE db_name WITH RECOVERY




Safety OFF :

In the event of failure of the principal, the database service becomes unavailable. You can perform a force service to make the database service available on the mirror. However, since the safety level is OFF, it is possible that there were transactions that didn't make it to the mirror at the time of the failure of the principal. These transactions will be lost. Therefore, manual fail over with safety OFF involves acknowledging the possibility of data loss.

For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:

ALTER DATABASE db_name SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS




Once the database on Server_A becomes operational, it automatically assumes the role of the mirror. However, the mirroring session remains SUSPENDED, and you will need to manually RESUME the mirroring session.

Login Failures while connecting to new principal database after fail over ?

After configuring database mirroring in SQL Server 2005 and performing fail over, the original mirror database now becomes the new principal database. We might have even created the same login (as in principal) in original mirror server prior to fail over. But after fail over if we try to connect or if the application tries to connect, the following error will be returned,
Cannot open database requested by the login. The login failed.
In that case we need to map the login to the user in the database using the procedure s p_change_users_login after which the application or the user will be able to successfully connect to the new principal database.

This problem occurs because the S I D s the SQL Server logins on each server do not match. Although the names for the logins are the same, the login is resolved via the SID. This is not a problem with Windows/Domain user/group logins because the S I D s for these logins are created based on the domain SID for the user/group, and hence will be the same for the same given user/group no matter what SQL Server the user/group is added to.

In order to make the s p_change_users_login synchronization step unnecessary, we need to create the SQL Server logins on the mirror server not only with the same name, but also with the same SID as on the principal server. This can be accomplished by using the SID specification in the ‘CREATE LOGIN statement when creating the logins on the mirror server. Here is an example where we create a the same login in mirror server as the one in principal server.

CREATE LOGIN WITH PASSWORD ='password',SID ='s i d for same login on principal server'

To retrieve the SID for each login from the principal server query the s y s . s q l_logins catalog view.

You can also create all the logins with same SID in mirror server from principal server using s p_help rev login procedure. Consider this step as p r e-requisite for configuring db mirroring.

SQL Server D B A Interview Questions and Answers – Database Mirroring – 1

This is very interesting topic and consists of very wide range of terminologies and scope. I will cover this topic in two blogs so that I can touch base all the possible Questions from Database Mirroring.

1. What is Database Mirroring?

Database mirroring was introduced with Microsoft SQL Server 2005 technology that can be used to design high-availability and high-performance solutions for database redundancy.

In database mirroring, transaction log records are sent directly from the principal database to the mirror database. This helps to keep the mirror database up to date with the principal database, with no loss of committed data. If the principal server fails, the mirror server automatically becomes the new principal server and recovers the principal database using a witness server under high-availability mode. We will discuss these modes later. Fundamentally to summarize there are three jargon to understand – Principal database is the active live database that supports all the commands, Mirror is the hot standby and witness which allows for a quorum in case of automatic switch over.

2. How does Database Mirroring works?

In database mirroring, the transaction log records for a database are directly transferred from one server to another, thereby maintaining a hot standby server. As the principal server writes the database’s log buffer to disk, it simultaneously sends that block of log records to the mirror instance. The mirror server continuously applies the log records to its copy of the database. Mirroring is implemented on a per-database basis, and the scope of protection that it provides is restricted to a single-user database. Database mirroring works only with databases that use the full recovery model.

3. What are the benefits of that Database Mirroring?

• Database mirroring architecture is more robust and efficient than Database Log Shipping. It can be configured to replicate the changes synchronously to minimized data loss.
• It has automatic server fail over mechanism.
• Configuration is simpler than log shipping and replication, and has built-in network encryption support (A E S algorithm).
• Because propagation can be done asynchronously, it requires less bandwidth than synchronous method (e.g. host-based replication, clustering) and is not limited by geographical distance with current technology.
• Database mirroring supports full-text catalogs.
• Does not require special hardware (such as shared storage, heart-beat connection) and cluster ware, thus potentially has lower infrastructure cost

4. What are the Disadvantages of Database Mirroring?

• Potential data lost is possible in asynchronous operation mode. R TO will vary and depend on several factors, such as propagation interval time and bandwidth speed.
• It only works at database level and not at server level. It only propagates changes at database level, no server level objects, such as logins and fixed server role membership, can be propagated.
• Automatic server fail over may not be suitable for application using multiple databases.

5. What are the minimum requirements for Database Mirroring?

• Database base recovery model should be full
• Database name should be same on both SQL Server instances
• Server should be in the same domain name
• Mirror database should be initialized with principle server

6. What are the Restrictions for Database Mirroring?

• A mirrored database cannot be renamed during a database mirroring session.
• Only user databases can be mirrored. You cannot mirror the master, ms db, temp db, or model databases.
• Database mirroring does not support FILE STREAM. A FILE STREAM file group cannot be created on the principal server. Database mirroring cannot be configured for a database that contains FILE STREAM file groups.
• On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance.
• Database mirroring is not supported with either cross-database transactions or distributed transactions.

7. What is a Principal server?

Principal server is the server which serves the databases requests to the Application.

8. What is a Mirror?

This is the Hot standby server which has a copy of the database.

9. What is a Witness Server?

This is an optional server. If the Principal server goes down then Witness server controls the fail over process.

10. What is Synchronous and Asynchronous mode of Database Mirroring?

In synchronous mode, committed transactions are guaranteed to be recorded on the mirror server. Should a failure occur on the primary server, no committed transactions are lost when the mirror server takes over. Using synchronous mode provides transaction safety because the operational servers are in a synchronized state, and changes sent to the mirror must be acknowledged before the primary can proceed

In asynchronous mode, committed transactions are not guaranteed to be recorded on the mirror server. In this mode, the primary server sends transaction log pages to the mirror when a transaction is committed. It does not wait for an acknowledgement from the mirror before replying to the application that the COMMIT has completed. Should a failure occur on the primary server, it is possible that some committed transactions may be lost when the mirror server takes over.

11. What are the operating modes of Database Mirroring?

SQL Server provides 3 operating modes for database mirroring.

• High Availability Mode
• High Protection Mode
• High Performance Mode

12. What is High Availability operating mode?

It consist of the Principal, Witness and Mirror in synchronous communication. In this mode SQL server ensures that each transaction that is committed on the Principal is also committed in the Mirror prior to continuing with next t r a n s a c t i o n a l operation in the principal. The cost of this configuration is high as Witness is required. If the network does not have the bandwidth, a bottleneck could form causing performance issue in the Principal. If Principal is lost Mirror can automatically take over.

13. What is High Protection operating mode?

It is pretty similar to High Availability mode except that Witness is not available, as a result fail over is manual. It also has t r a n s a c t i o n a l safety FULL i.e. synchronous communication between principal and mirror. Even in this mode if the network is poor it might cause performance bottleneck.

14. What is High Performance operating mode?

It consists of only the Principal and the Mirror in asynchronous communication. Since the safety is OFF, automatic fail over is not possible, because of possible data loss; therefore, a witness server is not recommended to be configured for this scenario. Manual fail over is not enabled for the High Performance mode. The only type of fail over allowed is forced service fail over, which is also a manual operation.

15. What are Recovery models support Database Mirroring?

Database Mirroring is supported with Full Recovery model.

16. What are End Points and its usages?

An endpoint is a network protocol which is used to communicate Principal, Mirror and Witness servers over the network.

17. How can we create an end point using SQL script?

CREATE ENDPOINT End point 1
STATE = STARTED
AS T C P ( LISTENER_PORT = 5022 )
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO

18. What is the default of end points (port numbers) of principal, mirror and witness servers?

The default port numbers of principal, mirror and Witness servers are 5022, 5023 and 5024

19. What is Log Hardening?

Log hardening is the process of writing the log buffer to the transaction log on disk, a process called.

20. Is it possible to perform read only operation at mirrored database in mirror server?

Yes, using database snapshots.

21. What is Role-switching?

Inter changing of roles like principal and mirror are called role switching.

22. How to Set a Witness Server to Database Mirroring?

ALTER DATABASE Adventure Works SET WITNESS = 'T C P://S Q L W I T N.local:5024'

23. How to Remove a Witness Server from Database Mirroring?

ALTER DATABASE Adventure Works SET WITNESS OFF

24. What are the Database Mirroring states?

• SYNCHRONIZING
• SYNCHRONIZED
• SUSPENDED
• PENDING_FAIL OVER
• DISCONNECTED

25. What does SYNCHRONIZING state means in Database Mirroring?

The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward. At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up.

26. What does SYNCHRONIZED state means in Database Mirroring?

When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database.

If transaction safety is set to FULL, automatic fail over and manual fail over are both supported in the SYNCHRONIZED state, there is no data loss after a fail over.
If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.

27. What does SUSPENDED state means in Database Mirroring?

The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a fail over. A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session. SUSPENDED is a persistent state that survives partner shutdowns and startups.

28. What does PENDING_FAIL OVER state means in Database Mirroring?

This state is found only on the principal server after a fail over has begun, but the server has not transitioned into the mirror role.

When the fail over is initiated, the principal database goes into the PENDING_FAIL OVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.

29. What does DISCONNECTED state means in Database Mirroring?

The partner has lost communication with the other partner

30. Why we get the below error message while configuring database mirroring?

M s g 1416, Level 16, State 31, Line 3. Database is not configured for database mirroring

We need to restore the Full backup from principal server using With No Recovery option and also one t r a n s a c t i o n a l log backup from principal server using With No Recovery option and then start configuring mirroring.

31. Can we configure a single database to be mirrored to more than one server. i.e) One source & many destination like log shipping?

No, It is not possible.

References: Thanks to the all the SQL Server techies who wrote and shared the valuable information in the below blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.

SQL Server D B A Interview Questions and Answers – Database Mirroring – 2

1) What is Database Snapshot?

A database snapshot is a view of what the source database looked like at the time at which the snapshot was created. This means that all the objects will be the same as what it was when the snapshot was taken and all of the data will be exactly as it was then. To use database snapshots to recover from an unwanted D M L statement, you need to have a suitable snapshot in place. Snapshots can only be created by using a T-SQL statement.

2) How to create a Database Snapshot?

CREATE DATABASE Adventure Works 2012_S 1 ON (NAME = [Adventure Works 2012_Data], FILENAME = 'D:\ M S SQL\DATA\Adventure Works 2012_Data_SS_1.s s') AS SNAPSHOT OF Adventure Works 2012

3) How does a snapshot work?

High level tasks of snapshot involves

• When you create a snapshot a sparse file is created for each data file
• When data is modified in the source database for the first time, the old value of the modified data is copied to the sparse file
• If the same data is the subsequently changed again, those changes will be ignored and not copied to the snapshot
• When you query the snapshot, it first checks if the data is available in the snapshot. If it’s there it reads if from the snapshot. If it’s not there, I reads through to the source database and gets the data from there instead, because that means the data has not yet changed since the time the snapshot was taken

4)  What is SPARSE file?

When a database snapshot is created, a sparse file is added for each database file in the database of which the snapshot was taken. A sparse file is basically an empty file. It does not contain any data until a change is made to the source database

5) What are important things to remember about sparse files?

• The maximum size a sparse file can grow to is the size of the original file at the time of the database creation
• Sparse files are limited to 16 GB on Windows 2008 and 64 GB on Windows 2003
• Sparse files grow in increments of 64 KB

6) How can we check the SPARSE files?

SELECT name, is_sparse FROM s y s.database_files

7) Can we change the permissions in a database snapshot?

No it’s not possible.

8)Can we take a backup of a database snapshot?

No.

9) Can we detach a database snapshot?

No.

10) What are the restrictions in Database Snapshots?

• Database snapshots depend on the source database. They can only be created on the same server as where the database resides (or the server to which the database is mirrored.)
• While there are snapshots present, you cannot drop the database or add any new files to it
• Once a database snapshot becomes suspect it cannot be saved. It just has to be deleted. This can happen if the snapshot runs out of space or reaches the maximum sparse file size limit
• You cannot create indexes on snapshots, they are strictly read only
• The user permissions are exactly the same as it was in the source database. You cannot grant a user access to a snapshot. You have to grant the access in the source database and then take another snapshot first before the user will be able to access it

11) What are the benefits of Database Snapshots?

• Snapshots can be used for reporting purposes.
• Using a mirror database that you are maintaining for availability purposes to offload reporting.
• Safeguarding data against administrative and user error

12) Can we configure mirroring between Standard Edition & Enterprise Edition or Vice Versa?

No it’s not possible, both principal and mirror should have same edition

13) Is it possible to take backup of mirrored database in mirror server?

No

14) Can I create multiple endpoints for configuring different databases for mirroring and point each database to unique endpoint?

No

15) Can I configure a single database to be mirrored to more than one server. i.e) One source & many destination like logs hipping?

No

16) How to know the database mirroring connection time out in Mirroring?

SELECT Mirroring_Connection_Timeout FROM s y s.database_mirroring WHERE database_id = db_id('Database Names')

17) How can I increase Heartbeat time between principal and mirror server? By default its 10 sec.

ALTER DATABASE Adventure Works SET PARTNER TIMEOUT 30

18) What status of mirroring has if secondary is down?

If secondary is down principle or Mirror show status disconnected

19) What status of mirroring has if principle is down?

If principle is down mirror will be disconnected with in recovery instead of synchronized with restoring

20) What status of mirroring has if mirroring is paused?

If mirroring is set to paused from principle then both principle & mirror in suspending

21) How to monitoring Mirroring?

There are six methods are available for monitoring the Database Mirroring
a) Database Mirroring Monitor:- Database Mirroring Monitor is a GUI tool that shows update status and to configure warning thresholds.
To open D M Monitor:- Right click on Principal Database > Tasks > Select Launch Database Mirroring Monitor.
b) SQL Server Management Studio:- A green arrow on the mirror server is indicates running well. A red arrow indicates problems that need to investigate.
c) SQL Server Log:- It provides information of Mirroring establishment and status. If any errors occur it will be logged to SQL Server log and Windows event log.
d) Performance Monitor:- It can provides real-time information about Database mirroring. We can use performance counters to get status of the database mirroring such as Bytes received/sec, Bytes sent/sec, Transaction delay etc.
e) Pro filer:- Pro filer many events are providing the status of the Database mirroring
f) System Stored Procedures:-
s p_db m monitor add monitoring
s p_db m monitor change monitoring
s p_db m monitor help monitoring
s p_db m monitor drop monitoring

22) What will be the impact if we add data files to the Primary database in Database Mirroring?

Data files will be added to the Mirror database if the identical location is present at the mirrored server without any issues.

23) What will be the impact if we add data files to the Primary database with a location which is not present on the Mirrored server in Database Mirroring?

Database mirroring will not work in this case. We have to break the Mirroring and take a Log backup of the Primary database and restore it on the Mirror database using “WITH MOVE” and “NO RECOVERY” option. Once it’s done then Reestablish the mirroring.

24) If we add any users to the Primary database, will it be copied to the Mirror databases or not?

Yes, It will be copied to the Mirror database with the permissions. However you have to create the login manually with the same SID.s

25) How to disable mirroring by scripts?

ALTER DATABASE <s database_name> SET PARTNER OFF

26) How to do manual fail over to Mirror when principle is working fine?

ALTER DATABASE <DB Name> SET PARTNER FAIL OVER

27) What is Transparent Client Redirection?

Database mirroring provides a feature of automatically redirection of the Application connection to Mirror database in case of Primary database failures. Database connections configured as below to enable this functionality.

.NET Example
Data Source=S Q L A;Fail over Partner=S Q LB;Initial Catalog = Adventure Works 2008 R 2 ; Integrated Security=True

28) What is Log buffer?

Log buffer is a small contiguous memory structure, only 60 k maximum, to host in memory transaction logs per database. When the transaction is committed, entire buffer will be written to transaction log file before commit finishes.

29) What is Automatic Page Repairing?

Automatic page repair is supported by database mirroring. After certain types of errors corrupt a page, making it unreadable, a database mirroring partner (principal or mirror) attempts to automatically recover the page. The partner/replica that cannot read the page requests a fresh copy of the page from its partner. If this request succeeds, the unreadable page is replaced by the readable copy, and this usually resolves the error, this whole process is called Automatic Page Repair.

30) What are the error types which cause and Automatic Page Repair attempt?

Error number	Description	Instances that cause automatic page-repair attempt
823	Action is taken only if the operating system performed a cyclic redundancy check (C RC) that failed on the data.	ERROR_C RC. The operating-system value for this error is 23.
824	Logical errors.	Logical data errors, such as torn write or bad page checksum.
829	A page has been marked as restore pending.	All.

31) What are the page types which are not repairable using Automatic Page repair option?

Automatic page repair cannot repair the following control page types:

• File header page (page ID 0).
• Page 9 (the database boot page).
• Allocation pages: Global Allocation Map (G A M) pages, Shared Global Allocation Map (S G A M) pages, and Page Free Space (P F S) pages.

References: Thanks to the all the SQL Server techies who wrote and shared the valuable information in the below blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.

Ref:

h t t p://w w w .d b a mantra.com/s q l-server-d b a-interview-questions-answers-database-mirroring-1/