Effectively troubleshoot latency in SQL Server Transactional replication: Part 2
Are you struggling with latency issues in SQL Server Transactional replication? Our comprehensive guide provides clear, step-by-step instructions to effectively troubleshoot and resolve these challenges. Dive into proven techniques and best practices that will help you enhance your SQL Server's performance and ensure seamless data replication. Don't let latency slow you down—master the art of SQL Server troubleshooting today! I hope you find this teaser engaging! If you need any adjustments or additional content, feel free to let me know.
Transaction Replication - How does the Identity range work.
Hi All, I am setting up a transactional replication for MS SQL 2019. The team is using Identitiy columns as the PK on the tables. I know they are a issue when a failover happens. I would like to set my Identity Range for my Publisher to be from 1 to 2 lakhs and in case of failure the subscribr should start from 2 lakhs. Is this possible if yes can anyone help in providing a sample code to do the same.SQL Server Replication Management Pack SCOM
Windows Server 2019 SQL Server Standard 2019 CU 15 SCOM 2019 This server has four sql instances configured. Three of the sql instances has replication installed and configured. To monitor the OS, SQL and SQL Replication we have SCOM configured with the appropriate MP. The monitoring for SQL and SQL replication is configured for low level monitoring by following the guide in the Microsoft docs for the MP. For the Instance where we have the problem, I can under Replication\Local Publication see two configured BiDirectional replications, and under Local Subscriptions there is two Subscriptions. After the setup the replication monitoring I get two errors in scom, one is for monitoring and the other is for discovery. "Module: Microsoft.SQLServer.Replication.Windows.Module.Discovery.Discoveries.PublicationDiscovery Version: 7.0.28.0 Error(s) was(were) occurred: Message: An error occurred during discovery. ---------- Exception: ---------- Exception Type: System.Data.SqlClient.SqlException Message: Column names in each table must be unique. Column name 'allow_drop' in table '#TranPublTemp_PublicationDiscovery' is specified more than once." and "Module: Microsoft.SQLServer.Replication.Windows.Module.Monitoring.Monitors.MonitorDistributorSnapshotFreeSpace Version: 7.0.28.0" "Error(s): An error occurred while parallel querying SQL: One or more errors occurred. at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification) at Microsoft.SQLServer.Module.Helper.Sql.SqlParallelQueryHelper`1.<>c__DisplayClass7_0.<FillCollectionParallel>b__1(IGrouping`2 parallelQueryParameterGroup) mscorlib" When i collected the exceptions and got the statements it looks like it for each database that is part of a replication the MP is trying to do "ALTER TABLE #TranPublTemp_GetPublisherInfoQuery ADD allow_drop int;" But since that column is added for the first database, when it tries to add it for the second database it fails with the above error. Is this a bug in the monitoring MP? Can it be something in our configuration of the replication? I don't know if this is effecting the second alert I have or not, but I haven't found anything for that error in the logs.
Syncing local db with cloud db
I have a web application that runs on SQL 2012 locally in the office, We have a virtual server in the cloud hosting a copy of the web application so management connects to it and run report the next day after a backup of the local db is restored on the cloud server. How can i sync or replicate the local with the virtual server which will be near realtime.
Read this if you have transactional replication configured and plan to upgrade from SQL 2008/2008 R2 to SQL 2012/2014
First published on MSDN on Jul 16, 2014 SQL Server online documentation makes very clear that you need to 'drain' your replicated transactions before doing any upgrade if you have replicated databases.
