AWS Summit DC 2021: Migrating SQL Server to Amazon RDS

you are in migrating sql server to amazon rds and i'm delighted to introduce our speaker today and el perez senior solutions architect great thanks everyone for joining me here today and was as jan mentioned i want to talk about migrating sql server to amazon rds a little bit more background on myself i've actually been with aws a little over five years and i actually support our public sector customers specifically in higher education in new york city and i have worked with some of our customers supporting microsoft facebook those that have been supporting microsoft workforce for closer to eight years or so so we'll dive right in what we'll cover today are three things what are the benefits of migrating sql server to amazon rds assessing and planning your sql server migrations and also we'll dive deep into the options for migrating sql server to amazon rdx what we won't be covering today are basics of operating and administering sql server basics of amazon rds or refactoring sql server the intent is to give you as much information as possible to move your self-managed on-prem databases to our fully managed amazon ideas all right so here's our agenda for today we'll start by talking about why you would like to migrate your databases options for running your databases on sql options running a single server on amazon aws migrating sql server workloads specifically the three main options which are sql server backup and restore sql server replication and database migration servers so the three big topics we'll cover and then we'll wrap up at the end as jan mentioned if you have any questions feel free to go up to the mic ask a question more than happy to spend a few extra minutes diving deep with you all okay so why migrating workloads to aws for many of our customers spend a lot of time doing the day-to-day maintenance of these databases that includes the storage the maintenance patches and the backups one of the main benefits of using amazon rds is that all of this is fully managed for you when you run this service we're automating your packages automating your backups and you can do single click high availability so business drivers for migraine to the cloud vary from customer to customer they can be cost savings it could be trying to consolidate your data centers it could be around your backup and dr strategy digital transformation all the above so yesterday my customer was here so new york university and i'll share this anecdote with you all and we began doing a data center closure with them about two years ago two and a half years ago let's say and their initial driver was a they had to get out of the data centers inside of manhattan and also reduce costs one of the unlikely things that we discovered while we're doing the migration is that the speed at which they can actually operate in the cloud significantly improve their vr strategy to give you an example they have a large financials app running on a database and they will do this yearly vr test and that dr test would take them to restore this one application around 280 hours their recovery time objective for that same application was actually seven hours when they moved to aws they could restore that same application in two hours so you may start with one initial idea or goal but quickly you'll realize that there's many benefits to operating inside of the amazon universe infrastructure so once you're ready to migrate you may be wondering okay how do i get there what are the tools available to me to help me get there we have a wide range of tools available to you and i'll start i'll pick a few just to talk about them so for example on the left hand side there you see the cloud adoption readiness tool application discovery tool and even microsoft sorry migration evaluator as well and these tools are designed to not just assess your current environment but also help you analyze your current business processes that you have to see if you're ready to even migrate to the cloud so these are tools that are available for you for free inside of your ews account you can look at them some of these are just websites you can click on what i'll mention as well at the end of the presentation i do have a qr code so any links that i share or references that i make will be available to you at the very end of the presentation as well so i see phone phones coming out which is great take it out later i'll call it out once i have the qr code up there some of the tools that we see customers leveraging quite a bit when it comes to migrations are the migration hub and our prescriptive guidance page really popular tools migration tab is going to be your one stop shop to be able to monitor and maintain manage your overall migrations to aws and it ties into many of our other aws services prescriptive guidance is actually a web page on our website that actually has several documented use cases it talks about some of the personas we need to actually be able to do these very specific workloads and we actually do have several papers on there on migrating sql not just the rds but also ec2 and other database engines as well so let's talk about running sql server on amazon rds start amazon rds as i mentioned earlier is a fully managed service that runs on the aws cloud you can have up to 128 vcpu four terabytes of ram and up to 16 bytes of storage and in terms of storage you could also use our provision i o i o one volume up to 64 000 iops and if you're looking for that level performance that's actually going to be our db x1e 32x large instance type we support sql server express weber standard and enterprise there's a wide range of instance types that fit just about any workload we do believe that there is a right instance type for your particular job so if you folks have any questions about any of that definitely reach out to your aws account team if you don't know who that is my evil's at the end definitely reach out to me i'm happy to answer any questions as well and with regard to storage you can leverage our tv to our io one storage for your uh database so i can't talk about migration without mentioning the 6rs today our focus is primarily going to be on a re-host a re-platform i'll quickly summarize this for you in case you're brand new to this so retain retire are when you actually keep you know the existing infrastructure you have today or you actually completely decommission any infrastructure you have replaces when you're just going to let it replace that hardware re-host aws means that you're doing a lift and shift to amazon ec2 that's what's highlighted there the purpose of our discussion today is to be focused entirely around re-platform or refactor and some people it's our re-platform two m's rds a refactor would be when you actually want to move from one database engine to another so for example if you have microsoft sql today and you actually want to move to an open source database engine like mysql or postgres that would be a refactor of your database like i said we'll be focusing primarily on a platform so now let's talk about migrating sql server workloads to amazon rds so as i alluded to earlier in the conversation you really have three choices here when you want to migrate databases to amazon rds the first one sql server backup or restore that's using native sql backups the second is using sql server replication which is the native replication built into microsoft sql the third option is going to be amazon database aws database migration service and we'll dive deeper into each one of these right now so many of you may already be familiar with your traditional sql backups where you have that bak file and use that dot bak file to either restore to previous point in time and you can actually leverage that same file to do the same thing inside of aws and you have two options for doing that so at the top we have amazon ec2 which if you recall that's going to be your rehost so lift and shift but since we're talking about going ahead to re-platform the database you could also leverage rds to go ahead and do that for you remember the advantage of rds is that you don't have to worry as much about patching or maintenance of your databases your teams can focus on other things like improving the performance of your applications or actually adding additional functionality to those particular applications i do want to spend some time talking about this very specific process because there are some steps that are required in order for you to do this so for example when you first start doing your migration using this particular pattern you actually do have to start with a full backup to load the data into the database so many of our customers may start with either a weekly backup or if they do daily full backups that's your first place you want to start you want to take that backup and actually want to copy it up to aws we have a whole array of storage solutions which i'm going to talk about in a few minutes where you can dump that data into and then you actually want to do incremental updates of that data or refreshes so as we take the differential backups do the same thing upload them up to aws and there's a series of sql commands that would run inside the sql server management studio to actually go ahead and load that data into amazon rds right there are some requirements though so on the rds database you have to enable what's called an option group and all that option group really does is give you the functionality around a very specific sql command sql query to go ahead and load that data into your database okay so going from left to right here we're going to start with amazon s3 amazon s3 is going to be your primary data store for actually importing data into rds i just mentioned that option group that option specifically allows amazon rds to go ahead and pull data from s3 so that'll be your full backup your differential backups and also your transaction logs okay and if you're not familiar with amazon s3 it's a object-based storage solution that's built on top of pws some things i would like to mention to you all today is that there are some limitations that you should be aware of so for example the largest individual object size is five terabytes so if you have sql backups that are larger than five terabytes you actually have to do what's called a multi-file backup and do a multi-file restore so at the end i do have links for this at qr code that i mentioned so definitely reference that information if you do have backups that are larger than five terabytes that would definitely be applicable to you also keep in mind that amazon rds has a maximum size of 16 terabytes like i mentioned earlier so if you have databases that are larger than 16 terabytes that's probably a candidate for either ec2 or a actual uh platform of that actual solution so moving that database from sinai platform a refactor of that database from one database engine to another because our other database engines actually support the 64 terabytes compared to 16. okay so then we have our slow family that's gonna be snow cone snowball and snowmobile so these are options for offline restores i do have some additional slide decks that go into detail and i'll cover those in a moment and lastly we have storage gateway and data sync these are going to be your primary tools for moving data from your on-prem data center over to aws storage gateway comes in three flavors we have our file gateway we have our tape gateway and we also have our storage gateway sorry our disk kit volume gateway storage gaming file game yes and then we have data sync data sync is actually going to allow you to connect it to an existing file share inside of your data center and whenever you upload data to that power share you can have data sync automatically move data into your designated sd bucket and i'll cover these in detail in the upcoming slides so the first option i want to talk about is using storage gateway to move your backups up to aws the advantage of storage gateway is that it operates a traditional sme nfs and even amazon api calls so if you're familiar with existing file shares i'm sure many of you have in your own environments you could actually leverage storage gateway it comes in two flavors two forms i should say you either have a physical appliance that's actually installed inside of your data center or you can have a virtual machine that's running inside of your environment that could act either as a storage device you know like a volume yeah i study or even a file share where you can actually dump your backups into that location on-prem looks completely like every other file share but behind the scenes the appliance is actually going to upload that data up to aws to the s3 bucket that you specified the nice thing about storage gateway is that has encryption and transit as well too in the middle between the two environments your corporate data center and uws you'll see that we have direct connect and vpn these are optional so if you want to route the traffic over a private connection that's going to be more reliable than the public internet you could definitely do that or if you want to add encryption on top of that via the uh vpn tunnel that is also an option for you too although it's not required okay are you playing yeah yeah if you want to walk up and ask questions you know transactions occurring still occurring like this is okay but you know what if you need your database up and running and still receiving transactions on your on-prem stuff right absolutely either exactly so thank you for raising that's a valid point so one of the disadvantages of going with this particular migration pattern so we're talking about using the native sql backup restore functionality is that you're not capturing those changes in real time because you're not always running backups all the time and even the time it takes to upload data is significant so one of the things that i could uh mention what we see customers doing in the real world when they want to do a cut over using this type of methodology they actually go ahead and make their on-prem the read-only and then they point everyone to the aws database and then they do the last differential backup then move that backup over and that'll bring everything up to speed that's the common pattern so in terms of doing what you're asking where you want to do real-time transactions to amazon rds that's gonna be our database migration service that's gonna do it for you and i actually have a little demo and i talked about in detail but just to give folks some contact is a great call out and i don't want to disadvantage this methodology but factor that in when you do your cutover thank you you're welcome okay next we're talking about data sync and personally on bias storage data stick is one of my favorite services for me it's the most efficient way to transfer data between your on-prem environment and aws it has a really efficient way of aggregating data together and sending it in batches over to the s3 bucket and if you have there are ways to optimize s3 to get more efficient performance too so for example if you're using prefixes to create data structures inside of your backups or your files whatever you're uploading data sync is really efficient uh when it says transfer data speeds up to 10 times faster that is no joke and many times it exceeds that it's really powerful what i'll say that's really nice about data sync as well too is that you can schedule these jobs schedule let's say you don't want to do with the certainty times and saturate your utilization of your network connection you can even do um bandwidth limits on the actual transfers as well and you can schedule when those bandwidths are lifted or they go down so it's really granular intent in terms of the functionality it has and the ability to give you the transfer date of aws but very similar to storage gateway you would actually point this to point just an existing on-prem file share where you're actually putting your full differential and transaction logs and then behind the scenes it will actually go ahead and place that inside of an s3 bucket where you can then run your sql statements inside of sql management studio to then import that into s3 oh sorry import that's amazon rds then there's the offline scenario so let's say you have a data center that's in a remote location has really low bandwidth or you just don't have a good mechanism for actually transferring the data online into aws that's really where the snow family comes in handy so as you'll note here the architecture diagram mentions full database backup hypothetically you could do your incrementals using snow devices as well i mean i've seen customers they're able to get the device out same day about a week or so turnaround time to get it inside of your s3 bucket so it is possible to incrementals but you know your data is going to be severely delayed from the time that you do that lasting comments all the time you do the cut over definitely factor that in if you need to do an offline backup restore option so for those of you that are not familiar with our snow family we essentially ship to you a physical hardware device it's a storage appliance and it can't have compute associated with it if you need it snow cone being the smallest one and then we have some whole video and snowball edge as well as options and on the device itself actually has a shipping label so all you really have to do is take that device plug it into your physical server or your network environment upload your files and then ship it back and the label will be prefixed already on it and ups can just pick it up and ship it directly to our data center as it goes into an s3 bucket and the data is available for you right there in terms of what that looks like that device will go on trains like i mentioned you have the backup bucket and then once you have it inside of that bucket once again you would issue the same sql query to go ahead and import that data into amazon rds live examples in the links at the end sql server replication so this is another option for you what i really want to highlight here is that first bullet point that's really important so sequel with rds only supports transactional replication as a subscriber only with a push subscription so if you're wondering what that is and what that details i do have a link that outlines that points on microsoft doc that gives you all the information on that but that is the only way that you're able to do this within amazon rds also i'll mention that this is only recommended for one-time migrations so if you're doing a bunch of updates and changes it's not ideal it's not the fastest way to do it it's not the most efficient and you will or might see some discrepancies in your data between the source and the target right but the idea is that you would actually log into your sql server you'll configure your database to be both a publication and a distribution for that particular database and then you would connect that to rds and then behind the scenes there will need to be a network some type of network creativity between your on-prem infrastructure and the amazon cloud so that means either a vpn or direct connect would need to be pre-established before you can consider this path unless your sql server is out on the internet and that's doable by i don't know any customers running sql server on the internet so that's definitely an option as well too but we do recommend that you use vpn or direct connect for actually transferring the data via this replication mechanism so the third option and the final option that you actually have for migrating data into aws amazon rds specifically is our aws database migration service really powerful tool and i'll share a story here too some folks know that we were oracle shop we had a bunch of oracle databases and about two years ago i want to say we made this big push to actually move all our databases off of oracle into our native products when we did that we had to develop a lot of the tools to make it happen and that's really where a lot of experience came before the aws database migration service when we actually finished moving our last day basically a big party that's how big of a deal it was and i actually personally like this service live i have a little demo for you as well too but the idea is that this is a fully managed service that allows you to maintain both your source and target in real time replicating data between the two so aside from doing this a big full initial load into your database it can actually detect in real time whenever there's a change to your source type your source database and move that to the target that's what i have in my demo for you so in terms of what the architecture looks like for aws dms you start off by logging into the aws console you have to actually configure your endpoints and think of your endpoints as your connections between your source and target servers so there would need to be some form of network connectivity between the two so if you have your on-prem data center that means you need to have a vpn or direct connect to be able to talk to the aws dms service this service has a public endpoints if you talk about the internet there is encryption and transit so you are protected but you can also wrap this inside of a vpn or direct connect if you chose to do that in terms of what's actually doing the heavy lifting for you there's a dm mask replication instance that says in between the two so that's really doing the orchestrating doing the migration task and actually looking at your sources seeing if there's any changes that are being made and they're replicating that data over to the target server okay so one of the big benefits here too is that if you want to load an entire database you want to do it slowly over time you can set up several migration tasks and you can actually have it load that into the target table by table if you want the entire database you can but we definitely have customers that have large databases that see value and move in individual tables and the main reason for doing that is if you have a lot of interdependencies between your applications you want to move applications little by little to the cloud this is one mechanism for doing that but actually instead of doing a bulk import and perhaps breaking a bunch of apps because you know the table my thing like i said so great so the onboard replication phase on the actual dms instance there is going to actually look for any updates changes any um deletions on the source target and actually replicate that over to the destination this is optional if you don't want to do that you don't have to turn it on there are a few check boxes that you can dive into a tune inside of your database however when you see the demo i'm going to show you i have a video of a change happening and in real time i'm partnering that same database and you're seeing that change being replicated so how does this apply to you in the real world so let's say you start off with your database your users are connecting to it today just like they have every single day you connect to that source database you know everything is good in the world but you have this larger goal of wanting to move your database into the cloud let's say specifically amazon rds because you don't want to worry about the patching the maintenance those everyday tasks that don't have business value so what the aws tms service allows you to do is select those tables those schemas those databases that you want to move over and establish that connection with aws so once you have that initial data load and you want to do your actual cut over what that means for you is that instead of your users then connecting to that outcome database you can point them straight to your amazon rds database that they can continue operating and working as if they normally would without any disruption without them noticing any difference except that now instead of pointing to an on-prem database you're now pointing to a database that sits inside of amazon rds so the one thing i'll mention here as well maybe you're thinking the back of your mind all right so dms sounds great it sounds amazing what's the catch what do i want to know about this what can it do so what i'll share with you is that we have other tools to help you do this but one of the things that is not captured or one of the few things that is encapsulated by tms are things like stored procedures things like um any special scripting you have that's happening inside your sql database so the advantage of using a bak file is that it captures everything including those stored procedures dms can not negatively do that if you want to capture things like stored procedures you have to use what's called our schema conversion tool sdt and that will capture your actual stored procedures involving other things inside of your database so definitely keep that in the back of your mind if you're thinking about moving these databases it captures the data inside of databases primarily all right now it's double time so fingers crossed let's see if it works yes it's working okay cool so this is the aws console right here this is the database migration service what you're seeing here is the main landing page you can see that i actually have one active migration test that's there that's constantly monitoring my source database i have a few different endpoints too i have a source on target endpoint i'm going to click into this and here you can see where i have my target and my source servers configured in this particular configuration my source server actually lives inside of amazon ec2 so it's not a full you know data center i'm just simulating it by using an ec2 instance and inside the endpoints we configure all the connection properties for your database whether or not you want to use encryption the ip addresses the credentials to actually go ahead and capture that data that's all specified inside of this endpoint section of the console and then the replication instances this is where you pick how large of an instance you want to actually do the heavy lifting for you typically if you want something that has a large amount of memory um the more memory you have the more data you can load at once and you actually capture and see these metrics because it's assigned the cloud watch you can see how much it's being utilized over time and if you bump it up within a few clicks you can pump it up to a larger instance type if you needed to do that your migration task is where you specify what type of migration is it if i just want to do one time load or if i want to continuously monitor the actual database in this scenario i am monitoring the source database to replicate those options those changes to the target and even within the migration task you could have filters applied so you're only doing specific tables or specific schemas if you want to do that as well and here i'm clicking through that so here you can see that my database has actual data this is data from the source tables i have a bunch of rows preloaded in there and that's back to details page i should pivot pretty soon to the instance i'm actually logged into a database the source database which is on amazonbc2 there it is so here i have a sql query that's just going to a csv file to load a bunch of tables and i'm a point to the target server i'm gonna run a query to look up how many rows i actually have any second now there we go and when i expect where you see that two rows are returned and again this is on the target this is amazon rds right here i write down the initial load of the data there we go there are my two rows now i'm gonna go back to my source and i'm going to run that query to actually load some additional data on my source all right success now we'll go back to the target run the same query there's more data pretty interesting of course it's going to vary based off the connection speed that you have that's why you recommend using something like direct connect direct connectors give you dedicated bandwidth to your aws vpc however you could replicate this you know very easily inside of your own environments at the end i do have links with workshops and guys and walking through the same exact demonstration i just did here you can replicate yourself and do it any questions folks okay awesome so this is it we buy your phones take a picture this is the link it points to a github page has a bunch of links even some miscellaneous things that i found to be really useful when you're looking at migrating your sql databases up to amazon rds including some workshop guides so if you actually want to do the same exact exercise i just did you can't do this yourself i'll give folks a few minutes unlock your screens i know it's difficult to face masks taking your code so liberating having to go it's nice yes the point here is eventually your migrated everything how do you manage the cutover and your servers talk to the right server and you have a facility to do it as data moves so for example let's say you migrate by geography you know by state and you have the state of maryland already migrating to your applications know that you don't need to already go to the source keep your transactions there because all of your data is already on yes happy to review the questions so correct me if i'm wrong here as well so you're asking if you have a large environment that spans across multiple states right and you want to be able to manage the cut over between those production databases that sit inside of a data center to an amazon or yes database for example how would you go about doing that right yes but i'm not saying that the database there is data yeah users accessing it from different locations gotcha okay so there's a few different things here i want to mention first amazon rds primarily operates using dns and we like the usa and aws because when you talk about high availability you want to treat those individual resources like ephemeral resources if there's an issue with a database server let's say if this crashes or the operating system crashes you want to be able to fail over really quickly without interrupting the actual applications that's really the power of dns so when you provision amazon rds you aren't provided with a dns endpoint so this is all hypothetical right so let's say you wanted to manage who was connected from where to that dns endpoint you could use our amazon router 3 service to manage those connections you could tie it by geography if you wanted to or from source ip and say okay if you're coming from here brought my traffic to this endpoint and there's even ways to create rds proxies where you could actually have it go to specific database engines if you wanted to as well so you could have traffic going to three different places keeping everything updated at the same time might be a little bit challenging kind of car large databases but hypothetically it's possible and i have my email here send an email happy to talk and think through it with you that help answer okay sounds good awesome so let's talk about some key takeaways here so when you're considering moving your databases to amazon rds some of the same limitations live whether you're on-prem or inside aws so for example you can't have a backup file that is newer than the database engine that you're migrating to so for example for say older than the basic newer or so for example if you have uh sql server you know 2012 for example and you actually want to take that backup file and move it to aws you can take that 2012 backup and move it to 2008 for example it needs to be that database engine or newer so definitely keep that in mind and for many customers this is a great opportunity if we have a database that's maybe not supported inside of amazon rds to actually migrate that to a newer database engine as well and take advantage of patching and the benefits that you get from having a newer database engine authentication is a big piece of it too so when you're talking about how do i authorize access to my applications with amazon rds you actually can use either i am or local database authentication many dbas like davis communication because they're used to it but you can use aws i am as well in addition to that the amount of data is really going to be pivotal to how you can achieve success here so you know working with my customer nyu who presented yesterday we did it in phases we planned everything out we moved into waves we moved non-critical um keyway non-product databases first and we did a table by table we said okay these applications look at these look these tables let's move the tables first and the reason we did that is because we didn't want to break any dependencies that other applications have and the key to our success there it took us about two to three years to get there was that we did a very intensive discovery using aws tools and we had to map out all those dependencies between those applications so we knew if we moved this application we're not breaking something else so that's definitely very key understanding the amount of data and also the interdependencies between that data connecting with aws the core like i mentioned so if you want the fastest most reliable connection update of us direct connect is definitely going to be your answer is it required no you use vpn use other methods as well but that's going to give you the best bandwidth the best speeds up to aws also you have to decide you don't want to re-host your platform refactor you know a big part of that could be retired too like we have this database that's aging you know we don't really need to keep it we can move these tables to another database and you have flexibility in making those choices and retiring existing hardware to fully maximize the amount of cpu and memory you're utilizing because that's the key right you don't want to over provision your resources that's not what the cloud is about you want to have the right amount of cpu the right amount of memory to meet the demand of your workload right then the migration method so we talked about three today is the three main methods that we see moving sql databases so just to recap that's going to be the native sql server backup restore that's going to be the ongoing sql replication or aws dms those are the options and of course if you feel like you need help with any of this we have solutions architects are here to answer questions like myself happy to spend some time with you all also we have professional services and partners that are available to help you achieve your goals highly recommend doing the wall architecture framework on your applications so we have compiled some best practices based on our based off our experience working with customers we call that the one architecture framework this is a self-service tool that you do inside the aws console if you have questions for your channel happy to answer some questions around that and then optimization that's a continued journey for you it's not like once you move to aws it's all said and done it's going to be great there's still going to be ongoing work optimizing your workloads making sure you have the right memory where cpu making sure that you're right sizing that's a big part of it and even as we release new instance types and features you start thinking about okay how can i use this feature to actually make my job easier or give a better experience to my end users yes so one of the scenarios this scenario with one of the scenarios could be a hybrid like the there's a he's asking if it's possible to do a hybrid of two solutions specifically the native backup and restore option and using the database migration service right yeah awesome so yes that is possible what you would do you would take that initial full load from that bak file and actually value that right like i mentioned you get those thought procedures you get those equal scripts right from that dot b file so if you want that you can definitely go with that path first keep in mind that that bk file is probably including everything unless inside of your query on the back up you're excluding certain things that'll have everything in there and then you can do incrementals using dms that's entirely possible for sure have you had any customers do that if you can recall no yeah i might work with so just to give you some background nyu and why nyu had close to 1500 servers they closed the data center moved most of it up to aws and um with them we used a combination of cloud endure and dms so cloud endura was a lot of lifting shift and dms which a lot of the database work we even had some oracle stuff so for that we use golden gate and the like so um it was a mixture of tools but no we didn't cross-pollinate that way they did use the mask yeah they still use it so dms is also a great tool for refreshing data across aws accounts you could definitely do that as well too if you have a question raise your hand i'll bring the mic to you so you can so that database dms instance will actually go ahead and monitor the source database and any changes it can replicate that data in pretty much near real time the limiting factor is going to be the size of the instance how much memory it has to capture that data and also the network speed so it can replicate data depend how fast it's really available you know all those factors yeah yeah if you need real-time replication it's definitely i wouldn't recommend it because of all those stacking i already mentioned oh yeah and um views indexes right can you use the schema convolution tool for that ongoing schema changes you can use the sct the schema conversion tool for ongoing changes it isn't set up in the same way that dms is where you have a migration test that's just monitoring sitting there you have to do those executions you know periodically so you could schedule it right ews were very big on apis and automating things you could have a lambda function or some other automation that's triggering those sct jobs to actually go ahead and update those schemas if you chose to do that yes and it can be done between like an rbs instance to on-premise so yeah the other way around sct doesn't really care about the destination so if you want to go back and forth you could with sct as well two questions actually so the first one is if there's no connectivity between our data center in amazon is the only way to do it the full back up and then maybe try to get the incremental that helped through s3 to minimize downtime you don't know like we did as a test we did a snowball but that takes a week to get the data from the data center into the bucket yeah so if you want to minimize the downtime like for in production how would you recommend yeah the best thing you could do is one mentions you have to schedule some downtime unfortunately there's going to be some time in between where not all the transactions are going to be up to date where you take the source database make that a read only start pointing people to amazon rds in between that time where you're getting that data migrated so if you're able to plan and schedule that's gonna be your best bet but there is really no you know real-time way to do that with zero downtime using the native sql backup restore you can do a combination of sql backup and tms as long as there's network connectivity internet connectivity or some other way of connecting like you do a quick vpn connection just to get that last transactional log and last incremental up to aws and that will help you minimize that time significantly so do a full load using s3 snowball whatever you got to do but then schedule that incremental as quickly as possible and you could use tms to do that as well and then the second question is so amazon does all the patching right with rps how often like how long do you have to actually implement the patches because sometimes the development teams need time to say oh i want to test out the latest patch like what type of time frame does amazon give you before you have to implement the patch or something yeah great question so with amazon rds you do specify maintenance plan bills and these are typically weekly and during that maintenance windows any patches they need to apply to apply those patches um you can defer maintenance if you don't do that however if you get to a point where you're so far behind you'll get notifications out of your account that says you have to do this and you can't defer so you will be forced to do it at some point in time if it's a fully managed database we want to support you know our customers the best of our ability but we want to make sure you're safe doing it as well so we just want to make sure that you know you're aware of that fact that you can defer but so did you did you mention how large your backup file could be what's the maximum time if with s3 using the backup restore option we're talking about five terabytes oh five five terabytes but you could do multi-file backups in that previous qr code i have a link that talks specifically about how do you do multi-file backup and multi-file backup restores there was a limited texas we talked about 16 terabytes does that really know yes yes 16 terabyte is the largest microsoft sql amazon rds database you can have if you have a database that's larger than 16 terabytes ec2 can support 64. so ec2 we're providing you with a virtual machine inside of the aws infrastructure so you're correct it's not rds so you as the customer will still be responsible for the operating system so patches and maintaining your database at the operating system easy to can be 64. and the rds is 16. so that's yeah if your game is larger than 16 terabytes five each yeah you do multiple five yeah thank you you're welcome great so with that we have a few more minutes so please if you have some time i would appreciate if you give me any feedback but one more question oh sorry all right it's more of a public service announcement we finished this process about nine months ago but what you really need to do is start paying attention to how much you're using when you're coming from a traditional data center you put memory in you put cpus in and it's painful but because this is a paper service you need to write some governance around what you're doing so that you go because keep seeing the peak five six seven eight ten point different versions of their software and writing some governance around that to say hey you're only gonna hold this much and you control your costs absolutely that's a great point once you get into the aws absolutely that's a great point so one anecdotal to all mention we have something called the ec2 instant scheduler but it applies to rds2 so what we did with nyu they have a qa prod and development environment so with the qa and dev environments we actually schedule those databases automatically get shut down when they're not using them and that significantly helped them reduce their cost and even with those non-production databases they were doing you know apples for apple's builds so for example let's say the fraud database had provision iops for storage you don't need that really for qa and then that's really expensive to do provision iops so we had to make some of those design choices as we went and you know there were lessons learned on both sides of the i'll say but we're happy to work with our customers and help them find those optimizations me as an architect my customers goals are my priorities so if they tell me hey i need to reduce spend somehow i'm helping find ways to do that so if you have an account team highly recommend you engage them they'll do the same thing for you as all right everyone well hopefully uh you found this session informative there's about four minutes left i'll let you break out early i'll stick around if these folks have questions and thank you for attending welcome you too

Channel: AWS Events

Views: 242

Keywords: AWS, Events, Webinars, Amazon Web Services, AWS Cloud, Amazon Cloud, AWS re:Invent, AWS Summit, AWS re:Inforce, AWSome Day Online, aws tutorial, aws demo, aws webinar, Public Sector, AI, ML

Id: HbVcNndrVkY

Length: 46min 37sec (2797 seconds)

Published: Mon Nov 01 2021