How to Create and Connect to RDS Custom for SQL Server Instance | Amazon Web Services

How to Create and Connect to RDS Custom for SQL Server Instance | Amazon Web Services


How to Create and Connect to RDS Custom for SQL Server Instance | Amazon Web Services

Amazon RDS Custom is a managed database service that allows businesses with applications that need customization of the underlying operating system and databases that support them while getting all the automation, durability, and scalability benefits of a managed database service. RDS Custom automates database administration tasks and operations while providing the access needed for legacy, packaged, and custom applications.


This video will cover the requirements for creating an Amazon RDS Custom instance. We will also demonstrate how to create an Amazon RDS Custom for SQL Server, pause/resume RDS Custom automation, and an example of how the support parameter detects an issue with your RDS Custom instance.

Learn more:
Working with RDS Custom for SQL Server: https://bit.ly/3zj9iix


Requirements and limitations for Amazon RDS Custom for SQL Server: https://bit.ly/3yZiozM


Setting up your environment for Amazon RDS Custom for SQL Server: https://bit.ly/3v4ow8v


RDS Custom support perimeter and unsupported configurations: https://bit.ly/3PHMIFs


Subscribe:
More AWS videos - http://bit.ly/2O3zS75
More AWS events videos - http://bit.ly/316g9t4

ABOUT AWS
Amazon Web Services (AWS) is the world’s most comprehensive and broadly adopted cloud platform, offering over 200 fully featured services from data centers globally. Millions of customers — including the fastest-growing startups, largest enterprises, and leading government agencies — are using AWS to lower costs, become more agile, and innovate faster.

#AWSDemos #RDS #AWS #AmazonWebServices #CloudComputing


Content

0.78 -> - Hello, everyone.
2.1 -> Welcome to another video on AWS databases.
5.7 -> My name is Mesgana Gormley.
7.38 -> I am a Senior Database Specialist
9.3 -> Solution Architect at AWS.
11.64 -> This is the second video in a multi-part series
14.4 -> on Amazon RDS Custom for SQL server.
18.685 -> In this video, we are going to work through step by step
22.14 -> on how you can create and connect
25.29 -> to your Amazon RDS Custom for SQL server.
28.23 -> Please check out upcoming videos
29.97 -> on additional information and deep dives
32.28 -> on RDS Custom high availability,
35.25 -> migration scenarios, and so on.
38.07 -> All right, let's get started.
42.45 -> Here is the agenda for this video.
45.15 -> I'll be spending more time showing you a demo
47.94 -> on setting up the environment,
50.34 -> completing all the steps required
52.44 -> in order to successfully create RDS Custom
55.68 -> for SQL server instance,
57.63 -> and I will then create RDS Custom instance
60.51 -> and show you how you can connect to it
63.66 -> using RDP Client.
65.4 -> I will also show you how you can pause
67.77 -> and resume automation.
69.63 -> And then we'll see an example of a support parameter.
73.83 -> If you are new to RDS Custom,
76.17 -> I highly encourage you to check out the first video
79.8 -> in this series where I cover the technical overview
83.82 -> and the use cases for RDS Custom for SQL server.
87.84 -> Before we start creating instance,
90.06 -> let's talk about the steps required
92.61 -> in order to create RDS Custom for SQL server.
95.97 -> The first requirement is KMS.
98.07 -> You must create a symmetric customer master key.
101.16 -> The AWS managed key is not supported.
103.92 -> If you have an existing KMS key in your account,
107.22 -> you can use that to create RDS Custom.
109.98 -> Next, you need to have an IAM role
112.65 -> and instance profile created as well.
115.29 -> You need to provide this IAM role necessary permission
119.67 -> to access KMS key, S3 and the other AWS services.
125.79 -> You will then add this IAM role
128.37 -> to your RDS Custom instant profile.
131.19 -> Next, you have to configure the VPC,
133.92 -> making sure that RDS Custom can communicate
137.22 -> these other AWS services used in the RDS Custom environment.
141.93 -> These services include CloudWatch, S3,
144.27 -> systems manager, and so on.
146.25 -> We covered this in the previous video.
148.68 -> If the RDS Custom cannot reach any of these services,
153.21 -> your instance creation will fail.
156.27 -> Or if you already created your instance successfully
159.63 -> and then it was running,
160.89 -> and at some point, for some reason, RDS Custom loses access
165.06 -> to any of these services,
166.68 -> your instance will go to the unsupported configuration.
170.43 -> You can create and configure
171.9 -> this required services manually,
174.9 -> but the easiest way will be to use
176.64 -> the cloud formation template that's readily available
179.31 -> in our documentation.
180.78 -> I'll show you where those are and then how you
182.79 -> can get that cloud formation template
184.92 -> so that way you don't have to create and configure
187.38 -> this required services manually.
189.99 -> Once required services are correctly configured,
193.62 -> you can simply create the RDS Custom instance.
196.77 -> Once instance is in available state, you can connect
199.89 -> to your instance using RDP client.
202.59 -> So to connect to the database instance using RDP,
206.217 -> the VPC security group must allow
209.49 -> inbound traffic on port 3389.
213.24 -> Also, the underlying Amazon EC2 instance,
216.69 -> we need to have the Windows firewall configured
220.14 -> so it can allow a remote desktop access.
223.56 -> We will use system manager to run a command
226.53 -> that will configure the firewall on the host for us.
229.8 -> Let me get into a demo now to show you things in action.
233.91 -> Okay, so if you go into the AWS documentation
237.06 -> in the RDS user guide, there is a section for RDS Custom.
241.29 -> So if you go to the SQL server one and then to setting up
244.77 -> your RDS Custom for SQL server environment,
248.01 -> and in here we want to go to the configuring
251.46 -> with AWS cloud formation section where you'll be able
255.18 -> to download the JSON file that is needed to set up
259.89 -> the prerequisite for our demo instance.
263.46 -> Save this into my download.
268.47 -> And while that is downloading,
271.2 -> here is a list of the resources
273.3 -> that will be created by this cloud formation template.
276.3 -> I will encourage you to go through this so you
279.39 -> can understand what the cloud formation template is doing.
282.72 -> Here is also the section guiding you step by step
285.87 -> how you can create the cloud formation stack
288.69 -> using the JSON file.
291.81 -> So let's go ahead and create our cloud formation stack.
295.65 -> Here I am in the cloud formation console.
299.22 -> I'm going to go ahead and then create the stack.
304.77 -> And I will choose the file I just downloaded.
312.69 -> Okay, for the name, I wanna use the same name in here.
316.77 -> Just copy that and add demo.
322.23 -> As you can see in here, here's the CIDR block for the VPC
326.1 -> and it is going to create two subnets as well
328.95 -> and then you can see here CIDR block for the subnets.
332.85 -> Feel free to modify this according to your requirement.
336.03 -> I'm just gonna accept the default for this demo.
340.62 -> I will accept all the default here as well.
345 -> And I will acknowledge and click on create stack.
349.35 -> And this can take about four, five minutes.
351.84 -> I'm gonna pause the video here
353.19 -> and come back when it's ready.
355.68 -> All right, our stack is ready.
359.55 -> And if you go to the output section,
363.21 -> you will see the resources created
366.63 -> by this cloud formation template.
369.87 -> As you can see, here's the VPC
372.33 -> named RDS Custom SQL Server Demo VPC
375.81 -> and there's also a security group
377.97 -> that we can use or attach to our RDS Custom instance,
382.89 -> and then also a KMS key, an instance profile,
387.93 -> and also a DB subnet group created here.
392.19 -> So once this cloud formation template
394.86 -> create the initial resources,
396.67 -> there are few additional step that you need to take
399.9 -> in order to be able to connect
401.67 -> to your RDS instance successfully.
404.52 -> The first thing you need is this VPC
407.28 -> is only coming with two private subnets
410.34 -> so we need to have a public subnet
413.34 -> where we can have Amazon EC2 instance
416.58 -> or our jump server created, right?
419.52 -> So we need to have public subnet.
422.13 -> For the public subnet to work,
424.08 -> the VPC need to have internet access,
426.81 -> so therefore we need to create internet gateway
430.74 -> and attach it to this VPC
433.23 -> created by our cloud formation template.
435.36 -> So I'm gonna go through all of that steps
437.19 -> so you know exactly what you need to do
440.19 -> in order to have your test environment running.
443.55 -> Then the other thing is there is security group
446.73 -> created by this cloud formation template,
448.62 -> however, if you look into the security group,
451.02 -> there is no rule currently defined,
453.66 -> so therefore we need to add a rule for our DP access
457.77 -> so we can connect to it from the jump server.
460.74 -> The other rule is for super server port for subset three.
465.027 -> And the other change I need to make is in the network ACL.
469.41 -> The network ACL currently is very restrictive
473.52 -> and we need to modify that in order to allow
476.85 -> inbound and outbound connection to the private subnet
480.36 -> so that way I can connect to that RDS instance.
484.59 -> And one other thing I need to create will be an EC2 instance
488.61 -> that I can use as a jump server
490.77 -> and I'm going to deploy that EC2 instance
493.68 -> in the public subnet that I will be creating.
496.8 -> So those are the additional steps that I need to complete
500.55 -> and let's get started.
503.76 -> So the first step is going to VPC.
513.6 -> And I will go to internet gateway
517.38 -> and I'm going to create internet gateway.
521.76 -> I'm going to name this IGW RDS Custom Demo.
531 -> That's all I need.
532.35 -> I'll create the internet gateway now.
534.36 -> Next I need to attach this to the VPC
538.2 -> so I'll click on attach to VPC.
541.17 -> This is our VPC, and click on attach internet gateway.
548.04 -> That is created and then attached.
550.35 -> Next, I need to create the subnet.
554.37 -> So from the subnet, I will click on create subnet
559.44 -> and select the VPC.
561.24 -> That would be our RDS Custom Demo VPC.
565.02 -> And for the subnet name,
566.303 -> I will call this RDS Custom Public Subnet.
576.78 -> Then for the CIDR block for this subnet,
579.75 -> I will give it 10.0.112.0/20 is sufficient.
589.74 -> That's all I need to provide.
591 -> I'll click on create subnet.
593.7 -> All right, go to the route table
596.4 -> and click on the route table ID.
601.05 -> Then if you look into the routes,
603.6 -> currently only the local route is there by default.
607.29 -> So I'm gonna edit the route.
610.71 -> Click on add route
612.24 -> and this is gonna be for the internet access.
614.97 -> And I will select internet gateway.
618.99 -> Then pick the one that says attach to our VPC.
623.67 -> And click on set change.
625.62 -> That is all for the route.
628.71 -> So now here is a route to the internet.
633.54 -> Next I need to modify the network ACL
636.66 -> that's associated to the private subnet.
640.98 -> I'm going to search for custom again.
644.88 -> And the first one is the one that's associated
647.43 -> with my public subnet.
649.17 -> I want to modify the second one,
650.88 -> which is currently associated with two private subnets.
654.09 -> If you go to the inbound rule,
655.32 -> you'll notice that this is very much restrictive
657.93 -> and then it's only allowing a certain port ranges here.
661.14 -> I'm going to modify both inbound
663.36 -> as well as the outbound rules.
665.19 -> So, because this is my demo environment,
667.35 -> I'm going to open in wide.
669.33 -> Please don't let your environment be intentional
671.94 -> if you have to modify the network SL in your environment.
676.02 -> So I'm going to edit the inbound rules
680.1 -> and I'll delete this first one and allow all traffic
686.82 -> and save the changes.
691.77 -> And if I go now, as you can see, it's wide open.
695.07 -> And now do the same for outbound rules.
704.49 -> And again, I route and save the change.
709.65 -> So now I have modified both inbound and outbound rule
713.16 -> to be wide open.
715.95 -> Next, I need to create a security group that I can attach
720.24 -> to the version host I'll be creating.
722.82 -> So let me go to the security groups
726.54 -> and click on create security group.
729.63 -> And I'm going to name this RDS Custom Security Group
735.96 -> for RDP Access and select the VPC and other rule.
746.28 -> And this is going to be RDP rule,
749.16 -> so I'm going to be explicit there
751.83 -> and I'm going to open it wide open again
754.95 -> because I want to be able to RDP into this version host.
759.81 -> Okay, that's all I need here
761.07 -> and click on create security group.
764.97 -> And one last step before creating our instance
767.79 -> is I need to look into the other security group
771.06 -> that was created by the cloud formation template.
774.69 -> I will search for custom
776.64 -> and it is the RDS Custom Instance Security Group.
782.16 -> As you can see, there is no inbound rules there
785.28 -> so I need to edit inbound rules and I'm going to add a rule.
790.68 -> And first I want to add RDP,
795.18 -> and this time I'm not going to open it wide to internet.
799.98 -> That's because I'm going to be accessing
802.41 -> the RDS Custom EC2 instance
805.08 -> as well as the super server instance from my jump server,
809.19 -> so the source for this rule must be the security group
814.05 -> that I just created in the previous.
819.75 -> And it is this first one and I will add another rule
823.65 -> for SQL server and then the same thing.
831 -> And then save rule.
833.88 -> So there were a few additional steps that I need to take
837.81 -> before I can start the creation.
840.45 -> That is all I need
841.8 -> in order to create and connect my RDS instance
845.28 -> so I'm going to go to the RDS console now.
848.1 -> As you can see, there is no RDS instance
850.59 -> in my environment right now.
852.27 -> I'm going to click on create database
854.88 -> and select the Microsoft SQL server,
859.53 -> then Amazon RDS Custom.
862.86 -> And our goal is Enterprise Edition
865.68 -> and then you will notice that the only version supporter
868.86 -> right now is SQL server 2019.
873.81 -> And for the name, I will say RDS Custom SQL Server Demo.
883.38 -> And just like RDS, you still need to provide password
886.95 -> for the master user account.
898.83 -> And I'm good with m5.xlarge
902.19 -> and I will accept the default
904.08 -> for the storage as well, 100 gig is enough.
907.83 -> And for the VPC, I need to make sure I selected
912 -> the RDS Custom VPC that was created
916.38 -> by the cloud formation template.
919.62 -> And then this is also the DB subnet group
922.17 -> created by the template.
924.45 -> And for public access, I'm going to say no
927.12 -> because I'm going to access this environment
929.67 -> only from my jump server.
933.75 -> I will delete the default
935.1 -> and select the RDS Custom instance SG,
940.71 -> which is the one we just modified.
943.35 -> And for availability zone, it doesn't matter,
945.96 -> but our goal is A.
950.04 -> And I'm going to accept the default port here
953.52 -> and for the IAM instance profile,
955.65 -> so this is where it's different
956.94 -> than the regular RDS SQL server.
960.06 -> I need to select the instance profile
962.64 -> created by the cloud formation template
965.22 -> and that is RDS Custom Super Server Demo US-West-2.
970.11 -> And then the same for KMS key.
973.32 -> I need to select the one that was created by the template
978.57 -> and that's all I need to provide.
981.6 -> And click on create database.
984.63 -> My RDS Custom instance is now being created
988.26 -> and this is going to take about 30 minute or more
991.5 -> and I'm gonna pause the video in here
994.29 -> and create a version host in that public subnet
998.04 -> then come back and resume the video
999.93 -> once the RDS Custom instance is ready.
1003.56 -> As you can see here, two EC2 instances
1006.95 -> are currently being created
1009.05 -> and this first one is the resource name that is showing
1012.68 -> for the RDS Custom instance.
1016.292 -> Right now it is showing the resource ID in here,
1018.41 -> but later once the RDS Custom is completed,
1022.49 -> this is going to be updated with the correct name
1025.01 -> which will have that do not delete prefix.
1028.82 -> The second one is the EC2 instance
1031.91 -> with the jump server I just created
1034.34 -> so this will be the instance I will use to connect
1037.28 -> to the underlying EC2 instance hosting my RDS Custom.
1041.6 -> So as you can see, the instances is almost ready.
1044.66 -> It is currently being backed up
1046.82 -> so I can actually go in and start connecting
1049.73 -> to the jump server, and then from there,
1052.4 -> we can connect to the RDS Custom EC2 instance.
1056.27 -> So let me connect.
1073.652 -> Copying the DNS name.
1081.59 -> And the user name is administrator.
1090.47 -> And then here is the password.
1104.81 -> I'm connected to my jump server at this point.
1109.58 -> This is a new EC2 instance I just created.
1113.12 -> I will have to download SQL Server Management Studio
1116.45 -> so I can connect to the RDS Custom in the port.
1120.65 -> From here, I could RDP into the RDS Custom EC2 instance.
1126.08 -> Before that, there are a few more steps I need to follow.
1129.98 -> First one is grabbing the password for the EC2.
1135.269 -> I need to go to a secret manager
1139.79 -> to get the key pair information for my RDS Custom instance.
1144.38 -> Here is do not delete prefix resource
1150.08 -> and I need to go retrieve secret value
1153.77 -> and I need to use this to create the password
1156.86 -> for the RDS Custom EC2 instance.
1160.01 -> I need to configure the local firewall rule
1163.58 -> on that EC2 instance.
1165.47 -> So what I need to do is click on connect
1169.67 -> and I'm gonna use the sessions manager
1172.34 -> to run a PowerShell command.
1177.5 -> So I have the session established to that EC2 now.
1180.92 -> I need to go back to the documentation
1183.14 -> and grab the command I need to run
1186.41 -> and you can find that in the creating,
1188.45 -> connecting to RDS Custom instance.
1191.06 -> If I scroll down, here is the command I need to run
1195.32 -> in sessions manager.
1197.66 -> Copy that into my clipboard.
1200.15 -> And all I need to do is just execute that same command,
1203.03 -> no modification is required.
1209.03 -> So now the local firewall is configured.
1213.68 -> The next step is now to retrieve the password
1218.06 -> and here I already have that secret value.
1222.5 -> I need to copy the entire key content
1231.11 -> and paste that in here and create the password.
1234.71 -> Okay, here is the password.
1237.17 -> So my RDS Custom instance is in a private subnet
1241.19 -> so I have the private IP only for that.
1245.09 -> Compute that and paste it in there
1251.21 -> and the username is again administrator
1256.58 -> and click on connect.
1259.22 -> And now I can go ahead and then bring the password
1266.18 -> and paste that in.
1269 -> And here I am connected to the EC2 instance
1272.72 -> that is hosting my RDS Custom instance.
1279.256 -> And now the next step is to test the SQL server connection
1282.8 -> from Management Studio on my jump server.
1287.18 -> You may notice that the RDS Custom console
1290.96 -> is very much the same as a regular RDS.
1294.08 -> If you've used RDS before,
1296.21 -> this should look very familiar to you.
1298.1 -> There are few differences,
1299.39 -> mainly in here you'll see the RDS Custom path,
1302.78 -> the automation mode that will show in here.
1305.66 -> By default, when a new instance is created,
1308.9 -> RDS Custom will have full automation
1311.45 -> and that's what you're seeing in here.
1313.25 -> And if this was changed,
1314.6 -> you will see the current status reflecting in here.
1318.08 -> Besides that, the monitoring logs and configuration,
1321.02 -> all of these pages looks very similar to the RDS.
1325.82 -> So I need to copy RDS Custom end point.
1331.28 -> And now I can go back to my version host.
1334.46 -> I'm going to past the end point here.
1340.7 -> And I will provide the password
1342.44 -> that I typed in when I was creating the RDS Custom instance.
1352.16 -> And that's it.
1353.63 -> I'm able to connect using Management Studio.
1357.2 -> This is my RDS Custom Demo Instance.
1360.38 -> That's all I wanted to show you
1361.91 -> for the creating and connecting to RDS Custom instance.
1366.23 -> Next, I want to show you how you can pause and resume
1370.407 -> the RDS Custom automation mode.
1373.1 -> To change the automation mode,
1374.87 -> all you need to do will be just click on modifies
1377.78 -> and in the RDS Custom there is automation section.
1381.65 -> As you can see currently,
1382.73 -> it's in full automation mode.
1384.65 -> To pause it, I can click on the pause
1387.47 -> and the default is 60 minutes,
1389.78 -> that's actually the minimum.
1391.28 -> You can pause it up to 24 hours.
1393.89 -> So for now, I'll just accept the default,
1396.5 -> pause it for one hour.
1399.02 -> Click on continue and click on modify DB instance.
1405.02 -> And this is going to take a few minutes
1406.52 -> to change the automation mode.
1408.74 -> I'll pause the video here and come back.
1412.16 -> The automation is now paused.
1415.07 -> If you look into the summary,
1416.63 -> you will see that the automation is currently paused
1419.42 -> and it will resume in 57 minutes in my example.
1422.81 -> At that point, when this time runs out,
1425.24 -> the RDS Custom will automatically resume
1428.09 -> in full automation mode.
1430.49 -> I have the option to wait the 57 minute
1433.73 -> and have the RDS Custom automatically change
1437.84 -> the automation mode to the full automation mode,
1441.53 -> or optionally, I can go back to the instance and modify it
1446.45 -> then change that to full automation mode.
1452.39 -> The instances is now available again
1454.67 -> and then it is in full automation mode,
1457.97 -> as you can see here.
1459.71 -> That's all for this part again.
1462.17 -> Now let's go ahead and then see as example
1464.63 -> of the support parameter.
1466.85 -> Like I mentioned in the previous video,
1469.28 -> now this custom support parameter monitors your environment
1473.39 -> and take corrective action when possible.
1477.62 -> And when it's possible,
1479.03 -> it will automatically take corrective actions,
1482.24 -> but in cases where it's not able to recover,
1485.87 -> then it will put your RDS Custom instance
1489.26 -> into unsupported configuration.
1491.9 -> We're going to see an example of both scenarios.
1494.96 -> First, let's see how RDS Custom correct problem
1498.08 -> when it detects.
1499.1 -> To initiate that, I want to go to the RDS Custom instance
1505.43 -> to go to services.
1508.76 -> And I've already discussed about some of the agents
1512.12 -> that are running in your RDS Custom instance.
1515.87 -> So one of those agents is RDS Custom agent
1518.6 -> which is currently running.
1520.31 -> I wanted to go ahead and then stop this agent.
1523.07 -> So the RDS Custom monitoring is going to detect that
1526.85 -> and it will restart the service
1528.65 -> so it's not going to cause any issue
1530.99 -> to a RDS Custom instance.
1536.63 -> It's now in stop mode
1538.91 -> and then if I go back to my RDS Custom,
1541.19 -> I won't see any issue because of my action right there.
1545.66 -> If I go back to my RDS Custom console,
1548.93 -> I can see that the instance is still available,
1552.26 -> but if I go to the RDS Custom logs and events,
1556.76 -> I will see a new entry logged in there telling me
1560.3 -> that RDS Custom automation has found an issue
1563.69 -> and then it took a corrective action.
1567.53 -> Here is a message telling you that RDS Custom
1570.17 -> is performing a corrective action to recover
1572.96 -> the RDS Custom agent on your instance.
1577.115 -> And now during this time, the instance is still unaffected.
1579.62 -> The status always show as available.
1582.5 -> So that's one of the example I wanted to show you
1585.29 -> that RDS Custom automation
1587.39 -> is able to take corrective action when it can.
1591.35 -> The second scenario I wanted to show you
1593.51 -> is manually stopping the EC2 instance
1596.633 -> that is hosting my RDS Custom instance.
1601.04 -> That is definitely not a supported action
1603.68 -> so that will put my RDS Custom instance
1607.88 -> into unsupported configuration.
1610.46 -> Let's go ahead and then see that.
1612.65 -> And here is my do not delete RDS Custom instance
1616.46 -> and I'm going to go to instance state and stop the instance.
1623.6 -> And this is gonna take few minutes
1624.76 -> so I'm gonna pause the video here
1626.69 -> and come back when the status change.
1629.96 -> Here, as you can see,
1630.95 -> the status is now changed to unsupported configuration.
1634.22 -> At this point, RDS Custom is unable
1636.98 -> to take corrective action and the instance
1639.35 -> is going to remain in this state until I fix the issue.
1644.99 -> I can go to the instance logs and events
1648.17 -> and then see the information about that problem.
1652.46 -> As you can see, the RDS Custom instance
1654.26 -> is going out of parameter
1655.97 -> because the unsupported configuration was used
1658.49 -> on the EC2 instance,
1660.26 -> which is I stopped the EC2 instance, right?
1662.48 -> That's not a supported configuration.
1665 -> And to bring back your instance into the available state,
1668.81 -> you have to find out what the problem is
1671.54 -> and then resolve that.
1673.01 -> In my case, I'm going to go back to my EC2 dashboard
1677.42 -> and currently the instance is in a stopped state.
1681.5 -> I need to start this
1683.36 -> then that should automatically resolve my issue
1686.48 -> on this specific example.
1690.68 -> In few minutes, instance is now back to available state
1695.78 -> and that's all for this demo.
1698.18 -> Thank you for watching this video.
1700.13 -> Please check out upcoming videos to learn more.

Source: https://www.youtube.com/watch?v=6rJArhGNens