Snapshot replication setup architecture 1

[web4fun]

Background:

Planning on setting up SQL 2000 snapshot replication for a 12GB db once a night as we need a complete replica of the entire production db on another instance for reporting purposes. The snapshot will occur once a night at non-peak hours.

Question(s):

Given the above requirements and due to heavy load on the potential publisher / source SQL server (4 CPUs, 4GB RAM), we have built another SQL 2000 server to server just as a Remote Distribution Server (2 CPUs, 2GB RAM) that also has a separate drive (separate from the SQL Data and Log files) to store the snapshot file(s). We have also built a SQL 2000 server to be the Subscriber server (2 CPUs, 2GB RAM). All servers are in reasonable proximity to each other and all of the Network guys claim that network connectivity should not be an issue. I also have installed / enabled FTP on all servers. Does anyone see a problem with this setup?

I understand that ordinarily the Distribution Server would be on the same server as the Publisher Server but unfortunately, past Admins had setup / tied so many applications and databases to this server that we don't want to risk anything...we are in the process of moving things off of the current source server. The Distribution Server needs to be setup/defined first, then the Subscriber, and then the Publisher correct?

Also, I understand that during the snapshot process, the db will be unavailable for access so if we perform this nightly, that would mean some downtime for the db each night?

In regards to cleanup, does the snapshot file that gets created get removed after a new one is created on the distribution server or does another one just get created?

Apologies for all of the questions but we have never setup any sort of SQL replication at our organization. Thanks.

 

[mrdenny]

Why the FTP setup? Are the machines all in the same location? If they are just use the file shares.

You need to setup the Distributor, then the publisher, then you will push it to the subscriber.

Correct each database table will be locked while the data is being exported.

What's the data change rate on the database? I'd recommend using either a full backup or a log shipping setup. Replication may not be what you are looking for. The main potential issue that I see with replication is that the data from each table will be out of sync as the tables are exported one at a time.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[web4fun]

mrdenny, thank you for the valuable information. Yes, I do not need FTP setup as I posted this before I actually began the process of setting everything up and so I saw that I could use file shares.

I've got the Distributor and publisher setup and will configure the subscriber next.

Thanks for the confirmation regarding the tables being locked, I believe that 'pault' had noted this to me before.

The data change rate for the most part is not significant, we have 5 or 6 tables that change somewhat frequently as this db stores the data for our Call Management system. We had thought about full backup / log shipping however we needed an automated way of making the replica db available for reporting users and I had thought that log shipping requires some manual intervention in that it's just like restoring the db to a particular date/time? If this is not so, then please correct me but I had posted the requirement of needed an automated way of replicating this db and having the replica available for Reporting without the need of doing anything manually....this is why 'pault' recommended Snapshot Replication as we only needed a nightly refresh.

The other question I have is since I want to replicate the entire db, do I need to create an empty db with the same name on the subsriber side or will the Replication process handle that?

On my publisher server, my db and log files are located in D:\MSSQL\MSSQL\Data, but my distributor and subscriber have the db and log files located in D:\Program Files\Microsoft SQL Server\MSSQL\Data so when the snapshot replication occurs, the replicated db will not be created in a different folder on the subscriber will it? i.e. in D:\MSSQL\MSSQL\Data vs. D:\Program Files\Microsoft SQL Server\MSSQL\Data....I just want to avoid having dbs scattered in two different locations. I know this question sounds strange but when using IDERA's db mover tool to move dbs from one server to another, if the db and log file paths are not the same on both the source and destination server, the process creates the copied db in a new location on the destination server that mirrors the source server which in my opinion is a 'bug'. Thanks in advance.

 

[web4fun]

mrdenny, okay...again the scope has changed and now we can only afford minimal downtime of the production db so, we are now going to have to go to either log shipping or backup / restore. Problem is all servers are Standard Edition and so I'm going to have to track down the resource kit in order to get the scripts that would provide the same functionality as the Log Shipping wizard does in Enterprise Edition of SQL Server 2000...hopefully the scripts will suffice for simple log shipping. Full backup and restore may be a problem as the db is 14GB in size and so the recovery would probably take too long for the reporting users. Agree?

 

[mrdenny]

Skip the Microsoft log shipping it's a pain. Use the log shipping code that I wrote and published faq962-5754.

Instead of having the first server start the restore job, have the restore job scheduled to run once a night.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[web4fun]

mrdenny, thanks a bunch...your version looks great! My needs are pretty simple compared to other folks as I just need a FULL copy of the production DB over onto the reporting server every night. I only need to restore from a full backup every morning so don't even need to restore transactional backups. I wonder if it would just be easier to setup an ftp job on the production server of a copy of the latest .BAK file over to the reporting server and schedule a full restore of the db? I've timed this process manually and it takes about an hour total (for the ftp and the restore). The problem I have is that we didn't define a backup device and so the backup files for the dbs are created / stamped with the dates appended to end; i.e. mydb060508.bak and I haven't been able to figure out a way to have ftp send the latest .BAK file each morning??? There has got to be some VB script out there that could accomplish this but I have yet to have found one via the GOOG. I know that I could create a new backup device and run perhaps another backup to that device which would create the backup file without the date stamp appended to the filename but I was hoping to avoid that as it would be wasting disk space. Any thoughts on this? In the meantime, thanks again for pointing me to your log shipping instructions.

 

[mrdenny]

Be careful with full backup and restore operations. A company I was at did this. It worked well when they set it up, back when the database was 3 Gigs. However when I got there the database was 200 Gigs and the process took 9 hours to complete. We replaced it with the log shipping process based on the code in the thread.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[web4fun]

mrdenny, yeah...I was thinking about the same potential problem as the db gets larger over time. Right now, it's roughly 13GB in size and the complete restore has taken 15 minutes so it's acceptable now....however that will not be the case when/if it reaches a couple hundred gigs...something definitely to consider. Thanks again.