FAQ

FAQs – SQL.REPLICA FOR SQL SERVER HADR ON AWS AND AZURE

Q 1: We currently operate RDS SQL Server Multi-AZ (2x SQL servers – Active and Standby, where standby is not a readable-replica). Can we drop Multi-AZ (the Standby SQL server) and subscribe to your CloudBasic SQL.Replica? At least, we'll be able to utilize the Read-Replica for our users to run reports. Can we also implement cross-region DR?

Yes, upgrading from AWS RDS Multi-AZ to SQL.Replica is a common use case. You will be able to create readable-replicas not only zone-to-zone, but also cross-region for DR (both can be achieved on same CloudBasic instance). Plus all versions of SQL Server, including Web Edition are supported. Mixing of SQL Server version (i.e. creating of Web Edition read-replica from Enterprise or Standard edition; or replicating from 2012 to 2017 version) is supported.

You can launch a free trial from the AWS Marketplace: https://aws.amazon.com/marketplace/pp/B00OU0PE5M/

Unlike with AWS RDS Multi-AZ for MS SQL Server the replicas are actually accessible and can be used for reporting. Moreover the replicas can be located in another region, or even in another cloud (i.e. Azure, Google Cloud) or On-Premise.

Unlike both RDS and MS SQL Server native AlwaysOn Availability Groups, replication of even MS SQL Server Web Edition is supported. Using MS SQL Server Web Edition instead of MS SQL Server Standard and Enterprise results to substantial savings. For more information, and a sample scenario, refer to the AWS RDS Multi-AZ (MS SQL Server) vs. CloudBasic SQL.Replica price comparison table: https://cloudbasic.net/aws/rds/alwayson/aws-multi-az-vs-cloudbasic-multi-az-ar/

And unlike MS SQL Server’s native AlwaysOn Availability Groups, there is no requirement for Active Directory or Witness servers. And yet, replication of all RDS and MS SQL Server versions, including Web Edition, is supported.

Yes, this product supports RDS SQL Server Web Edition. It supports all RDS EC2 and MS SQL Server versions, and allows replication between any RDS type and EC2 SQL Server version, from on-premise MS SQL Server to AWS RDS and EC2 SQL Server. It can be used for any type of Geo-replication scenario. Since this product replicates asynchronously, it allows the replica RDS or EC2 SQL Server to be hosted on a smaller and less expensive instance EC2 or RDS SQL Server.

Yes, AWS RDS to Azure SQL is supported. The initial replication might be a little challenging depending on the database size. However the continuous replication is supposed to work smoothly for most use case and data change volumes. You can see a benchmark comparing AWS RDS Cross-region replication against AWS RDS to Azure SQL benchmark here: https://cloudbasic.net/aws/rds/alwayson/benchmark/

Yes, using CloudBasic SQL.Replica instead of RDS Multi-AZ is a common use case. It is used in various scenarios, including when the system architecture requires reporting to not run against the primary RDS, for data offloading. Also, since CloudBasic SQL.Replica Multi-AZ capabilities go beyond the boundaries of the AWS region (Multi-AR), it can be used for data locality. It delivers good performance even in cross-region scenarios (AWS Cross-Region & AWS-to-Azure/GoogleCloud): https://cloudbasic.net/aws/rds/alwayson/benchmark/

Sample configuration:

RDS Source: r3.XLarge, MS SQL Server EE w/ Encryption and Multi-Zone Mirroring RDS Destination: m3.Large, MS SQL Server 2012 Web Edition RDS Source Location: US-East-1 (Virginia) RDS Destination Location: US-West-1 (California)

Yes, this is a common use case. The replication is handled asynchronously; replication over long distance internet connections to remote region is possible.

Getting started with CloudBasic SQL.Replica is quick and simple. Most data replication tasks can be set up in less than 10 minutes. There is no requirement for Active Directory or Witness servers.

Once you sign up for the service on https://cloudbasic.net/aws/rds/alwayson/, you will receive an AMI ID and an activation key over email. Locate the AMI ID in the list of Community AMIs and launch as an EC2 server within or outside of your VPC. Once the instance is running, and port 80 opened in the security group, connect to it by pointing a browser to the public DNS root URL (or IP) at default www port 80. Activate the server with the provided activation key. Login with user: admin, initial temporary password: {EC2 Instance ID}. Upon successful login, you will land on the wizard page, which will allow you to initiate RDS/SQL Server replication in various scenarios, in just a few clicks

All RDS and MS SQL Server versions, including Web Edition (2008 and above) are supported. Replication between any two MS SQL Servers versions is supported. Replication from On-Premise to AWS RDS or EC2 SQL Server, from i.e. 2008 to 20016, from Standard/Enterprise to Web Edition, from AWS RDS or EC2 SQL Server to On-premise, from AWS RDS to SQL Azure, MS SQL Server on Google Cloud, Oracle Public Cloud or IBM Cloud are all supported.

CloudBasic SQL.Replica is very easy to use. It is a lightweight high-performance solution that has been designed for AWS. It has been tested by some of the largest corporations in America. It took CloudBasic’s senior engineers years to develop it and make it capable of delivering superior performance. Check out the benchmarks: https://cloudbasic.net/aws/rds/alwayson/benchmark

Yes. CloudBasic SQL.Replica has a variety of metrics displayed in Replication Runtime Console. It provides an end-to-end view of the data replication process, including diagnostic for each point in the replication pipeline.

CloudBasic SQL.Replica provides a provisioning API that allows creating a replication task directly from your development environment, or scripting their creation at scheduled times during the day. The service API and CLI allows developers and database administrators to automate the creation, restart, management and termination of replication tasks.

Yes, CloudBasic SQL.Replica can read and write from and to encrypted RDS databases. It will be able to extract decrypted data from such sources and replicate it to the target. The same applies to storage-level encryption. As long as CloudBasic SQL.Replica has the correct credentials to the database source, it will be able to connect to the source and propagate data (in decrypted form) to the target. We recommend using encryption-at-rest on the target and protect data in transit using SSL to maintain the confidentiality of your information. If you use application-level encryption, the data will be transmitted through replication service, in encrypted format, and then inserted into the target database.

Yes, CloudBasic SQL.Replica was designed to replicate over TCP/IP and replication across AWS accounts is supported. All it needs is the default port 1433 opened in the firewalls on both sides. VPN channel between AWS accounts' VPC networks is recommended but is not mandatory. Replications can be configured with data in-transit encryption, leveraging SQL Server SSL encryption.

Yes, CloudBasic SQL.Replica can support your replication scenario. You have to launch the CloudBasic server at the region/zone where the destination RDS is located. This is the recommended deployment setup for most cases.

Yes. You can run a continuous replication of your database (from On-Premise to AWS, between AWS regions; or between i.e. AWS and Azure or AWS and Google Cloud) just if you were to configure the destination databases to be your read-replicas for DR or reporting. You can cut-over by terminating the continuous copy process whenever ready. Once time migration without setting up of change tracking is also an option.

Yes, database schema changes are replicated to the replica databases. New tables, stored procedures, constrains, FKs etc; new columns, altered columns changes are all replicated. There are only 2 exception cases (ambiguous nature) in which changes need to be synced manually: renamed column & dropped column. Alternatively for those 2 cases, the respective table at the replica can be dropped which will trigger the table to be recreated (along with all related objects) and re-seeded.

Also note that by design, dropped tables are not removed in the replica database. The DBA is responsible to drop the table on the replica database Reason: 1 - in DR scenarios, if a table is dropped by mistake, the replica MS SQL server can be still promoted to primary. 2- in Reporting scenarios, this will prevent immediate failures in reporting applications which are not updated yet.

The only main requirement is all tables tracked for changes to have Primary Keys. Learn more about the prerequisites at https://cloudbasic.net/documentation/prerequisites/.

