September 2, 2008 at 8:38 am
In a previous post I asked about replication between and old production server and a new production server. We're going to go ahead with replication between these 2 servers. However, I don't have any experience at setting up replication and the system administrator has had problems with it. So I would like to know how we can set up replication between the old production database server and the new production database server. It will probably last for a while, perhaps a week at most, long enough for us to change the connection strings in all of our applications from the old server to the new server, and then we'll break the connection between the two.
How do we do that?
Kindest Regards, Rod Connect with me on LinkedIn.
September 2, 2008 at 9:02 am
Rod at work (9/2/2008)
In a previous post I asked about replication between and old production server and a new production server. We're going to go ahead with replication between these 2 servers. However, I don't have any experience at setting up replication and the system administrator has had problems with it. So I would like to know how we can set up replication between the old production database server and the new production database server. It will probably last for a while, perhaps a week at most, long enough for us to change the connection strings in all of our applications from the old server to the new server, and then we'll break the connection between the two.How do we do that?
That is a very deep topic whose details I have just started diving into.
But, if you are using SQL Server 2005 /2008 the available wizards make basic replication very simple once you figure out which type of replication you want to set up.
You may want to look into a book that covers it with some depth like Professional SQL Server 2005 Administration (its the one I used the first time I set it up), or if you really want all the nuances there are several books out there that focus explicitly on replication.
If you want more advice here, can you provide any more details as to your setup? Do you need information to flow both ways, or only one? Are we talking 2000, 2005, or 2008 or a mix?
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
September 2, 2008 at 9:07 am
Based on your description of having the app potentially pointing at two servers as you change connection strings the only method of replication would be to enable Merge replication. You should definitely read up on this. All tables that would be receiving updates / inserts / deletes from the app will need to be included in the merge replication. You should use a seperate server as your distributor so that you don't overwhelm either of the database servers with managing the replication traffic. This is a significant project unless you are working with a small database with a few tables (articles). Changes to the tables following initiation of replication will have to be done with care. There are a lot of considerations that need to take place.
Side note and I will leave it at this, if you were to do this as a one-time cutover, you could rename the server after the old server is offline and then you would not have to update any connection strings. I have done this on multiple occassions in the past and it works great. You can also then rename the old server and bring it back online if you want as a place to refer back to for a period of time following the cutover.
If you have more specific questions about configuring merge replication please post and I will try to respond as quickly as possible.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 2, 2008 at 10:18 am
Thank you, Timothy and David for your replies. I don't think we have a copy of SQL Server 2005 administration books around, but it sounds as though that would be a wise investment.
To answer your question, Tim, our servers are both Windows 2003 Servers, although the new server is a 64-bit version of Windows 2003 Server. Both are running SQL Server 2005, 32-bit versions, I believe.
Kindest Regards, Rod Connect with me on LinkedIn.
September 4, 2008 at 9:17 am
I'm reminded of old science fiction tv shows where the all-knowing computer responds 'insufficient data'. I agree that what you're describing is a merge replication scenario. However, some considerations:
Is this a 24/7 availability database? Or do you need to run only during normal / extended business hours with 'downtime' for backups and maintenance processes? Without knowing the business needs, it's hard to give solid guidance. In particular, the concept of a 'week' in parallel is based on information we don't have - there may be a better / simpler concept if we can address the anxieties that prompted the 'need' for a week of overlap.
The literature on replication can easily become overwhelming. It may be perfectly appropriate for you situation, or it could be a totally unnecessary complication instead of the suggestion above to use the same server name and just swap boxes out after loading all the databases.
Are you concerned over the switch to a 64-bit OS and that's why the week? Is the database a 'run-of-the-mill' or are there particular esoteric tricks implemented in the application that raises concerns over a 64-bit machine?
September 4, 2008 at 10:07 am
You've asked some good questions, Mr or Mrs 500, so let me see if I can answer them.
Yes, the databases are available 24/7 through our web applications. However, the nature of our business is such that most users only access it during normal business hours. (We're a nonprofit agency that have contracting agencies enter services for reimbursement through our web application.) However, there are times when our users will use our application almost all night long, as they are in a rush to get in services before invoicing. We have invoices every other week, so the week of the invoice our ASP.NET apps are being hit constantly. But the other week it is pretty quiet. That's why I was thinking of a week before doing the cut over. As it turns out, it is too late now for us to make the switch this week, simply because we're not ready with our applications' connection string, testing, etc. Next week will be our invoice period, and so I don't personally see the switch taking place until at least next week Friday, more likely the week after.
I'm not so concerned about the 64-bit OS, as we're running the 32-bit version of SQL Server 2005 on it.
I think our database schema is pretty run of the mill; nothing esoteric that I can think of.
I hope that I have answered the questions you asked of me.
Kindest Regards, Rod Connect with me on LinkedIn.
September 4, 2008 at 7:28 pm
Yes, if you need information to flow both ways and you are using SQL Server 2005, I'll agree with most of the other people here and say merge replication is the way to go.
If it turns out you only need it to go from one server to the other and never the other way around, then transactional replication is easier to set up.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
September 5, 2008 at 8:14 am
Rod at work (9/2/2008)
In a previous post I asked about replication between and old production server and a new production server. We're going to go ahead with replication between these 2 servers. However, I don't have any experience at setting up replication and the system administrator has had problems with it. So I would like to know how we can set up replication between the old production database server and the new production database server. It will probably last for a while, perhaps a week at most, long enough for us to change the connection strings in all of our applications from the old server to the new server, and then we'll break the connection between the two.How do we do that?
"We're going to go ahead with replication between these 2 servers."
Why ?
Is this the other post ?
http://www.sqlservercentral.com/Forums/Topic561291-149-1.aspx#bm561398
If so, you received good advice to NOT use replication for this task, and to do a backup & restore instead. Re-read DavidB's reply to you. I agree. Using replication for a 1 time SQL build on a new server is foolish and will take 5-10 times as long considering the learning curve you have. (unless I'm misunderstanding your project)
Replication is useful for updating and maintaining data between 2 different databases (on the same box, or across the country) day after day, but it's cumbersome to setup & learn.
September 8, 2008 at 9:35 am
Homebrew, you've put me into an awkward position. How do I answer your question, without, well, making it look bad for others. I'll try the best I can.
We're going to do replication because the system administrator wants to do it, period. End of story. Apparently the arguments I've presented, which I got here, have not convinced him otherwise.
Kindest Regards, Rod Connect with me on LinkedIn.
September 8, 2008 at 9:51 am
If you have ongoing needs to keep location 2 updated with data from location 1, day after day, week after week, then replication is the way to go. If you need to get the database from A over to B, and then you will be using B going forward, then backup & restore is the way to go.
Give them this rough analogy.
You need to get your car from Point A to point B. You can not have any wear of the tires, no loss of gasoline, no engine wear (no data loss).
With rplication, you will need to:
- Buy a manual so you can learn to disasseble the car
- Possibly pay someone to help you
- Remove tires, engine, transmission, axles, doors, hood, trunk, gas tank, seats, dashboard ...
- Carry each peice to location B
- Reassemble the car. That's replication.
Backup & restore ...
- Put the car on a flatbed truck
- Drive it to location B
- Take it off the truck.
If you NEED replication, then go for it, but unless I've misunderstood the situation (quite possible), then it's not the best solution
September 8, 2008 at 11:45 am
Good Luck !
There are other IT tools outside of SQL, used for pushing data to different locations, that use the word "replication" in their name or description. Perhaps some people in your group are confusing those other tools with SQL replication, and that's why they think it's the best tool to use ?
September 8, 2008 at 1:08 pm
homebrew01 (9/8/2008)
Good Luck !There are other IT tools outside of SQL, used for pushing data to different locations, that use the word "replication" in their name or description. Perhaps some people in your group are confusing those other tools with SQL replication, and that's why they think it's the best tool to use ?
That could very well be. The people I work with are good, and so perhaps that is exactly what's happening.
Kindest Regards, Rod Connect with me on LinkedIn.
September 8, 2008 at 1:36 pm
homebrew01 (9/8/2008)
Good Luck !There are other IT tools outside of SQL, used for pushing NON-SQL data to different locations, that use the word "replication" in their name or description. Perhaps some people in your group are confusing those other tools with SQL replication, and that's why they think it's the best tool to use ?
Clarification
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply