SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 72 Training Environments
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mfemenel
Professor Frink

USA
1421 Posts

Posted - 05/09/2006 :  11:55:52  Show Profile  Visit mfemenel's Homepage  Reply with Quote
We've been requested to set up up to 72 training environments (starting with 24) for our application. We'll need 1 database for each environment. I was thinking we'd do one master database and then 24 databases that should always mirror this master db. There is a web interface for users to change data in the master db that will trickle down to the other environments. What is the best way to do this? I've never really spent much time with replication and always chosen the log shipping option instead. Since that leaves the db read only however, this doesn't do me much good in this scenario. I was thinking replcation would be a good tool to use here but I'm not sure about having up to 72 subscribers. Is that possible? I'm open to ideas here.

Mike
"oh, that monkey is going to pay"

druer
Constraint Violating Yak Guru

USA
314 Posts

Posted - 05/09/2006 :  13:08:16  Show Profile  Reply with Quote
I'm not sure what you are trying to do Mike.

What kind of changes are users making to Master, and why do those need replicated to other machines?
Have you considered having all 24 databases on the same server, so there is only 1 Master?

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."
Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 05/09/2006 :  13:17:48  Show Profile  Visit mfemenel's Homepage  Reply with Quote
I'll try to explain in a little more detail. We have a production db today that has offer data and customer data. In order to get customers to renew their contracts the system lets them make one of these offers to a customer if they renew for another year. In training we'll have test accounts and test offers. There will be changes to these offers periodically to reflect "production like" offers but it won't be a true mirror of production since that's way too much data for training. We'll have 3 servers, 1 is a web server providing the interface to a master training db that will let a user change offers. We'll have 2 database servers, 12 environments on each. What I'd like is when a user makes a change to offers in the web app/master db, that those changes reflect across all 24 training databases. The customer information will be consistent already and there is no need for that piece of it to replicate across environments. Is that clearer?

Mike
"oh, that monkey is going to pay"
Go to Top of Page

druer
Constraint Violating Yak Guru

USA
314 Posts

Posted - 05/09/2006 :  13:42:26  Show Profile  Reply with Quote
I think I'm reading you now. You don't mean "SQL Servers MASTER database" you mean the "data master" for your application. If the administrators make a change to an offer on that server, you want that trickled down. I would think that there are a multitude of options you can choose from depending on your background, and what you'd like to learn going forward.

1. One customer I did some work with had such high volume that they replicated from the "master" server to another server (1 subscriber) and that "dummy" server then had the multitude of subscribers so that it was free to do the application work that was needed.
2. If you don't "immediately" need the data you can simply put an audit trail trigger on the master servers data table(s) for the offers. Nightly you could have a job that could output that day's changes to a datafile that is on a server, then push the changes out to the others servers so that they could then apply them.
3. You could setup a chain of triggers where the master updates 1 server/database, that server/database has a trigger that updates the next etc.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."
Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 05/09/2006 :  13:59:47  Show Profile  Visit mfemenel's Homepage  Reply with Quote
Except for the chain of triggers(my God what a maintenance nightmare), those are good starts. Thank you.

Mike
"oh, that monkey is going to pay"
Go to Top of Page

druer
Constraint Violating Yak Guru

USA
314 Posts

Posted - 05/09/2006 :  14:09:15  Show Profile  Reply with Quote
Maintenance nightmare? It will never change in the future will it? Aren't things STATIC? (Just kidding)

It was at the bottom of the list, and as prefaced, would depend on the background that you have. (The level of Brute Force required is always based on the knowledge that one has going into the project. Or in the case of home repairs the amount of money one is willing to spend to have the right tools.)

I just thought of a 4'th as well: Simply Bulk Copy out and Bulk Copy load the entire table each night and not worry about using an audit trail.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."
Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 05/09/2006 :  14:35:49  Show Profile  Visit mfemenel's Homepage  Reply with Quote
Can you imagine a trigger that updates 72 databases? Oh, it hurts to think about. You know, that bulk copy might be the simplest way. Just push it to each server and let a job pick it up for each db and make the update.

Mike
"oh, that monkey is going to pay"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000