May 16, 2014 at 9:19 am
Hello
I am currently setting up a disaster recovery for a customer but I have few questions.
My setup will be for testing Windows Server 2008 R2 + SQL Server 2012 SP1 CU7. SAP systems will be running on MSSQL DB.
Test systems and disaster site will use asynchronous mirroring. When their systems go live they will use AlwaysOn + Async mirroring.
My first question is;
I set up async mirroring between test and DR. I took a full backup of source system and restored it to destination with norecovery option. I gave a domain administrator user as service accounts and all was fine. Problem is I cannot switch over between servers, SQL says I need to switch to synchronous mirroring to do that but my customer doesn't want that. Also to test my scenario I shut down principal server and brought mirrored server by using "restore database SID with recovery" and then my SAP application started without problems but when source system came back online I couldn't set up mirroring again. Error message was
TITLE: Database Properties
------------------------------
An error occurred while starting mirroring.
------------------------------
ADDITIONAL INFORMATION:
Alter failed for Database '...'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3393.0+((SQL11_SP1_QFE-CU).131025-1850+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Database "..." is not configured for database mirroring. (Microsoft SQL Server, Error: 1416)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3393&EvtSrc=MSSQLServer&EvtID=1416&LinkId=20476
If I delete source DB and then restore mirrored DB back to source and then set up mirroring with sync mirroring and then failover to source and after that switch to async setting it works. Problem is when they go live DB size will be more than 3 TB and restoring from backup wouldn't be an option.
How can I solve it?
My second problem is, how can I setup AlwaysOn + Async mirroring? AlwaysOn will be used on production site (Two servers as primary for main DB operations and one secondary for backups and reports) and async mirroring to disaster site. Is it possible to do that?
Best regards
May 16, 2014 at 9:40 am
Regarding mirroring, the act of of switching over requires you putting the mirroring into synchronous mode.
The process you described essentially broke the mirroring.
Any failover script of an asynchronously mirrored pair will have at least three steps.
1. put mirroring into synchronous mode
2. do the switch
3. put mirroring into asynchronous mode
Otherwise every time you failover you must rebuild the mirroring.
Why do you not want to put the mirroring into synchronous mode fro the short period of the actual failover?
Presumably the applications have been shut down.
Are you mirroring over WAN?
Do you have latency? (have you checked latency during index rebuilds?)
May 16, 2014 at 9:48 am
Hello
They don't want synchronous mirroring because it will affect their performance and also if I know correctly even one synchronized mirror fails all applications will stop until it is restored.
As for site locations my customer will use a gigabit fiber connection between their branches supported by ssd on servers.
Also so far I learned if I lose my principal server and continue on mirrored server I can't go back without restoring my full backup. Am I wrong on that?
Best regards
May 16, 2014 at 10:07 am
Yes in your failover set up, you must rebuild mirroring every time you will failover.
Re Async:
I have in the past managed geographically redundant async mirrored pairs.
With async mirroring, failover is usually manual and will require some non-trivial application outage.
That means it is feasible to have a failover process that locks out the applications (e.g. lock logins).
Applications may even be shut down during failover.
In this scenario, there is no reason why you cannot temporarily set the mirroing to sync mode only as a part of the failover.
Furthermore, if you persist with your current process, you cannot guarantee tha all the data has made it over to the mirror before you promote it to principal.
I assume your managers prefer a short outage over data loss.
After failover, how do the applications switch to point to the newly promoted principal?
How are you ensuring that all logins are synced between the two instances?
May 16, 2014 at 10:12 am
Also, what is the failover requirement?
Automatic or managed?
May 16, 2014 at 10:28 am
Hello
Failover management will be manually. First they will get an approval from higher ups and after they say it is a disaster situation they will switch to disaster site.
If I switch to synchronous mirroring only for failover changes and then switch back to asynchronous mirroring will that have an impact on database or application?
There will be Windows cluster on production system, SAP servers will get only one IP address to connect SQL DB.
After switching to disaster site I will manually adapt all necessary IP address in SAP to look for new DB IP.
Another thing is, are there no other ways to reenable mirroring without restoring full db? It will take lots of time to restore a 3 TB db.
May 16, 2014 at 12:09 pm
KTurkes (5/16/2014)
if I know correctly even one synchronized mirror fails all applications will stop until it is restored.
No, that would completely defeat the point of mirroring as a High Availability option.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2014 at 12:14 pm
GilaMonster (5/16/2014)
KTurkes (5/16/2014)
if I know correctly even one synchronized mirror fails all applications will stop until it is restored.No, that would completely defeat the point of mirroring as a High Availability option.
But is it not waiting for commit transaction verification? If one server fails transaction verification will fail and db will wait for us to fix?
May 16, 2014 at 12:18 pm
KTurkes (5/16/2014)
GilaMonster (5/16/2014)
KTurkes (5/16/2014)
if I know correctly even one synchronized mirror fails all applications will stop until it is restored.No, that would completely defeat the point of mirroring as a High Availability option.
But is it not waiting for commit transaction verification?
No, if the one database disappears, the other will continue/start processing in a disconnected mode until the other DB comes back.
If one server fails transaction verification will fail and db will wait for us to fix?
If that was the case, why on earth would anyone ever implement sync mirroring, if it had no advantages and only disadvantages?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2014 at 12:23 pm
GilaMonster (5/16/2014)
KTurkes (5/16/2014)
GilaMonster (5/16/2014)
KTurkes (5/16/2014)
if I know correctly even one synchronized mirror fails all applications will stop until it is restored.No, that would completely defeat the point of mirroring as a High Availability option.
But is it not waiting for commit transaction verification?
No, if the one database disappears, the other will continue/start processing in a disconnected mode until the other DB comes back.
If one server fails transaction verification will fail and db will wait for us to fix?
If that was the case, why on earth would anyone ever implement sync mirroring, if it had no advantages and only disadvantages?
So compared to async mirroring sync mirroring's only disadvantage is performance impact?
May 16, 2014 at 12:31 pm
KTurkes (5/16/2014)
So compared to async mirroring sync mirroring's only disadvantage is performance impact?
Pretty much, yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2014 at 12:38 pm
Also remember, distributed transactions and cross-database transactions are not supported. That is their biggest drawback, in my opinion. Of course, if you don't have either of those, go ahead. Also, with async, if your primary goes down you have the potential of losing data.
Jared
CE - Microsoft
May 16, 2014 at 12:39 pm
If you are using Availability Groups, why are you using mirroring too? Why not have an asynchronous replica in AG?
May 16, 2014 at 12:44 pm
I have very little knowledge about AlwaysOn, and according to our SAP on MSSQL docs it should be in a MSCS environment. Alwayson servers will be in a cluster and disaster server will operate standalone and that was our main reason to plan such thing. If main site goes down disaster site will go up and when crisis is over we will reconfigure everything in prod site again.
May 16, 2014 at 12:52 pm
You can't use the secondary server for backups and reports with clustering. You can only do that with Availability Groups.
Also, MCSC doesn't exist any more. It was renamed to WFCS in Windows 2008. AlwaysOn is an umbrella term that covers a variety of HA technologies.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply