Last time I mentioned that I would take a look into some of the major cloud providers' database as a service offerings in order to show how each one works and how it is setup. With that said, I decided to start with the offering from Microsoft's Azure cloud. Below I will take you through creating a database and then how to connect to it.
Creating an Azure SQL Database
I figured since the entire process takes less than 5 minutes to do I would just create a short video on how this works. You can check it out here.
Since I could not add audio at the time I recorded it, I will describe what happens in the video.
- Basically we just log into the Azure Portal, so the biggest prereq is that you have an Azure account, if you do not it takes just a couple minutes to get going.
- Once logged in we click on the SQL Databases tab and click Add at the bottom of the page.
- Once we do that we get some options for creating our database, I picked Custom Create because I wanted to show you the most options.
- Next we name our database as well as pick how big we think we want it to be, don't worry too much here... we can scale this up or down later if we need to. Lastly I pick new server to put this database on, if you wanted to add this database to an existing server you can do that too.
- Next enter login credentials for our database server and pick where we want the new server to be created geographically. We can also pick whether we want all Azure VM's to be able to connect be default, as well as if we want to use the latest version of SQL.
- When we click finish the database server is created and then the database, this takes about a minute, but then you will see a status of "Online" on the database.
After this process we are ready to connect to our new database!
Connecting to your Azure SQL Database
OK now that we have a database in the cloud, we need to connect to it. I will use an Azure virtual machine, but anything from anywhere can connect to an Azure DB provided you allow it on the security settings.
Things we will need for this step include:
- Username you created before
- Password you created before
- Server Name (and FQDN)
- A server using ODBC (or something else compatible) to connect to the Azure SQL server
OK let's get started. I am using an Azure VM for this demo, but there is no reason why you couldn't connect from anywhere in the world. Just make sure to see my TIP section toward the end of the article for details on security settings you will need to change.
After logging in to your server you will need to prepare your server just like any other SQL server... So go install the SQL Native Client so that we can connect from the ODBC Data Source Administrator.
Once SQL Native Client is installed click on Add and select it as the new data source.
Now before I go any farther in the wizard on our client let me show you where to get the server name for your database.
In the Azure portal click on SQL Databases and then take a look at the list of databases. In this example we want to connect to the thwack database. So if we look in the server column we see the servers hostname. That name will be used to finish out our FQDN, which is simply <servername>.database.windows.net. You can also click on the database to see more detailed information and the FQDN is listed on the right hand side too.
OK back to the Client wizard.
How that we know the FQDN we can enter a connection name and the server name into the wizard, then click next.
Next we enter the login credentials that we created when we created the database and database server. If you don't remember then already you can use the Azure portal to reset the password and determine the login name.
SNAG!!!! Yes I really did get this message. But if you actually read it, which I don't typically do (who needs a manual right!), you will see that it gives you a big hint... use username@servername instead of just username. Presumably this is because I am using an older SQL native client.
So I added in @servername to the Login ID field and presto! I was able to login.
Just like any other SQL server you can then also select a database to make the default. I like to use th drop down to verify that the ODBC client can actually see the databases on the server. Here we can select our thwack database so we know its working now!
There you go! all finished with a new cloud hosted database, connected to a cloud server... ready to be used.
Tip: Connecting from outside of Azure
One thing I didn't show you so far was the Security section of the Azure DB settings. Because I used an Azure VM I did not need to modify any of the security settings because Azure DB understands that the traffic was coming from a (fairly) trusted source. Now in order to connect from a server at your office or something similar you will need to login to Azure Control Panel, navigate to your database server that contains the database, and then click on "Configure". Then on the Configure page you will see a list of IP addresses, or at least a box where you can put some in, this is where you will enter the Internet IP of your server. Once you do, make sure to click Save at the bottom of the page, and now you should be able to connect from that IP address.
Backup/Restore and Replication
So this part is always important no matter where your database lives. Azure DBaaS makes this really simple, in fact your backups are done automatically and are completed on a schedule based on your database type, so make sure to check that schedule and if it doesn't fit your needs you may need to scale the database up in order to get more backups. To restore you simply go to the database in question and at the bottom of the portal there is a restore button.
Once you click that button you will see a restore settings box.
In this box you can specify a database name for the restore to go to as well as the restore point... it has a DVR like slider... my database just hasn't been online very long so i don't have a long history.
At the bottom you can monitor progress on the restore. For my blank database it took about 5 minutes for it to restore.
Once it is restore it will appear in the database list ready to be connected to.
On the replication side things are a little big more involved, but still not too bad.
From the database dashboard we click on Geo-Replication. Then at the bottom we can select Add Secondary.
Click the Add Secondary button to get started.
All we need to do is configure the type of replication by selecting the secondary type (read only or non readable), as well as where we want it replication to geographically, and finally the target SQL server... I had already created a SQL server in the West Region, but if you haven't just select New Server and it will run you through the SQL server wizard just like we did earlier.
Confirm that they can bill you more
And there you go! SQL replication in place with a pretty darn respectable RPO! Check here for details.
So what did you think?
Well that wasn't too bad was it? I have to admit that I've used Azure's DBaaS before, so it was familiar to me and getting another DB setup for this post was super quick... except for the ODBC error I encountered ... but the error was descriptive enough that it didn't take too long to fix the issue.
So making it that easy to get a database is a great thing but also possibly a bad thing! This is exactly the kind of thing that causes shadow IT, but you can prevent this and still provide the same easy and speed if you have the Windows Azure Pack implemented.
So here are the homework questions:
How long does it take to get a database at your IT shop?
Have you ever found someone leveraging Azure DBaaS in a shadow IT scenario due to ease of use?
Are you using the Microsoft Azure Pack to build an internal DBaaS offering?