February 11, 2013 at 3:05 pm
Hi, I have a 4.7 TB Database and I need to replicate a good deal of that to another server. I am just having no luck with that. I would try not using a Snapshot but I know I will miss data if I do that. Has anyone here ever had an experience replicating a large DB of this size.
I don't always test my SQL scripts, but when I do, I test in Production.
February 11, 2013 at 8:19 pm
Have you tried initializing it from a full backup? Naturally you would have to change the setting in your publications for "Allow initialization from backup files" to True...and you would get all of the non-clustered indexes and file groups contained in the backup (but those can be removed after the fact)
Depending on your backup compression software (which I'm certain you must have for such a large DB) this may be a safe way to go.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 12, 2013 at 12:05 am
Actually the initial size is only affecting your initialization time for the replication to take place, what really count is the changes that happened in the database.
The strategy will be depend on what are the result you are expecting and you should share more information so we would understand your situation better.
Like the answer for these questions:
- Is the replication still on the same location or different, if different how long is the distance.
- What is the bandwidth available between this site and what is the latency
- What is the purpose of the replication, is it DR or HA or for Reporting or other.
- If for DR what is the expected RPO and RTO?
- If for Reporting, is it need to be real time or only timely bases?
February 12, 2013 at 2:18 am
KTD (2/11/2013)... replicating a large DB of this size.
Not of this size. 15 times smaller... but this looks like irrelevant. The using of snapshot was out of question from the very beginning. Snapshot generation and transfer would take too much time and network bandwidth.
So I can confirm that the answers of MyDoggieJessie and bobie are relevant and useful.
I initialized from backup - and everything went fine. Here[/url] is the article how to do that.
Just do not forget to disable Distribution clean up job before initializing - and be prepared for several hours of initialization (it took 20 minutes for me, but my database is 15 times smaller). Distribution clean up agent MUST be disabled before subscription creating - or it will clean the subscription before it is created.
February 12, 2013 at 7:03 am
I would like to thank everyone for their replies. The purpose is for reporting and on many tables I tried to filter by date to limit size. Of course not all tables have a date and I don't believe I can do joins to filter. ?? I have never tried. The data could be behind by a few hours. The connection is 1GB between servers.
I need to take a look at the option of doing the snapshot from backup. I'm not sure I quite understand how to grantee no data-loss. I will read the linked article. Mirroring with Snapshot is not an option the DB is already mirrored to our DR site. The DR site is not an option for reporting. I really would hate to use log-shipping if possible.
There are numerous tables I do not need as well I don't really want the entire DB on the reporting server. This is a pretty busy database but at least 70% of the traffic is reads. Thus the purpose for doing this. Have built this server with a SSD-IO card for caching and hoping to get some good response from it.
Again thanks for any info, advice, and help.
I don't always test my SQL scripts, but when I do, I test in Production.
February 12, 2013 at 9:20 pm
I haven't really try this either, just an idea
1. Backup Strategy
If you use full recovery model, maybe you can seperate the tables you want to backup into a new filegroup and Backup base on the FileGroup accordingly.
2. CDC
If you use SQL2008 above, maybe you can use CDC to achieve your goal but really need to consider the impact on the source databases.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply