November 30, 2008 at 11:01 am
Hi Folks,
I’m wondering how to migrate an Asp.net application, using MS SQL Express 2005 from one web host to another… without loosing data integrity.
The procedure to migrate from host to another, as I understand it is as follows:
1.) Make a backup of the Database, then install the DB to the new host
2.) Transfer the .aspx pages and the web files…
So far so good… however the site I want to migrate, is one where people may signup and create accounts at anytime… as well as do a variety of operations, that need to be tracked and accurate.
So I’m wondering how to insure the integrity of the data during the transfer…
For example, once I have a backup of the db on the old host…by the time I install the db to the new host, a user might have created a new account on the old host…so the new host would loose that user account…
Also, it takes 72 hours for all DNS servers to point to the same server…so for this period of time, I might get some traffic on either of the servers, so the dabases might get out of sync…and loose data integrity…
So, I’m wondering what strategies should be implemented to handle this situation.
Regards,
- Joel
November 30, 2008 at 12:20 pm
joel_langlois (11/30/2008)
So I’m wondering how to insure the integrity of the data during the transfer…For example, once I have a backup of the db on the old host…by the time I install the db to the new host, a user might have created a new account on the old host…so the new host would loose that user account…
SOP is to shutdown for the migration. Alternatively, implement a read-only mode, with an announcement to explain it. If these are insufficient, there are more difficult and complicated ways to shorten the Gap period.
Also, it takes 72 hours for all DNS servers to point to the same server…so for this period of time, I might get some traffic on either of the servers, so the databases might get out of sync…and loose data integrity…
SOP here is, first establish an initial (but different) name in DNS for your new site about a week before the switch. Then, after the data migration (above), start the new site on the application and "live" data. Then restart the old site with a redirect to the new site using the initial DNS name. After 3 to 7 days, you should be able to take down the old site and remove the initial DNS name.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 30, 2008 at 1:40 pm
Hi Barry, thanks for the reply!
There are somethings I don't quite follow in your suggestions.
I'm not exaclty sure what you mean by "initial (but different name) in DNS for the new site"
What I seem to understand is to use a "temporary address" for the new location of the site...for example using a preview address, or maybe even an I.P address...then redirect the old server to use the new server.
This procedure would ensure that while the DNS servers are updating, people who go to the old server will actually be redirected to the new server. But, I don't know what you mean by "Different Name in DNS"?
Also, white I have to redirect the old site to the new one...or even shutdown the site...
At that very moment, there could be someone doing a transaction, and that transaction may be imcomplete due do the site shutown, or re-direction command..causing loss of data integrety.
I can minizmize the risk of someone doing a transaction while I do a change, by doing this late at night, but still I have no gurantees...that some transaction won't be "truncated" by a shutdown or redirection in the middle of their process.
So, how to shutdown a site, or redirect a site, while assuring that all process which have been started are completed... before putting the change in effect.
I really whish to minimize or elimiate completely "downtime". I've heard of things called s-tunnels (I'm trying to learn more about it...)...to sync two DB's. Is this a good option?
Regards,
- Joel
November 30, 2008 at 1:59 pm
joel_langlois (11/30/2008)[hrI'm not exaclty sure what you mean by "initial (but different name) in DNS for the new site"
What I seem to understand is to use a "temporary address" for the new location of the site...for example using a preview address, or maybe even an I.P address...then redirect the old server to use the new server.
This procedure would ensure that while the DNS servers are updating, people who go to the old server will actually be redirected to the new server. But, I don't know what you mean by "Different Name in DNS"?
Yes, you've got it. Don't worry about that phase, I am not really a Web/DNS guy and I use the wrong terms all the time.
Also, white I have to redirect the old site to the new one...or even shutdown the site...
At that very moment, there could be someone doing a transaction, and that transaction may be imcomplete due do the site shutown, or re-direction command..causing loss of data integrety.
There is no problem of this when diond a shut down, because SQL Server will preserver the transactional integrity. You should shutdown the web site or the application first, then the database. If some user is still in with a transaction in-progress, it will be canceled, rolled-back and an error returned to the user. This preserves transactional integrity.
I really whish to minimize or elimiate completely "downtime". I've heard of things called s-tunnels (I'm trying to learn more about it...)...to sync two DB's. Is this a good option?
Sorry, never heard of it.
But I do want to caution you about this: The simplest and most reliable migration path is a clean shutdown and restart. There are ways to shrink this downtime window, but it involves increasingly greater cost and risk.
And yes, there are ways to do it with virtually no downtime, but these are also the most complicated (and thus risky) and the most expensive. What I would recommend you do is to get an estimate of the "effective cost" of downtime to your business. Then use this per Hour or per Minute cost to determine how much downtime you can afford vs. how much you are willing to spend on the migration to reduce the downtime.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply