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 |
|
Newbie
Starting Member
3 Posts |
 Posted - 2004-10-01 : 11:15:17
|
| Hi,I am very new to the database world, would really appeciate your help. At work I have two servers- one of the servers is the production server, and the plan is to refresh the second server with the content of the production server(such as the two are the mirror images in all aspects), once a month. The second server is used as a test server, that means after the update is made, we start doing our tests on that server and in no time the content is out of sync again. This is the reason that it will be refreshed every month. My problem is that this task has to be automated, so I thought SnapShot replication could be the answer, but I am not sure that this is the best solution. Production server doesn't have just one database, it has about 15-20 databases and every database has about 25-30 tables, and 25-30 stored procedures. Every table in turn could have an average of 7000 records. Is setting the server up for Snapshot replication the right solution, or is there a better way? If it is, Would I have to set a publisher for every single database?Appreciate your help. |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-01 : 11:46:07
|
| yes, IMHO snapshot is the best solution, no need to worry about data modifications and other objects.yes you need to setup publications per database especially concerning all tables in the database...you may also have another option, backup the databases and restore them on the other server automatically. |
 |
|
|
Newbie
Starting Member
3 Posts |
Posted - 2004-10-01 : 12:07:21
|
| Thanks Jen. Can I actually automate the restore of the database from a backup? Which one do you think would be a better solution, Setting the server for snapshot replication automation or backup/restore automation?I was under the impression that I will be able to publish all of the databases in one publication. Now that I know every database would have its own publication, I am dreading all the maintenance involved. If the restore could be automated, then this seems to be a better option. If this is possible, can the restore automation be set for all the databases (the same time) or would I have to go through individual databases in this case as well?Thanks a bunch for your help. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-01 : 12:17:20
|
| tara has some blog for this http://weblogs.sqlteam.com/tarad/if you want ready made scripts for you consider tara's script else you can create your own.the logic is to:1. backup the database to a network shared folder on the other server2. restore the database, benchmark how much time you need to complete the backup or you can send message to the other server once backup is finished, then restoreafter restore run a script to resolve orphaned users (new users that needs to be remapped otherwise no need after first setup). |
|
|
|
Newbie
Starting Member
3 Posts |
Posted - 2004-10-01 : 13:48:38
|
| Wonderful!Just another question, if I do go with IMHO snapshot, how do I synchronize between all the publications? I mean there are going to be almost 15 publications(1 for each database)/subscribers. I wouldn't want to start all of them at the same time (or that wouldn't even matter), my concern is that if all the publications start the replication process at the same time, it might hog the system and its resources. If this is a valid concern, then is there a way to time the publications so that after one is done it may notify the others so that other starts after that. Ofcourse I don't want to schedule all the publications for replication on the same day.Thanks in advance! |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-02 : 10:53:03
|
yap, you're concern is valid. create the publication one at a time, then spread the schedule, you can even script this so that creation will be automated, checking if there is still "creation" being processed before running the next script. you can check the sysprocess for the agents if they're running or not.for the snapshot schedule, this depends on your machine if it can handle the load. currently i have 4 publications with 2 or 3 subscribers on one machine and so far no problem. i only had a problem when i assumed, creating the publication and subscription at the same time is ok.  btw, what do you mean by how do you synchronize? if this is snapshot, you don't need to worry about this, just create an sproc that will automatically start the agent when it fails to start. |
|
|
|
|
|
|
|
|
Not sure if Snapshot replication is the answer