Imagine a bank with many ATMs and reams of customer transactions resulting from the ATM transactions. Now if there is only one database and if it fails, it is going to cast a pall of gloom over the bank. And that’s the reason why banks have a second, and a third and fourth if required, to support the primary database. Now every time a customer transaction happens, it gets replicated in the failover database. This rings true even in the case of alarm and polling stations wherein every time an alarm call and polling call happens, it gets replicated in the failover database. So much for data replication and the critical nature of it in today’s world.

And with organizations managing data with on-premise data center as well as cloud data centers, data replication has grew in significance. High availability (HA), Disaster Recovery (DR), load balancing applications or sharing data internally or even externally with clients are valid reasons that increase the significance of SQL server replication.

When talking about SQL server replication covering the scenario of data replication from on-premise to Azure Cloud environment, there are some salient features that need to be considered. Gaining an understanding of the SQL server backup and restore tools/utilities is pivotal as some the tools may not restore the database with replication objects due to various reasons.

What are the key features of SQL data replication from on-premise to Azure cloud?

In facilitating SQL data replication from on-premise to Azure cloud, what matters are the SQL server database backup and restore tools, growth of data, changes that ought to be handled, critical features like memory allocation and latency threshold limits, and most importantly, replication requisites that ought to be met.

Perquisites

  • For this scenario encompassing SQL server data replication from on-premise to Azure Cloud environment, the first step is about accessing the SQL server Database backup and restore tools/utilities to ensure Replication objects are restored properly
  • Firm up decision on keeping distribution agents on the production server or keeping them in the separate server to avoid replication load on the production server.

Accounts & Data

  • Creating separate account for Publication/Distribution/Subscriber databases
  • Making an assessment of data growth in the source or publication database is vital to understand Volume of records being replication on daily basis
  • Handling changes in structure of table schema very carefully involving replication as one change may override another change in the target or subscriber table schema

Critical Features

  • Memory Allocation to SQL server and operating system
  • Latency threshold limits and email notification when the latency threshold limit gets violated during data synchronisation
  • Alerts for failure and restarting of replication agents
  • Managing log space while running snapshot after the inclusion of a new filter to any existing tables or alterations to any data types in the publication database

Replication Requisites

  • Keeping Replication scripts up-to-date to ensure quick deployment of replication in case database (Publication) needs to be restored from backup
  • Adding new columns in the target table with identity property/calculation fields
  • Using custom queries to monitor replication
  • Establishing performance baseline and tuning replication based on the need
  • Logs are generally created when a new article is generated (i.e.) when you create a new table or when you include a new filter to any existing tables or alter any data types for any defined tables
  • Running the Snapshot and generating the articles, logs will be running continuously in the background and get placed at the specified path which will cause space issue – These space issues also depend on the number of times the snapshots get executed

Saksoft’s tryst with alarm calls and polling calls, polling server and SQL server data replications began when one of the leading clients sought our expertise to re-engineer their existing monolithic application that was leveraged for recording device heart beats and alarm messages.

With the alarm call and polling call getting replicated in the failover database, facilitated by SQL server data replication, the Infra team working on the platform acquired near real-time information (latency 4 seconds) pertaining to the Polling & Alarm calls delivered and informed the Technical team, which in turn swung into action to address the issue even before it was reported by the customer.