SQL server Database Replication -Need advise

Question

Hello Experts ,

Greetings ! Need your advise in one of our enterprise proposals.

Our Retail/ Manufacturing customer has business scattered across different continents in the world . Having said that, for business requirements, Data should decentralize from Headquartered location and should go to other regions almost seamlessly . To meet this requirement, customer is using SQL server replication but we have few challenges in it and these challenges compel us to give many P2 and P1's in the support.

Here are few significant challenges :

1. Weak WAN network
2. SQL servers is on legacy versions (SQL 2008 and 2012 )
3. Apps team messing up subscribers with DML operations
4. Always replication need attention from seniors

To get rid of this problem, we want to propose customer with support and Environmental friendly technology here to replace existing SQL replication. So kindly give your recommendations to avoid this problem. Many thanks !

Regards,

Naren

Email:******@live.com

Answer 1

Hi Naren,

Based on the challenges you have raised, we understand that you are looking for alternatives to SQL Server replication to solve the problems of fragile WAN networks, outdated versions of SQL Server, and the complexity associated with managing replication. increase.

Change Data Capture (CDC):

• CDC is a feature available in SQL Server 2008 and later versions. Capture changes made to the database and store them in another table. You can then use this collected data to push changes to remote sites using your own custom mechanisms or third-party tools. CDC is an effective solution for replicating data without relying on traditional replication methods. Database Mirroring or AlwaysOn Availability Groups:

If you're using SQL Server Enterprise Edition, you can consider implementing database mirroring or AlwaysOn Availability Groups (AG). These features provide high availability and disaster recovery capabilities and can be configured to replicate data between different sites. However, you may need a stable network connection, so you should troubleshoot weak WAN network issues.

Log shipping:

• Log shipping is a method of maintaining a warm standby server by automatically backing up and shipping transaction logs from primary databases to secondary databases. It can be used as an alternative to replication to achieve data distribution. However, log shipping has some limitations compared to replication. B. Lack of real-time data replication and need for manual failover.

Third-party replication tools:

• There are several third-party replication tools available on the market that provide a more robust and flexible solution compared to built-in SQL Server replication. These tools often offer advanced features such as conflict resolution, performance optimization, and heterogeneous database support. Common options include Attunity Replicate, HVR Software, and Dbvisit Replicate.

When choosing an alternative solution, it is important to consider factors such as the customer's specific needs, deployment scope, budget, and the expertise available to the team to manage and support the chosen solution.

I hope these recommendations help you find viable alternatives to SQL Server replication.

Answer 2

While MuhFirst of all, this situation is always challenging. Transaction Replication is probably the best solution, but as you have noticed it comes with some problems. Particularly:

1. Apps team messing up subscribers with DML operations
2. Always replication need attention from seniors

No matter which solution you use, you will need some monitoring to make sure that the solution is still working.

In the first point, do you by chance mean DDL operations (i.e. CREATE/ALTER TABLE etc), rather than DML (INSERT, UPDATE, DELETE etc.)? Schema changes in a replicated environment certainly require you to be more careful than in a non-replicated environment. And if you have developers who find these challenges uninteresting and just go on and make schema changes without thinking, you certainly have a problem beyond the purely technical.

Muhammaed discussed a few alternatives. Let's first quickly dismiss Availability Groups. AGs are definitely nothing you want over an unstable WAN connection. AGs also requires monitoring and good understanding of the technology.

Log shipping has the advantage of being the most simple-minded of all solutions. This is not the least compelling in an organisation with low skill level. Developers cannot really mess up log shipping with wild schema changes, and the solution is less prone to break down. And particularly, issues with the log shipping will not affect the primary database. But the distinct disadvantage is that when the log is applied to the secondaries, users have to be kicked out.

CDC is not really replication in itself, although it builds on the same technology. But it could be used if you want to implement your own replication. Rolling your own replication is a major investment, but this can have the advantage that it can be tailored to the needs of the customer.