This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Can we configure mirroring between Standard Edition & Enterprise Edition or Vice Versa?
Nope, its not possible, both principal and mirror should have the same edition.
What are Quorum types in Mirroring?
A Quorum is the relationship between the Witness,Principal and the Mirror.Depending on the mode of operation it is divided into 3.
Full Quorum: This is when all 3 Witness,Principal and the Mirror can communicate with each other.Since witness is present automatic failover occurs.
Quorum: This state exist if the Witness and either partner can communicate with it.
Partner-to-Partner: When only the Principal and Mirror can communicate with each other.
Can we upgrade SQL while mirror is configured? (More details)
When upgrading server instances from SQL Server 2005 or SQL Server 2008 to SQL Server 2012, you can reduce downtime for each mirrored database to only a single manual failover by performing a sequential upgrade, known as a rolling upgrade.
A rolling upgrade is a multi-stage process that in its simplest form involves upgrading the server instance that is currently acting as the mirror server in a mirroring session, then manually failing over the mirrored database, upgrading the former principal server, and resuming mirroring.
Can Log Shipping and mirroring configure together?
Yes we can combine both together, please visit this official article for more details.
Does mirroring support FILESTREAM file group?
No:
Database mirroring does not support FILESTREAM. A FILESTREAM filegroup cannot be created on the principal server. Database mirroring cannot be configured for a database that contains FILESTREAM filegroups.
Does mirroring support cross database transaction or distributed transaction?
No:
Database mirroring is not supported with either cross-database transactions or distributed transactions. This is because transaction atomicity/integrity cannot be guaranteed.
During the mirroring session, can we increase the disk size of primary database?
Yes, You can increase the size of a disk.
Can we have a different collation setting on both the database?
No, It wouldn’t work.
T-SQL Script to enable mirroring.
|
1 |
ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://databasemirror.adatum.com:5022'; |
Specify the partner from the principal server:
|
1 |
ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://databasemaster.adatum.com:5022'; |
T-SQL Script to check associate ports with DB Mirroring.
|
1 |
SELECT type_desc, port FROM sys.tcp_endpoints; |
T-SQL Script to check the state of the DB Mirroring.
|
1 |
SELECT state_desc FROM sys.database_mirroring_endpoints |
T-SQL Script check the service account connect permission on the DB Mirror endpoints.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT EndPnt.name ,SvrPerm.STATE ,CONVERT(nvarchar(38), suser_name(SvrPerm.grantor_principal_id)) AS GRANTOR ,SvrPerm.TYPE AS PERMISSION ,CONVERT(nvarchar(46),suser_name(SvrPerm.grantee_principal_id)) AS GRANTEE FROM sys.server_permissions SvrPerm, sys.endpoints EndPnt WHERE SvrPerm.major_id = EndPnt.endpoint_id ORDER BY Permission, grantor, grantee; GO |
T-SQL Script to check the DB Mirror timeout and resetting the DB Mirror timeout.
|
1 2 |
SELECT mirroring_connection_timeout FROM sys.database_mirroring; |
The most interesting issue in DB Mirroring you will ever see.
Before & After configuration of Database Mirroring , What should be our checklists?
- The principal database and the mirror database should be on separate physical hardware, and ideally, in different physical locations.
- The witness server should be on separate physical hardware, and be on a separate network (best if at a third location).
- Initial database mirroring setup should be done during less busy times, as the setup process can negatively affect performance of the production database being mirrored.
- Use high availability mode whenever possible, and high performance mode only when required.
- While a fast connection is not required between mirrored servers, the faster the connection, and the better quality the connection, the better.
- You will want to optimize the performance of the mirrored database as much as possible to reduce the overhead caused by the mirroring process itself.
- Thoroughly test database mirroring before putting it into production.
- Monitor database mirroring daily to ensure that it is working properly, and is meeting performance goals.
- Develop a formal operational and recovery procedure to support mirroring. Periodically test the failover process to ensure that it works.
How to move database files of a Mirrored SQL Server Database?
What are your steps to apply a service pack or patch to Mirrored SQL Server Databases?
- Step 1: Always backup all system and user databases before applying patches.
- Step 2: Remote Desktop into the “Mirror” server (SSQL2 in our example) and download/copy the patch to the server.
- Step 3: Stop all SQL Services on the “Mirror” server.
- Step 4: Run the patch on the “Mirror” server.
- Step 5: Once the patch is complete, reboot the “Mirror” server.
- Step 6 (optional): If your database mirroring is set to asynchronous (High Performance mode), we will need to synchronize the databases first.
- Step 7: The databases might change to “synchronizing” while the transactions catch up.
- Step 8 (optional):Once the failover has taken place and all the databases have switched roles we can set the operating mode back to asynchronous.
- Step 9: Remote Desktop into the “new” “Mirror” server (SSQL1 in our example) and download/copy the patch to the server.
- Step 10: Stop all SQL Services.
- Step 11: Run the patch on the server.
- Step 12: Once the patch is complete, reboot the server (SSQL1).
- Step 13: Upon reboot and login, make sure all databases come online clean and synchronized.
- Step 14: You now have two choices, leave the databases alone and leave SSQL2 as the principal and SSQL1 as the mirror or you can fail back to return the environment back to how it was from the beginning.
- More Details