It is a low intrusion solution. As an example, we have cases where customers create up to 6-8 read-replicas in same or different regions from same primary RDS (https://cloudbasic.net/case-studies/affinity-protection/, https://cloudbasic.net/case-studies/sports/). Creation of each replica is a separate process against the primary MS SQL Server.

For replication processes involving databases with a large number of tables and heavy volume of changes, there are advanced configuration settings, allowing setting up a limit on how many tables should be handled in parallel, to further minimize the impact on source.

Depending on whether your replica is located in the same region or another region:

If your replica MS SQL Server is located in same region, you would point your applications to the EC2 server instance. You can also configure your applications to access your primary RDS via Route 53 (an AWS service) DNS CNAME record. In case of a fail-over you would change the value of the DNS CNAME record.

If your replica MS SQL Server is located in another region, you would simply boot up your fail-over application/web servers located in same region (assuming you would keep those server shut down at all time, to not incur billing, and boot them up in case of a fail-over). The standby/fail-over application/web servers would already be pointed to the replica EC2 SQL Server. And of course your main domain/subdomain DNS records should be pointed to the new primary region application/web servers.

The replication lag varies depending on certain factors, such as: if replication is within same region or cross-region, advanced configuration (i.e. reducing default limits on parallel table replications, with the purpose to further reduce impact on source, resulting to slight increase in replication latency ), number of tables, volume of changes per minute. In most cases it is < 30 seconds.

Yes. Follow these steps to Extend Default Data Storage

Once the previous primary MS SQL Server is back up (or a new one is spun up in same or another zone or region, in the case where the entire zone or region is affected), a reverse replication form the newly promoted to primary MS SQL Server to the previous primary needs to be initiated (previous primary data is now outdated as data loss has occurred during the outage), followed by pointing your applications to the new primary.

You need to go to Configuration, enter SMTP parameters, and the DevOps email address the notifications need to be sent to, in the "email errors to:" fields.

CloudBasic supports resuming of replication in case of any prolonged downtime at the source or replica ends. Default retention period varies by product version. it is 8 days for the product version that comes with pre-installed replica MS SQL Server. It is 2 days in the product version that replicates to an external MS SQL Server. Default retention period can be adjusted during initial replication setup in the Quick Setup -> Advanced Settings.

CloudBasic features REST API designed for seamless integration with DevOps tools such as Jenkins and Go. HTTP Post calls are supported, which allows DB replications to be started in PowerShell scripts.

Yes. A custom create DB script needs to be used in the Advanced section of Quick Setup. The option to enter custom DB script becomes visible/enabled only when a DB name is entered in the Basic section. See Screenshot

Both are supported. Default is Change Tracking as a more lightweight, but there are certain cases in which CDC is a better option (such as when tables have unique indexes but no PKs), and in some use cases the only option (if building a SQL Server to Redshfit DW Type-2 data warehouse or data lake with CloudBasic). No difference in the way the replica is maintained. Change Tracking/CDC is related to the source processes handling.

Schema change tracking/DDL is part of the ongoing replication. Default replication settings are set to support the DR use case (i.e. a newly added table will be automatically created on the replica, seeded and transitioned to continual tracking of changes mode). New table fields and new objects will be seamlessly synchronized to the replica. If creating Read Replicas for reporting then a different set of replication settings may be more applicable (such as tables only to be tracked for schema changes, or newly added tables to be discovered but added to the table exclusion list only, and be available for manual inclusion. The read replica can be optimized for read-heavy queries (indexes added), but then the DDL/schema change tracking must be configured to not drop the objects created on the replica.

CloudBasic will seamlessly enable it as part of the initial seeding (enable script will be presented for review, you would need to agree and CB will execute it against the source. If CDC happens to be already enabled, then it will be reused, not reset).

We have not seen any issues with volume of changes handling. We can plan a no-cost POC, with a stress test being part of it.

We have not seen any performance impact issues. In rare cases of unacceptable workload on source/replica SQL Servers due to poorly designed databases, advanced configuration options are available to fine tune the continual replication processes, down to controlling number of concurrent tables, and connection pool size.