August 26, 2009 at 12:51 am
I would like to know how big database is supported by SQL Server 2005 replication. Is there any restriction?
I have a table with 980,054,120 rows into it. When configuring replication the initial snapshot agent job hang my SQL Server when it reaches to 5,700,000.
When starting the snapshot job, it starts with following message and when reaches at the figure i mentioned it stops responding, untill i reset my server.
Bulk copied 100000 rows from
Server Hardware specifications are:
Intel Core 2 Quad Core Q6600 2.4 GHz with 6 GB of RAM.
SQL Server is using 4 GB configured to use.
Windows Server 2003 Ent. Ed.
SQL Server 2005 Ent. Ed.
Database reside on D drive and the free space 238 GB
Distribution on C drive and free space 32 GB
Is there any issue in configuration?
I tried the same database on another server and it were also hanging machine.
I have setup replications several times on other databases on different server but they have no such size of rows as compared to this one and working perfect. Please help.
Shamshad Ali.
August 26, 2009 at 8:05 am
There is no set limit for replication but if the table is too big, I would not go for snapshot. I would use a backup method to do it. It will take too much time, Snapshot agent might timeout.
just my 2 cents
-Roy
August 26, 2009 at 8:35 am
there is generally no limit to the snapshot replication per say .... but you could be limited by the cache size if the bcp file becomes large ......I have not seen this with any of the replication agents, but on a file server ..... below article might give you a pointer to your situation
http://support.microsoft.com/default.aspx?scid=kb;EN-US;920739
having said that ... if I know the table is big, then in some circumstances I have used the following approach....
1. set up the publication definition, but do not create the subscription yet.
2. lock down the published data by putting it into single user mode or maybe shutdown the application - your call.
3. BCP out the table to a file on disk manually by running BCP from command prompt (more info from bcp /?) ensure that you use -n for native character set.
4. move this file from OS to Subscriber Server.
5. BCP in the file on subscriber database. Ensure that you dont append data to the same table .... you could truncate this table and bcp in the data ... or bcp into an antirely new table after you have created the skeleton of that table ... your choice
6. create subscription on Publisher with NOSYNC option - data already present on subscriber
7. put published database back to multi user.
If have seen this manual method of BCP for a single table having worked at least 10 times faster than regular snapshot. and it never fails. As always test it out on your dev server before prod.
if you are not ready to do this .... then you could generate the snapshot with multiple threads .... say 10 .... you could do this by modifying the profile of the snapshot agent
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply