Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mfemenel
Professor Frink
1421 Posts |
 Posted - 2006-05-09 : 11:55:52
|
| 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
314 Posts |
Posted - 2006-05-09 : 13:08:16
|
| 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,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2006-05-09 : 13:17:48
|
| 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" |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-09 : 13:42:26
|
| 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,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2006-05-09 : 13:59:47
|
| 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" |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-09 : 14:09:15
|
| 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,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2006-05-09 : 14:35:49
|
| 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" |
|
|
|
|
|
|
|
|
72 Training Environments