Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

i am in the process of setting upi a SQl Server 2008 64 bit Failover cluster. A lot of the database queries that we are running during the days are db intensive and lock a lot of the tables while executing.

I was wondering if there is a "best way" to setup a replication server so we can run the queries on it without locking the main db server

share|improve this question

2 Answers 2

If you want to run read-only queries on the replication server - Have you thought about using database mirroring to a reporting server, and then using database snapshots to create a read-only copy?

You'll need SQL Server Enterprise edition for asynchronous replication, and the other SQL Server will needed to be licensed as you're using it. You'd also need to test carefully, and be prepared for some latency.

I assume that you've performance tuned the database intensive queries?

share|improve this answer
    
yes, i have done the performance tuning. We are running Sql Server Enterprise on the cluster and i would use that too on the Replication server. –  Brirby Jan 10 '12 at 20:39

1) "A lot of the database queries that we are running during the days are db intensive and lock a lot of the tables while executing."

Have you thought of trying the snapshot isolation level? It is useful when: "Reader/writer blocking occurs to the point that concurrency benefits outweigh increased overhead of creating and managing row versions. An application requires absolute accuracy for long-running aggregations or queries where data values must be consistent to the point in time that a query starts."

Have you tried to use database snapshots? They are: "a read-only, static view of a database (the source database)...Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation. Snapshots can be used for reporting purposes."

You could move the blocking queries there and get rid of some of the problems (release blocking, but this will increase space usage, so need to put in balance). But this means you'll need to change some part of the application that accesses the db to redirect the big calls to the snapshot. I guess that read committed snapshot enabled at db level will be easiest to verify.

If none of the options suit your needs, then I guess you could try using a replication server, but I'd try those before, as they are a bit less PITA then a replication :-).

2) "I was wondering if there is a "best way" to setup a replication server so we can run the queries on it without locking the main db server".

I'm not sure if there is a best way to setup a replication server, as it's a usual database server. Maybe you should ensure it's closest to the production server (probably better in the same switch, the bandwidth as big as possible, latency as small as possible). In case that the replication is very busy, then another option would be to configure the distributor (the replication witness) on a distinct server.

Anyway, even if you read data from the replication server instead of the main server, this doesn't mean you'll get rid of blocking completely, as the blocking queries could block the replication db, which will cause delays in replication delivery, you'll only move the problem a bit further.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.