Interview Questions on SQL Server Database Mirroring


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

Refer for more on SQL Server Database Mirroring

http://mssqlfun.com/2014/11/13/script-to-monitor-sql-server-database-mirroring-status/

http://mssqlfun.com/2014/11/10/add-witness-to-existing-sql-server-database-mirroring/

http://mssqlfun.com/2014/11/03/sql-server-database-mirroring-monitoring/

http://mssqlfun.com/2014/10/29/add-database-file-on-database-invloved-in-db-mirroring-3/

http://mssqlfun.com/2014/10/27/remove-witness-from-sql-server-database-mirroring/

http://mssqlfun.com/2014/10/14/database-mirroring-states-sql-server/

http://mssqlfun.com/2014/10/13/configure-sql-server-database-mirroring/

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Script to Monitor SQL Server Database Mirroring Status


SQL Server Database Mirroring is fantastic HA solution developed by Microsoft. Microsoft lunch database mirroring with SQL Server 2005. Database Mirroring send logs to mirror from principle. We need to monitor Database Mirroring status continuously to ensure that everything is working fine. We can use MSDB.SYS.SP_DBMMONITORRESULTS system store procedure to monitor database mirroring.

Below script will convert number to actual values during monitoring. You can use below script to monitor the database mirroring status.

CREATE TABLE #DBMRESULTS

(

DATABASE_NAME VARCHAR(255),

ROLE INT,

MIRRORING_STATE TINYINT,

WITNESS_STATUS TINYINT,

LOG_GENERAT_RATE INT,

UNSENT_LOG INT,

SENT_RATE INT,

UNRESTORED_LOG INT,

RECOVERY_RATE INT,

TRANSACTION_DELAY INT,

TRANSACTION_PER_SEC INT,

AVERAGE_DELAY INT,

TIME_RECORDED DATETIME,

TIME_BEHIND DATETIME,

LOCAL_TIME DATETIME

)

INSERT INTO #DBMRESULTS

EXEC MSDB.SYS.SP_DBMMONITORRESULTS ‘USERDB1′, 0,1

SELECT DATABASE_NAME,

(CASE ROLE

WHEN 1 THEN ‘PRINCIPAL’

WHEN 2 THEN ‘MIRROR’

END) ROLE_OFDB_QUERY_FIRED,

(CASE MIRRORING_STATE

WHEN 0 THEN ‘SUSPENDED’

WHEN 1 THEN ‘DISCONNECTED’

WHEN 2 THEN ‘SYNCHRONIZING’

WHEN 3 THEN ‘PENDING FAILOVER’

WHEN 4 THEN ‘SYNCHRONIZED’

END) MIRRORING_STATE,

(CASE WITNESS_STATUS

WHEN 0 THEN ‘UNKNOWN’

WHEN 1 THEN ‘CONNECTED’

WHEN 2 THEN ‘DISCONNECTED’

END) WITNESS_STATUS,

LOG_GENERAT_RATE,

UNSENT_LOG,

SENT_RATE,

UNRESTORED_LOG,

RECOVERY_RATE,

TRANSACTION_DELAY,

TRANSACTION_PER_SEC,

AVERAGE_DELAY,

TIME_RECORDED,

TIME_BEHIND,

LOCAL_TIME

FROM #DBMRESULTS

DROP TABLE #DBMRESULTS

http://gallery.technet.microsoft.com/Script-to-Monitor-SQL-3d25e132

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Add Witness to Existing SQL Server Database Mirroring


1. After connecting to the principal server instance, Right-click the database & go to properties. Select Mirroring from left side option.

2. Click Configure Security, Configure Database Mirroring Security Wizard welcome screen appears, click Next.

3. In the Include Witness Server dialog box, click Yes, and then click Next.

4. In the Choose Servers to Configure dialog box, the Witness server instance check box is automatically checked. Click Next.

5. On the Principal Server Instance dialog box, keep the existing port and endpoint. Click Next.

