CODEWITHSUNDEEP
sqlsql-serversql-server-2012database-replicationtransactional-replication
nirish83
nirish83

Reputation: 1

SQL Server 2012 Database Transaction Replication performance issue

We have configured SQL Server 2012 database transaction replication for our client's .NET web application to distribute SQL transaction and reporting on different SQL Servers.

We had implemented transaction replication on to SQL-Node1 is working as Master DB Server, We'd configured replication of Master DB on SQL-Node2 to pull out reports in to our web application which having lots of transactions and data uploading from excel sheet entries around 10 million entries each day.

After configured replication on two SQL Server 2012 instances, after few weeks we facing some performance issues and found some resource get locked during uploading files on to database that's why application unable to access those tables and data. Also found that server performing too much slow during day time when users access our web application.

Now we are looking to distribute loads on different 3 Nodes of SQL Server 2012. Where web application will access and transact data on SQL-Node1, Reporting queries get pull data from SQL-Node2 and SQL-Node3 will be get used to upload excel sheet data on to Database which will get replicated on all other SQL Nodes.

Current setup, all servers having Windows Server 2008 Standard and SQL Server 2012 Enterprise Edition.

Database size approx : 15 GB / Replication used : Transaction / Distributor role configured on SQL Node1 / Subscriber role configured on SQL Node 2.

We are looking for solution to resolve above issues which can distribute different loads (reporting, data uploading, transaction) and replicate data between all SQL Nodes.

Which feature will do perform well for above scenario among SQL Server 2012 HA, SQL Server Replication or SQL Server Mirroring ??

Quick response will be highly appreciated....

Upvotes: 0

Views: 1010

Answers (2)

SQLGuyChuck
SQLGuyChuck

Reputation: 322

All of the technologies would bog down on a large data import that is done in one big transaction. I suggest doing it as an ETL like function. Import into a staging table and migrate the data into the production table in bite sized chunks (test many data row sizes to find the size that works the best for your environment). 2 servers should be fine with replication on a cluster for HA with work loads you are talking about.

Upvotes: 0

Ben Thul
Ben Thul

Reputation: 32737

Because you have changes happening at more than one node (transactional data at node 1, excel uploads at node 3), "none of the above". All of the abovementioned technologies are built on having data changes happen in one location and propagating to others. You could look at peer to peer replication, but it seems like overkill.

If it were me, I'd try to diagnose why your file upload process is killing performance and fix/work around that. Once you do that, I'd move that process back to node 1 and implement an availability group to cover your reporting needs (with the added bonus of HA).

Upvotes: 1

Related Questions