Learn how to configure your SQL database for redundancy and long term backup.
- [Instructor] Now that we've gone ahead and created our SQL database. I am back in the ImplementAzureSQLDatabases resource group and you will notice that we have our SQL database and our SQL server now available to us. Now let's go ahead and look at some of the configuration options for our SQL database. I'm going to go ahead and click on our SQL database. And the first thing I want to configure is Geo-Replication. And what this allows us to do is to have a secondary copy in another location.
Currently my database is being hosted in the East US 2, as your data center. But I'd like to have a secondary copy in case this center goes down. Highlighted in the purple will be the natural corresponding data center to the East US. I'm going to go ahead and select that, but I could go ahead and choose any of the other ones that I wanted to. If I scroll down, you'll actually see the list of the target regions and the Central US is the recommended one.
I'm going to go ahead and select that. I'm going to scroll over a little bit so we can see the screen a little bit easier. Our database name will be the same name that we created the database with. Next we select our pricing tier. Only pricing tiers that we can scale to will be available to us. I'm going to go ahead and us the S1 again. And now we must choose between readable and non-readable. The readable database is read only and supports up to four readable secondaries in the region.
If for some reason the primary database becomes unavailable, the secondary database then becomes your new primary. You can also use this feature to off load some workloads. For example, reporting jobs. As of today, in early January 2017, non-readable is still available to us, but as you can see the note above, this will be retired in April of 2017 and any databases that you currently have set as non-readable will be converted to readable databases.
I'm going to go ahead and take the readable. Next I'm going to go ahead and create a target server. Again, this must be a unique name. And you'll notice that I use the same name that I used when I setup my initial database server. Again, this is not unique because I've already used it. In this case, I'm going to add an extra letter to it. Keeping my fingers crossed that that is available and it is. I know it'll be challenging to create a unique server name that is also descriptive.
Next go ahead and enter your username and password. My location is already in the Central US, I can't change that and I'm going to go ahead and use the latest server available to me. Click Select. Because I hadn't setup an Elastic database pool when I created the databases, I'm unable to do so here. Click OK. This will take a few moments for your database to be replicated to the other region. We can go ahead and continue on though while that replication is occurring.
The next thing I would like to configure is long term storage. As you may recall, in the standard and premium tier, the backup is already configured for 3 days. In some cases, this may not be a long enough retention period, and we can go ahead and configure retention period for up to 10 years. I'm going to go ahead and close this blade. You'll notice here that I have a recovery vault called sqlbackups. I'm going to use this vault to configure our long term backups.
I have already created this vault and I've already set it up for some other databases. You may want to reference chapter five for details on creating recovery vaults. Next, what we need to do is actually select the database server, not the database. I know it's somewhat counter intuitive. I'm going to go ahead and select our SQL Demo Database SB. I'm going to go ahead and select the database, I know it is somewhat counter intuitive, and then click on Long-term retention.
This feature is still in preview, therefore you may be prompted to accept the terms. If that is the case, you'll have a bar very similar to mine and you'll click here, click the box and say yes I accept the terms and you can go ahead and use the service. Next I'm going to go ahead and select the database itself. Now I had done that in the previous step, you do not have to do it in the previous step, I just do it out of habit more than anything else. I've selected the database, click configure up at the top.
And now I'm going to go ahead and configure the required settings for that recovery services vault. Again, you have to create that vault prior to setting this up. Two vaults are displayed, but I can only leverage one. I'm going to go ahead and select the SQL backups and now I can go ahead and either use an existing policy that I've already created. I'm going to go ahead and create a new retention policy. I'm going to go ahead and create it new so you can see the process. I'm going to call this 10 years.
And I'm going to select 10 years to retain this backup, but I could take it down to weeks or months. Then click OK and don't forget to click Save again, and yes I want to apply this to all the selected databases. In our case we only have the one. That's it, your database is now protected for years to come. Let's go ahead and check on our replication. I'm going to go ahead and close this blade. I'm going to click back into the database itself and click back into Geo-Replication.
Now scroll over a little bit so we can see it and now you can see that our secondary readable database has been configured in the Central US data center. Using Geo-Replication, and long-term backups helps ensure that your database is always available to you, just in case the unthinkable does happen.
- Implementing storage blobs and Azure files
- Managing access
- Configuring diagnostics, monitoring, and analytics
- Enabling and viewing logs
- Implementing Azure SQL databases
- Implementing recovery services
- Creating an Azure Backup vault
- Configuring the Azure Backup agent
- Backing up and restoring files
- Backing up an Azure virtual machine