This post has been republished via RSS; it originally appeared at: MSDN Blogs.
Hello Team,
Yesterday, I worked in a service request with a lot of lessons learned when our customer was using Azure SQL Data Sync to synchronize around 15 million of rows.
In every process that they tried to synchronize the data they got the following error message: Sync failed with the exception 'Cannot enumerate changes at the RelationalSyncProvider for table 'dbo.customertable'. Check the inner exception for any store-specific errors. Inner exception: SqlException Error Code: -2146232060 - SqlError Number:-2, Message: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Inner exception: The wait operation timed out For more information, provide tracing ID ‘c4a39cc2-xxxxx’ to customer support.'
This error message report that DataSync process is not able to complete the process to synchronize the data and following I would like to share with you what was our troubleshooting process and the solution:
Troubleshooting:
- Using Azure Portal we didn't find a high resource consumption in terms of CPU, DataIO or Log IO. I would like to recommend using the dmv sys.dm_resource_stats with 15 seconds of delay for consumption resources.
- In this situation, we need to review what is the process or the query that is taking this time, for this reason, we enabled SQL Auditing in order to review what is the query that is taking this time. Also, using sys.dm_exec_requests we found a query all the time in suspending state. We found that a user table dbo.customertable is joining the data with the table that contains the modifications captured by DataSync, for example, customertable_dss_tracking under the schema datasync.
- In this situation, I asked to our customer if they have any maintenance plan for rebuilding the indexes and updating the statistics and they told they don't have.
Solution:
- As suggestion, we rebuilt all the indexes for this user table called customertable, and after running again the synchronization process it was completed in few seconds. if you need one, review this URL.
Other Lessons Learned:
- During the troubleshooting process, our customer has configured as a member of this Data Sync group a SQL Server OnPremise environment and we noticed that this process took too much time. In this situation, we found two important things that I would like to share with you:
- In every synchronization using the DataSync service that is running as a service in OnPremise, this program needs to download the data saving it in a temporal file in windows temp folder, it is very important that:
- Have enough free space depending on the data to be synchronized.
- The capacity of I/O is key in terms of performance. You could use Performance Monitor to review the transfer speed and MB/s of the IO. Please, use SSD if could be possible.
- Also, the capacity of network bandwidth to download the file. You could use Performance Monitor to review the transfer speed
- In SQL Server OnPremise:
- Maintain a correct maintenance plan for the tables. If you change the data with some frequency the data it is very important having a maintenance plan (updating statistics and rebuilding indexes).
- If could be possible, install the service in the same server that the SQL Server is or if you have an isolate server with DataSync Agent installed, please, try that the connection between these servers will be very closed to.
- In every synchronization using the DataSync service that is running as a service in OnPremise, this program needs to download the data saving it in a temporal file in windows temp folder, it is very important that:
Enjoy!