6. On the Witness Server Instance dialog box, click Connect. In the Connect to Server dialog box, specify the witness server instance in the Server name field, and use Windows Authentication (the default). Click Connect. In the Connect to Server dialog box, specify the witness server instance in the Server name field, and use Windows Authentication (the default). Click Connect. Once a connection is established, the listener port and database mirroring endpoint of the witness server instance are displayed on the Witness Server Instance dialog box. Click Next.

7. The Service Accounts dialog box contains fields for the domain service accounts of the principal, mirror, and witness server instances.

8. On the Complete the Wizard summary screen, optionally, verify the witness configuration, and then click Finish.

9. Configuration Completed.

10. On finishing, the wizard returns you to the Database Properties dialog box where the server network address of the witness now appears in Witness field. Also, High-safety mode with automatic failover (synchronous), which is required with a witness, is automatically selected. To enable the witness and change the session to high-safety mode with automatic failover, Click OK.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

SQL Server Database Mirroring Monitoring


SQL Server Database Mirroring keeps running all time to sync data between Principle & Mirror database. We need regular monitoring of Database Mirroring to ensure that everything is working fine.

We are monitor Database Mirroring by 2 ways :-

1) SSMS : Connect to Principle Server > Select Database > Right Click , Go to Task & Select Launch Database Mirroring Monitor

2) T-SQL Script : You can use sp_dbmmonitorresults system store procedure for the same. We care create customized scripts using this system store procedure for more detail output & email alerts.

database_name : Specifies the database for which to return mirroring status.

rows_to_return

Specifies the quantity of rows returned:

0 = Last row

1 = Rows last two hours

2 = Rows last four hours

3 = Rows last eight hours

4 = Rows last day

5 = Rows last two days

6 = Last 100 rows

7 = Last 500 rows

8 = Last 1,000 rows

9 = Last 1,000,000 rows

update_status

0 = Does not update the status for the database.

1 = Updates the status for the database by calling sp_dbmmonitorupdate before computing the results.

Exec sp_dbmmonitorresults ‘<DB Name>’

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Add Database File on Database invloved in DB Mirroring


Originally posted on MSSQLFUN:

Database Mirroring works on transaction logs. It applies transaction logs from Principle to Mirror. But Operation like addition of file is no log operation because of that file not be added to Mirror automatically.

Steps to Add Database File on Database involved in DB Mirroring :-

1) Break the Mirroring

ALTER DATABASE USERDB1 SET PARTNER OFF

2) Add New Data or log file, whatever is required

USE [master]

GO

ALTER DATABASE [UserDB1] ADD FILE

( NAME = N’UserDB1_1′,

FILENAME = N’H:MSSQL12.INS1MSSQLDATAUserDB1_1.ndf’ ,

SIZE = 3072KB , FILEGROWTH = 1024KB )

TO FILEGROUP [PRIMARY]

3) Take Transaction Log backup at Primary

BACKUP LOG USERDB1 TO DISK = ‘C:USERDB1_LOG.TRN’

4) Restore Step 3 Transactional Backup on Mirror with Move option

RESTORE LOG [USERDB1] FROM

DISK = N’C:USERDB1_LOG.TRN’

WITH Move N’UserDB1_1′ TO

N’I:MSSQL12.SQL14I2MSSQLDATAUserDB1.ndf’,

NORECOVERY, NOUNLOAD, STATS = 10

5) Reestablished DB Mirroring again

First on Mirror

ALTER DATABASE USERDB1

SET PARTNER = ‘TCP://SQL2014.RohitGarg.local.in:5022′

View original 91 more words

Add Database File on Database invloved in DB Mirroring


Add Database File on Database invloved in DB Mirroring.

Cumulative Update – 4 for SQL Server 2014 RTM Is Now Available !


The 4thcumulative update release for SQL Server 2014 RTM is now available for download at the Microsoft Support site. Cumulative Update 4 contains all the hotfixes released since the initial release of SQL Server 2014 RTM.

Those who are facing severe issues with their environment, they can plan to test CU4 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP1 final release to deploy on your production environment, to have consolidate build.

KB Article For CU4 of SQL Server 2014 RTM

Previous Cumulative Update KB Articles:

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

categories

Follow

Get every new post delivered to your Inbox.

Join 238 other followers