SQL 2008 Mirror for DR

Dear expertist,

Right now we tried to create a new way to DR that when we are doing large deployment, the DB still up and running.

May I know if SQL 2008 (not R2) mirror can help when we are trying to upgrade the SQL 2008 server from SP1 to SP2 (for example), or upgrade the Windows the SQL server installed on top from Windows 2008 R2 to Windows 2008 R2 SP1:

1) keep the connection from application  while the DB is switching over to DR site.
2) Upgrade one of the DB first (primary or DR DB),
3) Once it is verify working good, then we switch over to DR DB to keep operation.
4) then we upgrade the primary DB and verify it is working good.
5) we bring the primary DB server as a primary again and leave the DR DB as usual.
6) upgrade done.

marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can use database mirroring as a high availability solution and rolling upgrades such as you describe are possible.  

This article goes into performing rolling upgrades in depth including a step by step process for mirrored SQL 2008 (not R2): http://msdn.microsoft.com/en-us/library/bb497962(v=sql.100).aspx

There will still be a small delay when the primary db role switches between databases, and you will also need to configure your application to automatically connect to the new server. Whether your application will cope with the pause and the redirection would need to be tested.  There is a good technet article http://technet.microsoft.com/library/Cc917713 on setting up automatic application redirection that may help you.

You can find a full overview of mirroring technology in SQL 2008 here if you have not read it already: http://msdn.microsoft.com/en-us/library/ms189852(v=sql.100).aspx

I hope this helps you to come to a decision.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:

Very good direction and I setup the mirror before but not this way.

In this new company, they are doing much different DR. the DR site and primary site has to be up on the day we are deploying so that downtime can be limited, or no downtime from the user's new requirement.

But before I read your link, let discuss more:
1) When we are upgrading, it seems that we need to upgrade the primary DB first. Then we should switch over to DR server, right?
2) Will system DB mirror to the DR server?
3) if we upgrade the mirror DB first, as all DB in the mirror server is not accessible, how can we upgrade that if there are code/schema change?
4)One important thing is, we are using SAN replication, will the mirror replication conflict with the SAN data replicatoin?

I can't give you any clearer guidance than what is in those documents.  If you have set the system up in a supported configuration and follow the instructions provided by Microsoft  carefully you should not have a problem.

1) Please read the documentation.

2)you have asked about whether the system DB is mirrored - mirroring is setup per database so it will only be mirrored if you have set it up to be so, to understand better how it works, you would need to read the article I posted above about how mirroring itself functions if you are not sure. I think it would be available in other languages on technet if you need that.

3) Please see 1) , also are you sure you are not using the word mirroring where you mean clustering? Make sure you understand the differences between mirroring of SQL databases and clustering of SQL servers. Having clustering does not necessarily mean you have mirroring configured, but mirroring configurations often involve the use of failover clusters. The two technologies are often but not always used together and if you are using both you will need to know how to factor this into your failover plan.

4) I'm not sure how your SAN replication functions so I can't comment.  It generally occurs at the hardware level and is transparent to the OS. If you are using SAN replication you are possibly using a clustered system. *generally* speaking the only time that SAN replication direction gets switched is when there is a DR test or a complete site failure but how it is handled in your company I don't know and you would need to seek advice from your SAN  and windows clustering technical staff.
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

marrowyungSenior Technical architecture (Data)Author Commented:

hi, I am sure will read the doc but you know the doc is usually simple enought and they cant' cover all situtation.

As a result I would like to seek more advice on my own situation. We have both cluster (HA) and DR solution that right now based on SAN replication, a lot of company trust this instead of the mirror but right now we really experience situation that we need more downtime during the deployment.

The SAN link, during this DR moment, will stopped/paused, and we can only do quick work DURING this moment. And if we use mirror this will break the standard but if it really works then we can support that.

That's why I need more advice on this.
marrowyungSenior Technical architecture (Data)Author Commented:
Any doc say the differece between rolling upgrade and a full upgrade?

That link say nothing about that.
"I am sure will read the doc but you know the doc is usually simple enought and they cant' cover all situtation"
I'm sorry but if you won't read the information I give you I can't do much more to help you. I am not here to reinvent the wheel, Experts here volunteer to help you out, we do not get paid and have our own jobs to do too.  The expectation of you as the asker of a question is that you are willing to put in a certain amount of effort yourself in solving your problem.

