SQL Server 2012 in a Mirror
Enable SQL Server for high availability by enabling them in a mirror setup, There are two modes of database mirroring – synchronous and asynchronous. With synchronous mirroring, transactions cannot commit on the principal until all transaction log records have been successfully copied to the mirror (but not necessarily replayed yet). This guarantees that if a failure occurs on the principal and the principal am mirror are synchronized, committed transactions are present in the mirror when it comes online – in other words, it is possible to achieve zero data loss.
Synchronous mirroring can be configured to provide automatic failover, through the use of a third SQL Server instance called the witness server (usually hosted on another physically separate server). The sole purpose of the witness is to agree (or not) with the mirror that the principal cannot be contacted. If the witness and mirror agree, that mirror can initiate failover automatically. If synchronous mirroring is configured with a witness, the operating mode is known as high-availability mode and povides a hot standby solution. When no witness is defined, the operating mode is known as high-safety mode, which provides a warm standby solution.
With asynchronous mirroring there is no such guarantee, because transactions can commit on the principal without having to wait for database mirroring to copy all the transaction’s log records. This configuration can offer higher performance because transactions do not have to wait, and it is often used when the principal and mirror servers are separated by large distances (that is, implying a large network latency and possible lower network bandwidth). Consequently, the operating mode is also known as high-performance mode and provides a warm standby solution.
If a failure occurs on the principal, a mirroring failöver occurs, either manually (in the high-performance and high-safety modes) or automatically (only in the high-availability mode). The mirror database is brought online after all the transaction log records have been replayed (that is, after recovery has completed). The mirror becomes the new principal and the applications can reconnect to it. The amount of downtime required depends on how long it takes for the failure to be detected and how much transaction log needs to be replayed before the mirror database can be brought online.
I created three VM’s and attached an extra disk. As was the case for the two domain controllers, and attach the disK, i removed some of info due to confidentiality, virtual machines for the two front-end servers, the two application servers and the three servers to be used by SQL Server. So let start and install .Net and go to Server Manager —>
select Manage —> Add Roles and Features, i used as sp_sql1
Next —> Install .NET Framework 3.5 Features
The installation has completed.
Download and attach media (run from DVD)
SQL Server 2012 SP1 Enterprise Edition.
attach download next —> Run the Setup.exe efter couple of mins this show up
Off course select the New SQL Server stand-alone installation next —>
Efter support file installation enter product key
Read & accept the terms and click Next —>
Select SQL Server Feature Installation Next —>
My case I did not require SSAS, SSIS or SSRS, but i did select to install the mgm tools. If you need those tools in future you can always come back and install.
If all is green click Next even process will be blocked you 🙂
Enough space for the installation Next —>
I keep the default settings, but if you plan to use this server in a mirror setup – Default setting I will recommend that you use a domain account. It will make setting up the security during the mirror configuration so much easier. The reason being that the local account on Server 1 does not know anything about the local account on Server 2.
Just keep default setting values you can always change them later using the SQL Server Configuration Manager. Database Engine Configuration dialog on the Server Configuration tab keep the default value for the Authentication Mode. Authentication Mode Click Next.
Select the Data Directories change the Data root directory to the additional disk we attached, drives C, D, E, F:\
You want to select both the Enable FILESTREAM for Transact-SQL access and the Enable FILESTREAM for file I/O access options.
If all is green in the Installation Configuration Rules. The installation will begin and it is take a time, all should be green then go next otherwise fixed error then click Next —>
Note:- Repeat the above process for the other two SQL Servers. Once all are installed we will have the primary, mirror partner and witness servers and we are ready to enable the mirror.
However, before actually do this, we need to install SharePoint. The reason is that the mirror is enabled by backing up and restoring databases, hence we need something “in” SQL so to speak. As I am a SharePoint Admin also, nothing to worry. I need to config in farm site in SP and SQL in order to fully work.
Start a working SharePoint installed in to the primary SQL Server –SP-SQL1 (I given name to sp=SharePoint and SQL is SQL. To ensure that all SQL logins are present on the primary and mirror server, then must ensure that all databases are running with recovery mode set to FULL MODE. RDP into SP-SQL1 (the primary) and open op SQL Server Management Studio.
Note: there are no data connections to the SQL Server you may want to close down the SP site, use simple syntax, here we go!!!
ALTER DATABASE AdminContent SET RECOVERY FULL;
First take Full Backup the database
BACKUP DATABASE AdminContent
TO DISK = ‘D:\BackUp\AdminContent.bak’ –Path can be different i.e. e:\ or f:\
And the log:
BACKUP LOG AdminContent
TO DISK = ‘D:\BackUp\AdminContent_log.bak’
Copy the files to the mirror partner (SP-SQL2). Ensure they are placed in the same location, e.g. D:\BackUp in the above example. It is not a requirement, but the syntax of the T-SQL is slightly different if the location is different. Connect to the mirror partner (SP-SQL2) from the open Management Studio or RDP into the server and open SSMS from here.
First we restore the database
RESTORE DATABASE AdminContent
FROM DISK = ‘D:\BackUp\AdminContent.bak’ –Path can be different i.e. e:\ or f:\
And the log:
RESTORE LOG AdminContent
FROM DISK = ‘D:\BackUp\AdminContent_log.bak’
WITH FILE=1, NORECOVERY — with norecovery mode it must be.
SQL Server Always On technology is a good high availability option as compared with other high availability features like Mirroring, Log shipping and clustering.. SQL Server 2012 now has Always On technology, where the database can keep mirror copy/copies of the database that are constantly being refreshed. In the event of a failure of the Primary, it will seamlessly fail over to another copy. SQL Server 2012 Always On is employs mirroring and clustering together to achieve the high availability
SQL Server 2012 Always On — SQL Server Mirroring
This has significant disadvantages over traditional SQL Server Mirroring.
* In traditional mirroring we cannot manage groups of databases, so that when a single database failed they would all failover.
** There is only one Mirror of the database allowed
*** The Mirror is not useable unless you are prepared to work with Snapshots.
In SQL Server 2012 Always On Technology, SQL have Availability Group option, using which Databases can now be grouped together. When a failure happens, the entire group of database is treated as one. When one fails over they all fail over. This is very powerful when you have an application that uses more than one database in an instance.
SQL Server 2012 Always On — SQL Server Log Shipping.
Log shipping is sort of manual mirroring which allows more than one replica to be kept; perhaps a local one and a remote one. This is more difficult to setup and failover is not automatic The biggest downfall is we cannot use the receiving database since it is in a recovery state and read-only as the transaction logs are being applied. In SQL Server 2012 Always On Technology all the databases can failover automatically.
SQL Server Always On Versus SQL Server Clustering.
With SQL Server clustering, you are dealing with a shared storage system that adds cost and complexity to the equation. AlwaysOn doesn’t need to use shared storage. So here it is more cost effective.
Now ready to enable mirroring in SSMS right click on one of the databases and select Tasks and then Mirrror. Don’t forget to Configure Security in Database Properties. First step in the configuration wizard is to decide whether or not a witness server, here is using with a witness server. Good Luck 🙂
Let me know if you have any further question and comments is learning point. Back to top
Microsoft Certified Solutions Associate (MCSA)