April 26, 2009 at 1:27 pm
Let's say I have a production database server.
I want some way to have some level of availability so that if the production server goes down I don't have to drag in another server, image it, and restore from my tape/network/whatever backups.
The other thing I would like to do is allow intensive queries to be run against the data in the database, but not bog down production.
So I'd like to run queries against the "backup" server.
Something like a 15 minute delay from when it goes into production to when it shows up in those queries/reports would be fine. 5 minutes or less would be better of course.
I'm thinking Replication is the way to go here?
Out of all the options: log shipping, mirroring, and clustering (which doesn't really give me data backup since they share the same data source) - it appears that replication is the ONLY one that lets me actively report on the "backup" server.
So I'd setup my production machine to push out to the replication node which is my "backup" server - subscriber, and then once that is setup I could also report against the backup/subscriber node.
Am I understanding this properly?
Another question: Say I want to setup a full blown data warehouse using SQL Server.
Perhaps this would be a separate 3rd server that holds all sorts of data.
Would it “make sense” to have the data warehouse server do its ETL against the subscriber server so that it could pull the data it wanted without slowing down or interfering with production?
Thanks!
April 27, 2009 at 8:39 am
That's the strategy we use.
What I really like about it is that you have data readily available at two places and you can fully utilize the resources of both (publisher and subscriber) servers.
Good Luck!
* Noel
May 5, 2009 at 1:46 pm
There are several gotcha's with replication. From what you're describing it sounds like snapshot replication is the simplest choice. However, with snapshot replication ALL of your data is copied every time. If you have very large databases then this may not be the best option. If you go with transactional or merge replication then you have to alter your tables to include a guid column (they're required). Also, once you mark a table as being transactional or merge replicated, you cannot alter the table's schema without undoing replication (although I believe this is not true for 2k5).
We've been using Red Gate's SQL Data Compare Pro to migrate from a 2k to 2k5 and it's been working like a charm. Very easy to use, fast, and it only moves the records you need instead of the entire table.
May 8, 2009 at 7:25 am
Transactional replication does NOT requires a GUID column. Merge does.
* Noel
May 8, 2009 at 7:39 am
Is there any reason to not use a GUID and or a timestamp in all your tables anyway?
I mean provided storage space isn't an issue, it seems like they are so useful in so many different ways, why NOT have them?
May 8, 2009 at 7:56 am
When the tables are large these type of columns increase overhead in "write" performance.
* Noel
May 8, 2009 at 8:07 am
Good point... I can see that killing performance during massive data loads as well...
Thanks.
May 8, 2009 at 10:12 am
Transactional replication does NOT requires a GUID column. Merge does.
@Noel: Transactional replication does require a GUID column if the subcriber will be making updates. In Maxer's case, we have to assume that the stand-by server (subscriber) will be making updates if the primary goes down.
@maxer: Here's a good article that compares/contrast the approaches that you mentioned:
http://www.replicationanswers.com/ReplicationLogShippingMirroring.asp
May 8, 2009 at 10:45 am
@Noel: Transactional replication does require a GUID column if the subcriber will be making updates.
Immediate updating subscribers are "by far" NOT the typical use of Transactional replication.
In Maxer's case, we have to assume that the stand-by server (subscriber) will be making updates if the primary goes down.
No we don't have to assume that. The title of the post is to offload "reporting" which implies NO updates!
In addition, if the primary goes down, depending on the types of updates you may not even be able to do it
if it was not set up using "queued" updates. Your triggers would roll back the transaction instead.
If the primary is not available anymore for a long period of time you may as well remove replication altogether
and set it back up when a new server comes along.
* Noel
May 8, 2009 at 2:57 pm
No we don't have to assume that. The title of the post is to offload "reporting" which implies NO updates!
Touché! You win. 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply