{"id":1479,"date":"2012-04-26T00:00:00","date_gmt":"2012-04-26T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/how-to-set-up-a-database-driven-azure-site\/"},"modified":"2021-02-23T19:43:28","modified_gmt":"2021-02-23T19:43:28","slug":"how-to-set-up-a-database-driven-azure-site","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/how-to-set-up-a-database-driven-azure-site\/","title":{"rendered":"How to set up a database-driven Azure site"},"content":{"rendered":"
\n

This article is really a review of the alternative ways to get started with setting up a database-driven Azure site. I’ll start by using SQL Azure Management Portal and SQL Server Management Studio. Then, I’ll use Visual Studio to create a SQL Azure database (Web edition, 1 GB). I’m going to add a single Customer table and I’m going to see if I can deploy a LightSwitch application using that database.<\/p>\n

SQL Azure is NOT SQL Server<\/h2>\n

Yes, SQL Azure is NOT SQL Server. I’ve wanted to say that for weeks – now I’ve said it. I keep hearing from so many sources that SQL Azure is SQL Server in the Cloud and to a degree that is correct. SQL Azure is<\/em> a relational database system with many similarities to SQL Server; after all, both are Microsoft products. Beyond that many things from the developer’s perspective take a divergent direction.<\/p>\n

Taking a broad look, it takes planning to create any application that uses a database to store information. Let’s assume that you have elected to use SQL Azure as your database system. Now you need to do two things to get started;<\/p>\n

A. Name and generate a database.<\/p>\n

B. Build tables for that database.<\/p>\n

For me, the choice with SQL Server has always been simple; use SQL Server Management Studio. This is largely because it has some useful built-in wizards. I’ve never been put in the position of having to use scripts to do these things but with SQL Azure and SSMS you are stuck with scripts.<\/p>\n

There is a way of avoiding the use of scripts, however, since SQL Azure offers a management tool of its own much like SSMS, and Visual Studio 11 has some terrific tools available. We’re therefore going to explore these options for creating a database and the table using the tools that are currently available. I think you’ll be pleasantly surprised (or at least surprised) so sit back and enjoy the ride.<\/p>\n

Creating a database<\/h2>\n

Many things about SQL Azure are covered ad nauseam in dozens of places. For my initial foray here I want to talk about two things.<\/p>\n

A. Creating a database with the tools we all know and<\/p>\n

B. Creating tables in the new database.<\/p>\n

The tools we will use are the SQL Azure Management portal, SQL Server Management Studio and Visual Studio 2010. So let’s look first at creating a database.<\/p>\n

SQL Azure Management Portal<\/h3>\n

I’m going to assume that you’re already created a SQL Azure server and taken note of its name, url, administrator name and password because you will need these. Note that you cannot log in to a SQL Azure instance with Windows Authentication. You must use a user name and password. Here’s a sample of what you might see.<\/p>\n

\"1638-az1.png\"<\/a><\/p>\n

When logging in, your user name will take the form (using the information above) of dncadmin@i46it8qgsh. Note that you cannot use admin, administrator or any of several other short, easily-remembered, names for your Administrator Login name. Actually all of the rules that Microsoft employs make perfect sense because you will certainly want your database as secure as possible.<\/p>\n

Create a database using the Portal<\/h3>\n

From the above screen, use the ribbon Database Create<\/em> button in the ribbon to create your SQL Azure database. My suggestion is that you always use the Portal to create your databases. The Create button presents the following screen:<\/p>\n

\"1638-az2.png\"<\/a><\/p>\n

SQL Azure has a concept you won’t see in an on-premise SQL. That concept is size. The size is determined from this screen. The Web Edition offers 2 sizes:<\/p>\n

\"1638-az3.png\"<\/a><\/p>\n

The business edition started out with 10, 20, 30, 40 and 50 GB sizes. That has now been increased to 7 sizes as shown here:<\/p>\n

\"1638-az4.png\"<\/a><\/p>\n

For this discussion I chose a Web Edition with a size of 1GB.<\/p>\n

\"1638-az5.png\"<\/a><\/p>\n

And now we have a new database. The point is that the SQL Azure Portal makes the creation of a database a simple selection of two options, Edition and Maximum Size, and the entry of a name.<\/p>\n

\"1638-az6.png\"<\/a><\/p>\n

You can do a number of things now such as Test Connectivity<\/em>.<\/p>\n

\"1638-az7.png\"<\/a><\/p>\n

You can also Create<\/em> another database, Drop<\/em> the database or Manage<\/em> the database. So let’s explore Manage<\/em> for a minute.<\/p>\n

Manage your database using the portal<\/h3>\n

First, you will need your Administrator Login from the properties and your password to open the Manage<\/em> operation. Note that this is a Silverlight application. Click the Manage<\/em> button in the ribbon to get the Logon screen.<\/p>\n

\"1638-az8.png\"<\/a><\/p>\n

SQL Manager fills in the Server and Database information. Note that the User name is the Administrator Logon name with @ and the unique part of the server name appended. Once you’re logged on you get a very busy screen with a bunch of selections. We’re going to explore the design part for this discussion but you can always explore on your own if you like.<\/p>\n

\"1638-az9-1024x587.png\"<\/a><\/p>\n

In the lower left you will see Overview, Administrator, and Design. For now click the Design<\/em> button to get the following screen.<\/p>\n

\"1638-az10.png\"<\/a><\/p>\n

And suddenly we’re back to something most of us are familiar with:<\/p>\n

\"1638-az11.png\"<\/a><\/p>\n

You can change the table name and begin designing your new table.  Interestingly there are 36 data types, very similar to plain old SQL Server:<\/p>\n

bigint, binary, bit, char, date, datetime, datetime2, datetimeoffset, decimal, float, geography, geometry, hierarchyid, image, int, money, nchar, ntext, numeric, nvarchar, nvarchar(max), real, smalldatetime, smallint, smallmoney, sql_variant, sysname, text, time, timestamp, tinyint,  uniqueidentifier, varbinary(max), varchar, varchar(max) and xml<\/p>\n

Obviously you’ll be familiar with many of these types but we’ll get into the meaning of all of these another time. Here’s a sample Customer table filled in. Again this is all very familiar territory with Wizards used for easing the work.<\/p>\n

\"1638-az12.png\"<\/a><\/p>\n

<\/h2>\n

SQL Server Management Studio<\/h2>\n

Now let’s explore the very different experience using SQL Server Management Studio. For this example I’m using SQL Server 2012 Management Studio just to ensure we have the latest updates.<\/p>\n

\"1638-az13.png\"<\/a><\/p>\n

Things start out quite normally except that Windows Authentication is not allowed. The Server Name is the full name from your Azure portal and the Login name follows the Administrator Login name followed by the @ symbol and the unique part of the server name. But before we go any further let’s explore what we have when logging on to a local SQL Server instance. Right clicking Databases<\/em> brings up this dialog with a plethora of options.<\/p>\n

\"1638-az14.png\"<\/a><\/p>\n

Selecting New Database<\/em>… brings up the following wizard. You set various things in a Wizard fashion and Voila! you have a new database.<\/p>\n

\"1638-az15.png\"<\/a><\/p>\n

On the other hand connecting to a SQL Azure instance using SSMS gives you the following options when right clicking Databases<\/em>:<\/p>\n

\"1638-az16.png\"<\/a><\/p>\n

So you get an extremely truncated group of options. Making things worse, if you select New Database<\/em>… you get whoops!!! A SQL Script instance:<\/p>\n

\"1638-az17.png\"<\/a><\/p>\n

But let’s back up a minute and do some additional things. When you log in to SSMS you need to add some parameters. When a SQL Azure instance is built that instance includes a MASTER database. When you log on you need to log on in the master context so click the Options<\/em> button on the log on screen. You will get the following:<\/p>\n

\"1638-az18.png\"<\/a><\/p>\n

Select the Connection Properties<\/em> tab for this:<\/p>\n

\"1638-az19.png\"<\/a><\/p>\n

On the dropdown to the right of Connect to database<\/em> select <Browse Server…>.  After a few seconds you will get this:<\/p>\n

\"1638-az20.png\"<\/a><\/p>\n

Highlight master <\/em>to get the following screen and also check Encrypt connection<\/em>. Now click the Connect <\/em>button to proceed.<\/p>\n

Creating a database using SSMS<\/h3>\n

When you are connected to the master database, you can create new databases on the server and modify or drop existing databases. The steps below describe how to accomplish several common database management tasks through SSMS. They assume that you are connected to the master database with the server-level principal login that you created when you set up your server.<\/p>\n

When you right click on databases<\/em> you get the following script:<\/p>\n

\"1638-az21.png\"<\/a><\/p>\n

You can use the CREATE DATABASE<\/code> statement to create a new database. The statement below creates a new database named mySSMSTestDB, and specifies that it is a Business Edition database with a maximum size of 20 GB.<\/p>\n

CREATE DATABASE mySSMSTestDB\n(MAXSIZE=20GB, EDITION='Business')<\/pre>\n
\"1638-az22.png\"<\/a><\/p>\n
You can then use the ALTER DATABASE<\/strong> statement to modify that database, for example if you want to change the name, maximum size, or edition (business or web) of the database. The statement below modifies the database you created in the previous step to change the maximum size to 5 GB and change the edition to web.<\/p>\n
ALTER DATABASE mySSMSTestDB\nMODIFY\n(MAXSIZE=5GB, Edition='Web')<\/pre>\n
You could finally use the DROP DATABASE<\/code> statement to delete the database. The statement below deletes the mySSMSTestDB database.<\/p>\n
DROP DATABASE mySSMSTestDB<\/pre>\n
The master database has the sys.databases view that can be used to view the details of all databases. To view all existing databases, use the following statement.<\/p>\n
SELECT * FROM sys.databases<\/pre>\n
In SQL Azure the USE statement is not supported. You need to establish a connection directly to the relevant database. Also Transact-SQL statements that create or modify databases must be run within their own batch and cannot be grouped.<\/p>\n
Aside from the fact that you create and alter databases using script – it’s not too bad but I personally still prefer the wizards. The problem is it doesn’t get better.<\/p>\n
NOTE: I did notice one oddity. Sometimes 'Web'<\/code> and 'Business'<\/code> work and sometimes they work without the quotes. Your mileage may vary but if it fails one way, try it the other.<\/p>\n
Creating tables using SSMS<\/h3>\n
If you select the new database and expand it, then right click Tables<\/em> you will get the following and I’m quitting here. For you dyed-in-the-wool scripting fans this will probably work but I lost it at this point.<\/p>\n
\"1638-az23.png\"<\/a><\/p>\n
<\/h2>\n
Creating databases using Visual Studio 11<\/h2>\n
My tool of choice when working with local SQL instances has always been SQL Server Management Studio. I generate databases and tables using that tool and then deploy them using Visual Studio. I always knew that VS provided some good database tools – I just didn’t use them. Now, I’m going to go a little crazy. I will stick to one small SQL Azure database (Web edition, 1 GB). I’m going to add a single Customer table and I’m going to see if I can deploy a LightSwitch application using that database. I am NOT going to get fancy. If this works I’ll be proud and we will both have learned something (I hope). To duplicate this you will need the Visual Studio 11 Beta.<\/p>\n
Creating the database<\/h3>\n
I’m going to connect to my SQL Azure SQL instance and create a single database that I’m calling MyLightSwitchDB<\/strong>, so let’s fire up Visual Studio 11 and get started. The interface is likely to surprise you if you haven’t used it before. It comes in two flavors, dark or light. I’m using the light interface. Gone are the pretty icons. Instead most are very austere and functional.<\/p>\n
\"1638-az24.png\"<\/a><\/p>\n
Clicking the icon that looks like an American wall-socket plug with a + sign (3rd from the left) will bring up the Add Connection dialog.<\/p>\n
\"1638-az25.png\"<\/a><\/p>\n
Once filled out just click OK<\/em>. Because the database does not exist you will get the following:<\/p>\n
\"1638-az26.png\"<\/a><\/p>\n
Again click Yes<\/em> and the system will create your database in SQL Azure. You can verify this by going to the Azure portal and checking the database section. You should see a new Web edition DB with 1GB size.<\/p>\n
In Visual Studio 11 SERVER EXPLORER if you right click the database name you can shift to SQL SERVER OBJECT EXPLORER which shows the following view.<\/p>\n
\"1638-az27.png\"<\/a><\/p>\n
At this point you can right click Tables<\/em> and elect to Add New Table<\/em>….<\/p>\n
\"1638-az28-1024x690.png\"<\/a><\/p>\n
Now you have all of the goodness of SQL Server Management Studio with the addition of on the fly script generation. Start by changing [Table] in the script view to [Customers]. Also change the primary key name to CustomerID. Truthfully, I struggled for a bit finding where to set the identity specification but it’s in the PROPERTIES windows. That window has little right pointing arrows in the bar at the left of the window. Clicking that arrow expands the choices thusly:<\/p>\n
\"1638-az29.png\"<\/a><\/p>\n
Setting (Is Identity) to True<\/strong> fills in the Increment and Seed values with suggested values of 1 which is fine here. It also changes the script being generated to an appropriate value.<\/p>\n
Now fill in the additional values and click the Update<\/em> arrow at the top of the screen.<\/p>\n
\"1638-az30.png\"<\/a><\/p>\n
You have the option of generating and executing an update script or simply updating the database. Once you’ve updated the SQL Azure database you can move on by opening a LightSwitch application.<\/p>\n
\"1638-az31.png\"<\/a><\/p>\n
Once the application is created select the Attach to External Data Source<\/em> option.<\/p>\n
\"1638-az32.png\"<\/a><\/p>\n
Highlight Database as shown and click Next<\/em>.<\/p>\n
\"1638-az33.png\"<\/a><\/p>\n
You will need to fill in the Server name and credentials. The Connect to a database dropdown will at that point offer MyLightSwitchDB as selection. On clicking OK<\/em> you will get a new dialog that will take a little time to populate.<\/p>\n
\"1638-az34.png\"<\/a><\/p>\n
Once populated, make the selections shown above and click finish<\/em>. You should see the following screen indicating that things have gone as expected.<\/p>\n
\"1638-az35-1024x678.png\"<\/a><\/p>\n
With the problems I’ve had over the last few weeks with both Azure and LightSwitch this exercise has made me feel very good about future prospects. At this point I would suggest making a Detail Screen and an Editable Grid Screen so you can explore LightSwitch connected to SQL Azure in more detail.<\/p>\n
This article has been mostly about the creation of SQL Azure databases and the generation of tables using several available tools. At this point using the Azure Management Portal probably offers the best options overall. Visual Studio 11 has made some large strides into the management of SQL Azure as we’ve shown above. My favorite tool to this point, SQL Server Management Studio,  can be used but the Wizards are not available at this point even with the SQL 2012 version. It’s possible this will change but for me it has now become a choice of the Azure portal or Visual Studio 11.<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
So how easy is it to set up a database-driven application on Azure, using the tools that one is familar with. There are several different ways of doing it, and Bud decides to find out which is the best way…<\/p>\n","protected":false},"author":29013,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[5364,5336,4168,4359],"coauthors":[48709],"class_list":["post-1479","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-azure","tag-cloud","tag-database","tag-development"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1479","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/29013"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1479"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1479\/revisions"}],"predecessor-version":[{"id":90122,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1479\/revisions\/90122"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1479"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1479"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1479"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1479"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}