October 22, 2009 at 7:27 am
I have the opportunity to re-do the architecture of our DB's along with some more suitable hardware. What we have is 3 years old and due for replacement, so we have chosen the hardware we feel suits based on lots of info from Quest Spotlight. What I now want to do is get the most out of it. It wasn't fully maintained before (it is now), but the team have done a good job with the knowledge they have (the schema is very good for example). The bit that I'm asking advice on is with the replication setup.
At the moment, We have a master server (for persisting data to). This replicates out to (currently) 6 subscribers. MDF and LDF but no NDF's. It does the distribution, so basically takes a hammering.
I'm putting in a mirrored solution (not clustered for various reasons). I'm splitting the replication distribution out to another server (and have a service broker box as well which takes little load - 8 core 20GB so will use for the witness and some monitoring tools like Spotlight's diagnostic server).
We have somewhere around 6000 concurrent users on the site (Master DB Server wise there are 1300 user sessions on average through the day)
So....the setup to be.....
Master(Publisher) 8 core Nehalem 3.0Ghz, 32 GB RAM DDR3 (initially) (both principal and mirror are the same and don't share disks or spindles)
-split between RAID 5 and RAID 10 (new EMC SAN being used)
-no disk contention on the SAN (the spindles are mine, all mine)
-file groups split between drives as appropriate for read/write primary usage
-separate file group for none clustered indexes
-separate file groups for the biggest tables and their indexes
-file groups set with 4 files per file group as a good compromise
-RAID 3 disk for backups (as advised by EMC) before being copied off site
-Windows Server 2008
-SQL Server Standard 2008
-Will eventually log ship to second data centre
Distributor 8 core Xeon 20 GB RAM (we had this spare 🙂 so I snapped it up).
-RAID 10 disk with no contention
-Windows Server 2008
-SQL Server 2008
-likely to hold Spotlight repository, but not decided yet
6 Seperate servers (likely VM's) for reads and searches.
-RAID 5 for the disks and no disk contention
-Windows Server 2003 (not 2008)
-SQL Server 2005 SP3 (not 2008)
-There might be a 7th as a hot standby just in case.
The SAN has been kept sacred for the DB's (for now) so we have plenty of space for sizing files etc...and nothing should share anything else. Network wise is very good (we are our own ISP)
Everything is 64 bit and brand new, so 'just' needs the schema and data migrating. A great opportunity to tweak things first. It's quite a simple setup in all fairness so should fly. I have 6 weeks to set this up so plenty of time.
I can set up the replication however I choose provided that data ends up on the subscribers as soon as possible (no lag). We currently see approx 100 transactions per second for each subscriber. The main DB is approx 200GB in total at the moment, but the company is growing at approx 70% per year (even in a recession) which has helped the budget for these as they need to last 3 years.
So....what is the best option for me with replication ? Push or pull ? Single distribution database or multiple distribution databases (1 per publication and therefore 1 per subscriber). What options can I throw at this for best performance and stability. I have seen various things on performance in 2008 replication, so what are the thoughts on this ?
It has to be easy to use and maintain as I want the other guys here to get used to it and feel comfortable with what we have. It has to be supportable and easy to bring back quickly if we need to. Re-doing the existing snapshot can take several hours (which took up last Saturday sadly).
Any thoughts would be very much appreicated. I realise there are a lot of 'it depends' answers, but anything interesting would be good so I can sift through and see what fits best.
Thanks
Ryan
p.s. No this is not an interview question or for college 🙂 Most of this is a walk in the park, I feel replication is one of my weaker points so it's always good for constructive criticism if I've done something daft.
October 22, 2009 at 7:42 am
If you have one publisher then multiple distribution databases won't do you any good because a publisher is associated with one distributor.
MySQL is great for one master, multiple slaves.:hehe:
We did have some issues with the load on distributors and pull subscriptions alleviated that though this shifts the load to the subscriber.
We used to set our replication jobs to run every five minutes but this created large spikes in traffic through the distributor so we have gone back to the default of continuous.
I can't remember which was around it is but one of the properties of the stored proc to create a publication means that a snapshot is un-necessary when new articles are added. You will have to do some reading but I think if you choose the option not to run the snapshot then you have to subscribe to all articles whereas if you want to subscribe to only a few articles you have to choose the option to run the snapshot.
It is worth looking at the pre/post snapshot script facility. In some cases you don't want to replicate all indexes and constraints in which case using the pre/post snapshot scripts can help with that.
October 22, 2009 at 7:45 am
Thanks Dave. Will re-visit the setup with that in mind.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply