Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
the_toilet
Starting Member
5 Posts |
 Posted - 2011-03-19 : 06:41:01
|
| Hi forum.I just joined this morning after reading article after article about replication possibilities... i am hoping someone can jump on and help..I am installing a BMC remedy environment. Live environment with SQL server will be in one location, and DR will in a location 100 miles away at the end of a 100 MB LAN extension circuitI will be deploying a near real-time copy of the live database which will have reports/wall boards ran against it. so, the question parameters:SQL instance 1 - two node MSCS cluster running SQL Server 2008 Standard - this is running the live application databasesSQL instance 2 - one server running SQL Server 2008 Enterprise - this one runs the MS reporting environmentSQL instance 3 - one server running SQL Server 2008 Standard - this is a DR of the live database server, no automatic failover required, all manual in the event of DRSQL instances 1 and 2 are in the same locationSQL instance 3 is in the remote DR locationSQL Server Enterprise is required instance 2 because we require data driven subscriptions with Reporting Services.so, questions:1. What replication methods are available to me (whether it be legacy log shipping, or mirroring, or transaction based) to go between SQL Standard and SQL Enterprise that is supported by Microsoft? (I found an unsupported Mirroring method using t-sql commands)2. what mechanism would you use over the WAN link? it is a LAN extension so security is not an issue at all.i guess my parameters are 1. the reporting database is within minutes of real-time, maybe up to 5 minutes out of date2. the DR database should be as close to real time as possible.3. there will be a lot of transactions on the live databasesorry for the ling post, i just wanted to make sure i had everything listed...cheersdan |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-20 : 11:11:34
|
| You're going to want to use Transactional Replication.Instance 1 = publisherInstance 2 = subscriberInstance 3 = subscriberInstance 4 = distributorI don't think Mirroring will work for you because mirror db won't be available for reporting.I'd strongly suggest a dedicated distributor in the same location as the publisher -- particularly because instance 3 is in a remote location. If the link goes down, best to have the transactions clogging up the distributor rather than the publisher. The distributor doesn't need to be as robust in terms of hardware as the publisher. Depending on the amount of transactions, you can get away with far less RAM and CPU resources as for the publisher.As an example, I have a publisher with 64GB RAM and 8 dual-core CPUs with 4 subscribers that uses a dedicated distributor with 4GB RAM, 4 CPUs. The publisher is a very busy OLTP system. The distributor machine yawns at the load.You WILL want to test your bandwidth between locations.In your current topology, you can use SQL 2008 SE for the distributor, but you may want to use EE.Alternatively, you could replicate between instance 1 and 2, and use log shipping for instance 3. This shouldn't be an issue since you're manually failing over. But, logs are a lot bigger than transactions, and you'd need to be taking log backups every 5 minutes -- which is a lot to restore if you ever need to, but not too big of a deal. Also, with replication already in place, may be easiest to manage one more subscriber than implement log shipping as well.Here's an article you shlould review, as well as this one.One more thing...Replication will continue working fine when you failover your cluster. You'll get a connection error, but it will continue on its own when the passive node becomes active, with no loss of transactions. Subscribers will never *know* the difference.Couple other considerations:- You do know that every replicated table requires a primary key, right?- Don't perform write operations against the subscribers -- especially inserts and deletes.- The initial snapshot may take a while distributing over the WAN. Know how much data will be copied and your WAN speed before implementing.- Distribution profile can be set to continue on certain types of errors, but doing so can cause the subscribers to become out of synch with publisher.- it's a good idea to validate subscriptions nightly |
 |
|
|
the_toilet
Starting Member
5 Posts |
Posted - 2011-03-20 : 17:16:14
|
| first of all... thank you very much for replying to this post and also the other post, it is amazing that people like you are on these forums as you keep them alive. i will go away and review the links you put in-line now, but wanted to fire a couple of points back at you for your thoughts...The DR database will not be reported against (instance-3), the one in the remote location. In theory, it can be in standby mode until the event of DR. I just need to brush up on how it is manually brought online, but this approach would in theory mean the remote instance does not need licensed (though even as i write this, i can see one of the Admins wanting to move another DB onto this instance once it is brought up – hence fully licensed version of SQL SE)On you final consideration point, can the validation of the subscription be fully automated, and proactive alerts generated on discovered inconsistencies? (please tell me go to read the manual if you like as I am sure I can get all this from books ? )It looks like Transaction replication is the way to go, as all SQL instances will be online at all times. Reporting will run on Enterprise SQL, then the production and DR instances will run Standard SQL. I then will have the ability to fire up the DR application stack to test if it works from time to time, without writing to the DB itself.I can confirm I will never be writing to the DR database replica, it is just there for DR. I might run additional backups against DR just for additional fault tolerance, but the core backups will be done at the live siteThe question is, do I really need a distributor server. I can see your logic, but it is a fairly expensive nice-to-have if I do not really need it. Dare I suggest I use the reporting SQL instance to do it? or am I being a cheap-skate? The DB itself will be fairly busy, but not massively so, and the SAN it is being hosted on is pretty quick (EMC with solid SSDs for flash) and tons of capacity, so I would be happy with a few days of backup on the replications, and worst case I could just break the replica if the link was down for a long time, and take regular dumps of the DB for transit in the event we loose the link, and then (god forbid) we loose the main site some time after, before the link is brought back online…In terms of the bandwidth, I am very confident that it will handle the traffic… I am expecting a throughput measured in KB, not MB.. (once initial snapshot is complete) |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-20 : 21:43:02
|
| With apologies to the mods, I'm going to answer in sequential posts.Validating replication:I run a SQL Agent Job on every server that has published databases.Essentially, it checks every publication with sp_publication_validationAt the distributor, I query msdb.dbo.sysreplicationalerts looking specifically for error code 20574 and have it page the DBA group, or on call DBA, if anything is found.Additionally, I have a job that runs every few minutes that queries distribution..msrepl_errors and again, pages us if it finds a recent issue.EDIT: I'll post the code for the jobs if you need, but hopefully this has you pointed in the right direction |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-20 : 21:51:24
|
| Sounds like you can get away without using a dedicated distributor. I will use the publisher as the distributor in any case without a dedicated distributor.The reason is that if the subscriber(s) become unavailable, at least replication won't fail before they come back online. Of course if the publisher fails, you have bigger issues than replication continuing -- but replication will continue when it comes back online, unless the subscriptions expire (this is in your distributor settings).I almost guessed that, as you're running SE for the publisher, but that doesn't mean that it isn't a highly busy transactional system.You can certainly allow the publisher server to be the distributor, and once (if ever) you outgrow it at some time in the future, you can stand up a dedicated distributor. Of course that would mean rebuilding all of the publications AND re-snapshotting.As for the bandwidth -- don't guess. It's easy to measure.Loop through all of the tables you intend to publish executing sp_spaceused and then you'll know the size of the snapshot.Measuring the transactional impact is a little trickier, but not too bad. If your WAN is really 100MB then I suspect you'll be ok. But make sure it really is that fast. Create files of known size, copy 'em over and time it.Hope this helps. And by all means, post back with further questions.And thanks for the kind words.rb |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-20 : 21:58:36
|
| One more thought, if you're new to transactional replication...If you want the non-clustered indexes copied to the subscribers at snapshot time, you must explicitly enable this. By default copying non-clustered indexes is turned off.I've seen many a performance problem easily solved by this.Replication is one of the VERY few things I use the GUI for in most cases. There are a lot of options (see the table articles tab in the GUI). Make sure you understand them. If you don't, post back here, we'll help ya out. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-20 : 22:24:49
|
Just thought of 2 more things I want to share:(1) You're right. If you use log shipping or mirroring for your DR, you don't need to license it. Probably true with replication too, but technically not true if anyone ever accesses it. Check with your reseller on this.(2) If you ever fail over to DR, you'll need a plan to get data back to the primary site once it comes back up. NOT a small task.<end sequential posts>  |
|
|
|
andyc2000
Starting Member
1 Post |
Posted - 2011-03-24 : 08:16:21
|
| HiI found this forum and this topic almost answers a question I have:As a follow up - I need to get rough plan to get everything up and running in such a DR scenario.We will have 2 subscribers and a publisher. So what happens when the publisher fails completely and can't be recovered?Do I remove the subscriber roles from both subscribers, then make one of those a publisher make the other a new subscriber.Then rebuild the original publisher and make this a subscriber to replicate the data.Can I then remove all roles and promote the original back to publisher and make the other 2 subscribers?Forgive me if this is completely off the mark, my DBA has vanished for the weekend.ThanksAndy |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-24 : 09:49:59
|
| Depends. It's not as simple as saying "ok, make this one the publisher now." If you start performing writes against one of the subscribers, you're going to have a task synching everyting up again.How you handle it also will depend on your SLAs and how long it would take to rebuild the publisher.You need to determine what the business requirements are for uptime, acceptable failover time, DR etc. While replication can and sometimes is used as a DR or HA startegey, it is more commonly used as a scale-out strategy. There are other tools available -- mirroring, log shipping, failover clustering. Any combination of these may be used.Also, I do think you need to discuss this with your DBA. This isn't the type of thing that is typically hammerred out in a couple days anyway. |
|
|
|
|
|
|
|
|
new environment - replication limitations