February 9, 2011 at 7:27 am
Hi –
Environment: SQL 2008 R2 Enterprise, 900 GB RAM, 64 cores, 64 bit, win 2008
Goal - create 2 complete environments where data can be collected on each environment simultaneously.
I have a 1 TB, highly volatile OLTP database with 300+ tables. There are several tables with 2 billion+ rows and other tables with millions of rows. Generally speaking, I’m not concerned about the tables with 2+ billion rows, what really concerns me is the number of tables.
Unfortunately, ALL the tables will need to be replicated.
Additionally, there are two other databases (with several tables with billions of rows), and a dozen other smaller databases to be replicated.
Has anyone successfully leveraged P2P replication on databases with literally hundreds of tables w/out issue?
Any information you can provide is greatly appreciated.
February 9, 2011 at 7:52 am
Cheshire,
One of the issues that I recently got hit with was:
Setting up a central distributor, I had 64 stores with 1 publication being replicated to 1 subscriber. I also wanted to do replication from a couple of other databases to a few subscribers.
I was testing the first part, and in testing was using 15 stores without any issues. When I opened the flood gates, I started having issues with the snapshots, which was occuring hourly. They were failing.
Found, after pushing hard at MS that there is a setting that is created when you install SQL Server in the syssubsystems table in msdb that determines the number of connections that can run. In oour case it was very low. We had beefed up the box, but this setting is not automatically updated. I had to physicaly go in and make adjustments to this table after comparing the settings to another system built similiar in configuration when SQL Server was installed.
Run:
Select subsystem_id, subsystem, agent_exe,max_worker_threads
from msdb.dbo.syssubsystems
to see your settings.
Also, the reason that I was seeing the problem hourly was that the default for the snapshot job was to run every hour. When I turned this off (because I wanted to control the snapshot anyways) the rest of the problems went away.
You may see this as an issue, depending upon how you create your publications.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
February 16, 2011 at 4:19 am
If it HA solution for warm standy server would not advice P2P replication , you are better of with mirroring or Log shipping.
Cheers
Sat
Cheer Satish 🙂
February 17, 2011 at 8:02 am
It's not for HA. I've already configured SQL mirroring for 25+ databases on 10 GB NICS for HA.
I'm trying to scale out the application along the X axis - and I'm considering leveraging P2P replication.
I have two really powerful servers running with the db's being housed on local storage FusionIO cards. Ideally, I want to leverage both physical servers for application processing and maintain copies of the full databases on each physical server.
Any information provided is greatly appreciated.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply