SQL Server Performance Forum – Threads Archive

Setting up SQL replication: Questions

Hi There, My team has a heavly used SQL cluster, and we run builds against it throughout the day, we are looking into replication to offload the builds to another server and not affect customers performance on the main cluster. I am wondering what type of replication to go with? I was going to do transactional so we could continually build on the subscriber throughout the day without affecting build schedules. But I am wondering if transactional rep is really the way to go? If someone was to add a new table or sp I would have to rebuild replication? I am looking for the least amount of maintenance, would Log shipping be the way to go? Thanks for any info!

To avoid the hassle I would go with Log shipping rather than replication, which is more granular method of providing availablility. The log shipping is easy way to setup and monitor in the same way and all the transactions are transaferred to secondary database without any performance issues, and you can use the secondary server as a read=only server intermittenly. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Unfortunately I found out today that we have to have 2 way communication. I have a question. What would be the best least maintnence form of replication for a system needing 2 way communication? Trans-Rep? I guess our builds update the database! Another problem I forsee is a maintnence nightmere, since we are constantly updating the software. We will be adding tables, schema, objects, stored proceders and views, so that means we would have to re-setup replication each time that happens? Thanks for any help!

Although replication doesn’t speed up data transfer, it does let you transfer data from one server to others all at once, then apply those changes to other databases. In short, replication can be the best choice for data transfer if you’re looking for a happy medium between data concurrency and availability. The most common problem for two-way replication is the potential for conflicting data. ANd not always the replication configuration is required if the database schema is changed, but the changes to schema is inevitable then choose other path than replication. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Ok I found out that I dont need 2 way communication. Right now I just need a SQL mirror for read only queries, but it has to be realtime or close to it. BUT I looked into LS and it seems to me that you can’t query while it is applying/restoring the Tlogs. I need to setup a mirror that is close to realtime, and I dont want to mess with replication, but the more I look into this the more it comes down to really implimenting replication. Problem is we are trying to keep maintenance down, we have beta software and everytime we update the SQL database we would have to rebuild replication , that would cause downtime. Maybe backup
estore is the only way to have a mirror without using replication? Thanks!

The only backdrop in log shipping is while restoring the transaction logs on secondary server, the database must be in exclusive mode. I think the restore will not take more than minute or depending upon the database activity. For smooth flow of restore you can refer to thishttp://www.sql-server-performance.com/q&a37.asp link in managing the connections. If you need the realtime availability the only solution is SQL cluster. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>