Finding current database mirroring state in SQL SERVER

In mirroring databases can goes into suspend or fail-over status due to any reason like Network, Space etc. It is necessary to find easy ways to look into current running status of databases and servers. SQL SERVER 2008 provides variety of features to look into this.

There are different ways to look into mirroring status. 

T-SQL

Using t-sql query on master you can get all running databases on Principal server and datail about witness and mirror. I have returned only columns that are in my use you can have much more options. using DMVs you can get result. I did not setup Witness so data is empty for Witness

------------------------- TSQL Code -------------------------------------------------

SELECT d.name,m.database_id,m.mirroring_state,mirroring_state_desc,mirroring_role_desc,mirroring_partner_instance,mirroring_witness_name,mirroring_witness_state,mirroring_witness_state _desc FROM 
sys.database_mirroring M inner join SYS.DATABASES d
on m.database_id = d.database_id
where mirroring_state_desc is not null

--------------------------------------------------------------------------------------

SSMS Mirroring Properties

You can find database current status for mirroring using SSMS. 

GOTO --> database Properties 

Open --> Mirroring Tab

Press on Refresh button and you will get successfully synchronized message. If there is any problem between servers or database you will get different message and can identify that there is some problem.

Database Mirroring Monitor

If you go to database properties you will find Database Mirroring Monitor option. It will show you a new Monitor window and show 2 rows for that database. One if Principal server status and second is Mirror server status with witness address at the bottom. It will show you refresh time at top left corner as well. Using this monitor you can see History for last 2 minutes and set thresh hold for your easiness.

Warning messages can be set manually. I will warn you on windows log and sql server log as well.

Failover Techniques in SQL SERVER Snapshot vs. Log Shipping vs. Mirroring vs. Replication vs. Clustering

For a new person this is difficult to find differences in snapshot, log shipping, mirroring and replication. I have gone through multiple sites and videos last night and now summing up my findings for all 5 techniques. According to many expert persons I have found priorities in terms of failover & point in time recovery support of these techniques in SQL SERVERS, following are these priorities.

1-    Failover Clustering

2-    Replication

3-    Mirroring

4-    Log Shipping

5-    Snapshot

Here is the basic and major difference between all techniques

1) Failover Clustering is a high availability option used with clustering technology provided by hardware and Operating system. Data resides on SAN network storage instead of any particular server. Advantages of SAN storage is large efficient hot pluggable disk storage. You might use other technique like mirroring with failover clustering.

2) Replication is used mainly when data centers are distributed geographically. It is used to replicate data from local servers to the main server in the central data center. There is no stand by server like in mirroring and publisher & subscriber both are active. There are different data updating types in replication

Type of replication	Use when…
Merge replication	·         There are a large number of Subscribers. ·         Data is replicated to mobile users. ·         Replicated data is frequently updated at the Subscriber. ·         Data filtering is needed so that Subscribers receive different partitions of data.
Peer-to-peer transactional replication	·         Replication is used to improve scalability and availability. ·         Minimal latency is required. ·         Data is not partitioned among Subscribers. ·         Conflicts typically do not occur, but they must be detected if they do.
Transactional replication with updating subscriptions	·         There are a small number of Subscribers. ·         Replicated data is mostly read-only at the Subscriber. ·         Subscriber, Distributor, and Publisher are connected most of the time (for immediate updating subscriptions).
Snapshot Replication	·         Provide initial data set for transactional and merge publications ·         Can be used by itself ·         It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time. ·         Replicating small volumes of data. ·         Snapshot replication is most appropriate when data changes are substantial but infrequent.

3) Mirroring which was introduced with 2005 edition, works on top of Log Shipping. Main difference is the uptime for the standby server is quite less in mirroring. Standby server automatically becomes active in this case with the help of Witness, Additional advantages of Mirroring include support at .NET Framework level plus some new features like Auto Page Recovery introduced with SQL SERVER 2008.

4) Log Shipping is an old technique available since SQL SERVER 2000. Here the transactional log (ldf) is transferred periodically to the standby server. If the active server goes down, the stand by server can be brought up by restoring all shipped logs. As this process will complete manually you can also jobs to copy backups to destination folder and other job use that backup to restore.

5) Snapshot is a static read only picture of database at a given point of time. It is used as snapshot replication. Data is updated to snapshot when you have mentioned SQL Agent to run snapshot job. You can perform this for to get offline database with latest data changes for reporting.

I will soon upload videos for each technique. Thanks.