Regarding your questions about rolling vs full upgrade, I have never heard of either and am not sure what you mean. This may be a translation issue so if you explain more about what you mean I might be able to provide advice.
marrowyungSenior Technical architecture (Data)Author Commented:

I knew you helping me, but this is the last part of information I need.

The "rolling vs full upgrade" is the key part here and I read your doc, it don't show.

Might be let me open another question for it.


thankyou, I think you need to explain what you mean by rolling vs full.  It's not terminology I am familiar with.
marrowyungSenior Technical architecture (Data)Author Commented:
I have ask this one by other articles, someone answer me already and I am sure this is what we are doing now.

But right now the point is the down time is the time we do the database cluster failover.
ah, my apologies, now I understand what you were getting at.  
I was confused because you were discussing rolling upgrades for mirrored systems which is here http://msdn.microsoft.com/en-us/library/bb497962(v=sql.100).aspx vs the straight clustered system, which is the one you are now discussing in your other question, and then you had also mentioned stopping SAN replication which is usually your DR solution in the event of site loss - I'm not sure why you would stop this replication in an upgrade situation.

Whilst mirroring may minimise downtime as you're running a couple of sets of clusters (I assume this is the case since you said HA) each with one half of the mirror set and you could stagger it so you always have a live set of databases, you would still need some smarts in there to switch your client connections between each side of the mirror i.e. your server name changes when switching between sides of the mirror unlike a cluster failover which just moves the virtual server between nodes.

Apologies again for my confusion.
marrowyungSenior Technical architecture (Data)Author Commented:

We need to stop it or any change (Which is not confirmed yet) will replicate to the DR site. Once we confirm everything is ok we will upgrade the DR site.

Anything wrong here we will need to restore the DR site content back to primary site.

We have HA and we need to optimize the DR flow and minimize the down time.

How MySQL and oracle handle this kind of sitution?

marrowyungSenior Technical architecture (Data)Author Commented:

I have one more concern, What if during the rolling upgrade the SQL still serving , but when doing application deployment, DB schema needs to change, so this method (rolling upgrade) can't help at all, right?

with a rolling upgrade (even with mirroring involved) for OS or SQL you're always updating the passive node of the cluster.  Schema changes in databases on the active node shouldn't be a factor.  

Your example was upgrading from SP1 to SP2 but I wonder now if I are you talking about rolling upgrades to your line of business applications  (i.e. updating data inside your databases) or are you talking about hotfixes and service packs for your OS and SQL Server

I am still not sure of the benefit to stopping your underlying SAN disk replication since any upgrade would be happening on the cluster nodes that aren't actually running your sql server at the time.. wouldn't you want data changes to the sql server and databases replicated back in case there was a site failure?
Sorry, getting ahead of myself, if you have database mirroring you can't do any schema updates while you're doing the upgrade because your tables need to be exact copies and can't have different structures when you turn mirroring back on.
marrowyungSenior Technical architecture (Data)Author Commented:

Ok, then the mirror has some design problem that make me confuse in the way that how can I update the schema.

if I focus only on rolling upgrade by not using mirror, how can I do it by only rolling upgrade?

The straight rolling upgrade technique is the one you have discussed in your other question.  It does sound like you will have a problem with a rolling upgrade of a mirrored database if you can't stop schema updates for the duration.  Are schema updates something that needs to happen frequently for you? Can you ask people to delay any changes that will involve schema updates for the duration of the service packs/hotfixes?
marrowyungSenior Technical architecture (Data)Author Commented:

Can you ask people to delay any changes that will involve schema updates for the duration of the service packs/hotfixes?

We can't, or all developer will only wait for my deployement/change on DB.  This can wait for more than one hour. For 4 x nodes, it takes more than 2 hours, our release windows can last only 3 hours. We just can't only gives 1 hours for application to deploy.

Probably we need SQL 2012 so that we can take a SQL server aways from the DB DAG group and do the upgrade, and then plug it back to the group. we probably need a load balancer for the apps server so that when we going to upgrade the application tier, we unplug the application server from the load balancer and do the upgrade.

After application tier finish upgrade we plug the application server back to the load balancer and the site keep running.

Any other suggestion?

Your descriptions of what is being done here sound like you need to do rolling application deployments and updates to live multi-tier systems - a very complex problem.  
Your question was originally about service packs and hotfixes to OS and SQL Server 2008 in a mirrored and/or clustered configuration, which has a fairly easy to follow stepped out process.  
I am not sure I am understanding correctly which you are doing and these are two very different problems.. I'm sorry but can you just quickly confirm which you doing?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.