Solved

SQL 2008 Mirror for DR

Posted on 2012-03-28
18
431 Views
Last Modified: 2012-08-13
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.

DBA100.
0
Comment
Question by:marrowyung
  • 9
  • 9
18 Comments
 
LVL 14

Accepted Solution

by:
alimu earned 500 total points
Comment Utility
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.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
alimu,

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?

DBA100.
0
 
LVL 14

Expert Comment

by:alimu
Comment Utility
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.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
alimu,

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.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
Any doc say the differece between rolling upgrade and a full upgrade?

That link say nothing about that.
0
 
LVL 14

Expert Comment

by:alimu
Comment Utility
"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.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
alimu,

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.

Thanks.

DBA100.
0
 
LVL 14

Expert Comment

by:alimu
Comment Utility
thankyou, I think you need to explain what you mean by rolling vs full.  It's not terminology I am familiar with.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 14

Expert Comment

by:alimu
Comment Utility
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.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
alimu,

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?

DBA100.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
alimu,

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?

DBA100.
0
 
LVL 14

Expert Comment

by:alimu
Comment Utility
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?
0
 
LVL 14

Expert Comment

by:alimu
Comment Utility
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.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
alimu,

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?

DBA100.
0
 
LVL 14

Expert Comment

by:alimu
Comment Utility
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?
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
alimu,

---------------------------------
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?

DBA100.
0
 
LVL 14

Expert Comment

by:alimu
Comment Utility
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?
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now