Enhance availability with Multi-AZ on Amazon RDS Custom for SQL Server- AWS Database in 15

Key Takeaways

foreign [Music] my name is Anuja Malik I'm the product manager for RDS custom for SQL server today I'm excited to dive deep on multi-az feature for RDS custom for SQL Server few reminders for you before we get started feel free to ask any questions during the session because we have a panel of RDS experts to answer those questions for you online and secondly at the end of the session you will receive a survey and we would love to know your feedback about today's presentation the agenda for today's session is to take a quick overview of RDS custom for SQL Server look at the use cases and the features of the service next we will talk about bring your own media functionality with RDS custom SQL Server that allows you to leverage your existing SQL Server licenses as per Microsoft's licensing terms and finally we will talk about the high availability with RDS custom SQL Server so res custom for SQL Server is a managed database service that automates database Administration tasks and operations while making it possible for you as a database administrator to access and customize your database environment and operating system RDS custom is a great fit for those SQL Server workloads that need a managed database service and need to use their existing SQL Server licenses as per Microsoft's licensing terms RDS custom allows you to migrate to AWS with least amount of re-architecture for your current stack since RDS custom allows you to access the underlying OS and database you should be able to migrate your database workloads with a lift and shift approach and lastly you should use radius custom when you have a workload that needs granular control such as direct access to the file system or need Master database access or sys admin access to SQL Server it can be used for workloads that rely on SQL Server features such as CLR XP credential or file stream the table here highlights the configurations and features supported by RDS custom for SQL Server it offers many inbuilt features such as managed High availability automated backups point in time Restore for up to 100 databases and managed AWS managed monitoring it supports both SQL and windows Authentication you can join your RDS custom instance directly with your active directory without having to integrate it with AWS managed Ed so recently we released support for bring your own media for RDS custom SQL Server that will enable customers to deploy custom database instances using existing SQL Server licenses subject to Microsoft's license in terms customers can install SQL server on an ec2 instance with their own media they can then create an Ami Amazon machine image of this ec2 instance using this Ami customers can create a custom engine version cev which is a pointer to the Ami and finally using cev RDS custom database instance can be deployed you can choose to deploy it in a single easy or a multi-eazy mode so let's talk about multi-az with RDS custom SQL Server RDS custom for SQL server has an inbuilt multi-az feature that allows you to deploy your custom instance across two availability zones providing High availability and protection against AZ failures these availability zones are located in a physically distinct and independent infrastructure res custom uses Block Level storage replication to synchronously replicate your data between primary and secondary nodes since it is synchronous it provides zero data loss at the time of failover and because we use Block Level replication we are able to replicate up to 5000 databases across the availability Zone it also replicates system database objects such as your SQL logins SQL agent jobs and Link server configurations thereby reducing the overhead so let's see the multi-az in action once you provision a multi-az custom database instance the service creates the primary and the secondary database instance with the provision storage capacity whatever you define at the time of creation now at any given time only the primary database instance will access the storage to read and write to the databases all the rides on the primary database instance will replicate to the standby instance synchronously at the Block Level now let's say that RDS detects a failover so in the case of a failure on your primary database instance whether at the storage or at the host level the standby database instance will automatically become the new primary and will now read and write to storage and SQL services will quickly go through the instance recovery and resume on the standby database instance you don't need to change the application connection string in case of a failover because the failover mechanism will automatically change the DNS record of your database instance to point to the standby database before we get into the demo I want to call out that you must set up the prerequisites for multi-az in RDS custom at your end these prerequisites are listed on our documentation you can choose to create these prereqs using a cloud formation template provided in the docs or you can even set it up manually now multi easy in RDS custom uses Amazon sqs simple Q service to communicate between the two nodes with that let's take a look at the demo for multi-az in RDS custom for SQL Server here is my audience custom SQL Server instance I created it using my own SQL Server standard edition license currently the multi-az flag for this instance is set to know in order to set up multi easy for this instance I need to first set up the prerequisites which are listed on our documentation so the the prerequisites talk about setting up sqs endpoint and granting the necessary permissions to the instance profile role so let me go ahead and find out the instance role for this instance so this is my IM instance profile now I'll go to my VPC endpoints create endpoint I'll give a name i Custom C ql sqs endpoint under the AWS Services I choose sqs I'll choose the VPC where my RDS custom instance is deployed I'll choose the subnets which are being used by my custom SQL instance IP address is ipv4 and then in the security groups I'm going to use the security group that is attached to my VPC and then I'll attach the policy over here the policy is again one listed on our documentation we need to give the input for AWS partition the region where our instance is running our account ready and the IM instance role which was attached to our instance and then I say click create endpoint all right so once our sqs endpoints are created I'm going to update the instance profile to with permissions to access Amazon sqs so I'll go to iam under IM I find for rules and then within rules I look for that but to uh for my particular uh I am instance role that is attached to my custom SQL profile I'll edit the policy add additional permissions choose a service sqs on our documentation we have called out four actions that are needed so those are get queue URL receive message and send message and delete message then under the Arn I'm going to specify the region where my custom instance is located my account number and in the queue name I'm going to say that any resource that is prefixed with do not delete RDS custom then under the conditions I'm going to choose the resource tab again this is called out on our documentation where the key is RDS custom string like and the value is custom SQL Server so this is the policy and save changes after I have added the permissions to the sqs I'll make sure that in my security groups the port number 1120 is allowed for both inbound and outbound as well as I update my network ACL that allows the TCP ports from 0 to 65535 between the source subnet of the database instance once I have met these four prerequisites I'll go back to my RDS instance and now I am ready to convert it from single AC to multi-az I'll choose my database instance go to modify under the availability and durability I'll choose create a standby instance continue and I'll choose to apply these changes immediately modify this change should take about 30 to 40 minutes for converting to multi easy meanwhile I have another instance which is a multi-az instance so let's see how the failover looks like first of all we see that this instance is currently running in US is to be so if I go to my ec2 console in my ac2 console I will see that there are two easy to instances for my RDS custom instance one of them is running in US is 2A and another is running in Us East to B now please note that in case of RDS custom you have full access to both of these ec2 instances even though one of the SQL instance is active and another one is passive but you will be able to log in an RDP to both of these so how would you know that which one is active currently we saw on the audience console that RDS custom instances currently running into B so this is the one which is our primary I also want to create a new user and a new login so let me try connecting to my application using a very basic back file which is making a connection to my SQL Server while this application is running I will go ahead and initiate a failover to initiate a failover I go to my audience console choose my RDS instance and I say from actions reboot and this is the error that our application is now receiving when trying to connect so there was a failover and now it is trying to connect fall over to the standby instance and our application is back again so if we take a look at the timestamp it took about 50 seconds 52 seconds to uh for the failover to happen when our application started the connectivity started failing to the time when the application was able to connect back again if you notice that one of the RDP connections were lost because of the reboot and if we just refresh these Services we will see that the SQL Server is online on this instance again so let's connect to our SQL Server management Studio on our new primary and we will see that there is a new database that has been created and not only that we also created a new user and we see that that user is also created so that was our demo for multi-az if you need more resources to dive deep on RDS custom for SQL server or if you need to learn more about how to set up multi-az on your RDS custom for SQL Server instance I have provided some quick links in the resources section I would like to thank you for your time uh to learn about multi-easy on RDS custom for SQL server today we are looking forward to hear from you in the survey feedback thank you [Music] thank you [Music]