This is a fact you can’t deny: the Cloud is here to stay. The features are getting better and better, reliability as well, while the prices are getting lower every couple of months (thanks to the competition between major players like Amazon and Microsoft).
If you don’t want to be left apart, you better start digging into it right now by testing how you can benefit from it easily. Because of my back ground, over the many features available, I have decided to start with the database to break the ice.
This will be a 2-parts article. This month, I will show you how to use Microsoft Azure SQL Databases. Next month, I will demonstrate how to use Microsoft SQL Server Data Files in Azure.
What are Azure SQL Databases?
This feature of Azure let you host a database that can be used by your applications (web or not) without having to install a SQL server in your enterprise.
This feature is the perfect companion to other Azure services like Web Sites.
The demo application here is a plain-old Windows Form application which will use this database and it will work. Does that mean that it is the perfect solution to all application? Not really. Don’t forget that the Internet add some latency to your data transfer.
Downloadable demo
This is not a huge demo application. It is mainly a set of connection strings showing how to connect to sample database (local, Azure database, and Data Files in Azure).
The sample is provided in both VB and C#. It was created using Visual Studio 2013 but surely can be used with earlier versions.
The very same application will be used for the 2 parts of the article.
What you need
To test Microsoft Azure services, you shouldn’t be surprised if I tell you that you need an Azure account. If you have a MSDN account, you can use it to get a limited (but not so limited – enough to do plenty of testing). If you don’t have one, you can get a free trial from http://azure.microsoft.com/en-us/pricing/free-trial/.
You won’t need to install the Azure SDK toolkit as we will only use ADO.Net to access the data. Visit http://azure.microsoft.com/en-us/downloads/ to download the SDK if you want to dig into more features.
If you want to test the local database version, you will also need Microsoft SQL Server. If you want to test the “copy to Azure” and/or the Data Files in Azure, you will need Microsoft SQL Server 2014 installed.
Creating a local database
I will start with a local database created by the following script that we will later copy to the web.
USE [master]
GO
--Drop the database if it is existing
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Test')
DROP DATABASE [Test]
--Create the new database
CREATE DATABASE [Test]
GO
--Use this new database
USE Test
GO
--Create a test table
CREATE TABLE [dbo].[Clients](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[LastName] [varchar](50) NULL,
[FirstName] [varchar](50) NULL
)
GO
--insert data into the new table
INSERT INTO dbo.Clients ( LastName, FirstName )
VALUES
( 'Dalton', 'Joe' )
, ( 'Dalton', 'Jack' )
, ( 'Dalton', 'William' )
, ( 'Dalton', 'Averell' )
--Check to see if the data was properly inserted
SELECT * FROM dbo.Clients
Nothing is harmful in this script unless you already have a database named Test.
If you have access to a Microsoft SQL Server instance, run this script to create your test database.
Building the test application
Everybody knows that I am a developer. I always need an application to test the features. So we will create one.it will be a very simple one just to set the base and to show you how to connect to the source you want. It should be enough for you to understand how easy it is to start with and decide if you continue exploring.
On a Windows Form, I have added a button (btnGetDataLocal), 2 labels (lblRecordCount and lblTimeElapsed), and a DataGridView (dataGridView1). It should look like this:
Figure 1: The building of the demo application
With these controls in place, we are ready to write some code. The downloadable demo contains 2 more buttons to test the 2 other options.
Because only the connection string is changing between my 3 ways databases, I have built the code in order to not repeat myself with a centralize method called GetData which in turn call the BuildConnectionString and LoadDataset before showing data on the screen:
Private Enum enuSource
Local = 0
AzureDB
StorageDataFiles
End Enum
Private Sub btnGetDataLocal_Click(sender As Object, e As EventArgs) Handles btnGetDataLocal.Click
GetData(enuSource.Local)
End Sub
Private Sub GetData(ByVal pSource As enuSource)
Dim stpWatchInfo As New Stopwatch
stpWatchInfo.Start()
'reset the grid content
dataGridView1.DataSource = Nothing
lblRecordCount.Text = String.Empty
lblTimeElapsed.Text = String.Empty
'build the connection string
Dim strConnString As String = BuildConnectionString(pSource)
'load the data
Dim dt As DataTable = LoadDataSet(strConnString)
dataGridView1.DataSource = dt
lblRecordCount.Text = dt.Rows.Count.ToString("#,##0")
lblTimeElapsed.Text = stpWatchInfo.Elapsed.Seconds & "s"
End Sub
Private Function LoadDataSet(ByVal pConnectionString As String) As DataTable
Using cn As SqlConnection = New SqlConnection(pConnectionString)
Dim adapter As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Clients", cn)
Dim ds As DataSet = New DataSet()
adapter.Fill(ds, "Clients")
Return ds.Tables(0)
End Using
End Function
Private Function BuildConnectionString(ByVal pSource As enuSource) As String
Select Case pSource
Case enuSource.Local
Return "Data Source=(local);Integrated Security=SSPI;Initial Catalog=Test;"
Case enuSource.AzureDB
'Done later
Case enuSource.StorageDataFiles
'Done later
Case Else
Return String.Empty
End Select
End Function
If you have the database properly set and the code pasted correctly, you should see this result on your screen:
Figure 2: Running the demo application returning local data
The easy part is now done. You shouldn’t have learn too much yet. Let’s start digging Azure now.
Create a server in your Azure account
The first test we will do with Azure is to copy our local database to Azure and then modify our demo application to use it.
To be able to replicate the steps described here, you need Microsoft SQL Server 2014. You could also create a database directly on Azure and use it using exactly the same code as shown here.
Open the Microsoft Azure portal and open the SQL Databases section so that you get a screen similar to Figure 3. Click Servers at the top and click the ADD button from the bottom.
Figure 3: The server page in Azure
By clicking the ADD button, another dialog (shown in figure 4) ask you to enter some values. Fill them choose a region near to where you live.
Figure 4: Adding a SQL Server
After you filled the values, click the check mark from the bottom right of the dialog. The dialog will close and you will return to the page shown in figure 3. After a few seconds, your new server will automatically receive a name that you can find in the left column. Copy this name as we will need it later.
Copy the local database to an Azure SQL Database
Now that we have a server correctly set, we can copy the local database to this Azure server.
First, from SSMS 2014, right-click the Test database, select Tasks, and finally click Deploy Database to Windows Azure SQL Database as shown in figure 5.
Figure 5: Copy database to Azure
Once the wizard is starting, you will get an introduction screen on which you can click the Next button.
You will then be navigated to the Deployment Settings dialog. First, click the Connect button and enter your credentials as shown in figure 6. For the server name, enter the name that was generated for you when you created the server in the previous step followed by “.database.windows.net”. The login needs to be set to the login name you entered when you created the server followed by @ and the name of your server.
Figure 6: Connecting to the Azure server
Now click the Connect button and you will get the error shown in figure 7.
Figure 7: not everybody can connect to your Azure database server
I did on purpose. I wanted to show you that not everybody will be allowed to your database even if it is in the Cloud. You need to allow them the access. Click OK on the error message but keep your “Connect to Server” dialog open.
To configure the access, go back to the Microsoft Azure portal where you created your server (SQL Databases section, Servers) and click on your newly created server. Click the Configure link (top of the screen) and the figure 8 dialog will be shown.
Figure 8: allowing connection to the Azure server
If you look at the value of the “Current Client IP Address”, it matches the IP address of the error I just got. It make sense since I do everything from my laptop. In this case, you just have to click the “add to the allowed IP addresses” link and you will be set. If you need to trust a different IP address, just use the 3 textboxes to enter the required values.
One very important thing at this point is to click the SAVE button that appeared at the bottom of the screen after values have been added to the list of allowed IP addresses.
You can now go back to your “Connect to Server” dialog and click the Connect button. This time it will work!
Back to the Deployment Settings dialog, the “New database name” should already be filled with the name of the database you right-click initially. You are then asked to confirm the edition (web or business) and the size of database to create. Be warned that the decision taken here will change the amount you are billed. For this demo, I chose Web 1GB. Finally, a bacpac file will be created for the transfer from your local database to the server. You can change the location and name of that file (but I personally never change it). Click the Next button.
The dialog now shows the summary (figure 9). Review your settings and when you are ready, click the Finish button.
Figure 9: Summary
At this point, the Results dialog (figure 10) is shown and the database is slowly transferred to Azure.
Figure 10: The results dialog
If everything went well, you will see all green steps as shown here because transferring my 3 columns table was trivial.
Because Azure SQL Databases are not on par with the full SQL Server (but it’s getting closer each month), you might have error transferring your own databases. If you get an error, you see the word Error (instead of Success) on the Results dialog. By clicking this link, you will find out why you have this error and you will need to figure how to fix it.
Using your SQL Databases from SSMS 2014
Now that you have a database in the cloud, you can use your Microsoft SQL Server Management Studio 2014 (SSMS) to connect to it.
From SSMS, click the Connect button and select “Database Engine”. This will show you exactly the same dialog as shown on Figure 6 which you will need to feed with the same values. Once connected, you will find that you can do much the same thing as with a local database but not everything (as shown in figure 11).
Figure 11: Can you find missing features?
Modifying the application
Now back to my application.
I have added a button to my form which shows like this:
Private Sub btnGetDataAzureDB_Click(sender As Object, e As EventArgs) Handles btnGetDataAzureDB.Click
GetData(enuSource.AzureDB)
End Sub
Because everything else is the same whatever the location of the database, the only thing we are required to change is to provide the connection string (in the BuildConnectionString method) in which you will recognize the same credentials you already used:
Return "Server=tcp:ltgftgkor2.database.windows.net,1433;Database=Test;User ID=loginname@ltgftgkor2;Password=password1!;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"
You can now try the application and push the new button to get the data from the cloud database (instead of the local one).
Conclusion
Better get on the Azure train today. Starting with an area you already know is easy. Databases are normally a good place to start.
This month, I have demonstrated how to copy a local database to an Azure SQL Database and use it from an application.
Stay tune for next month article as it will show another way of having the data files of a database in Azure.