Book Image

Getting Started with SQL Server 2014 Administration

By : Gethyn Ellis
Book Image

Getting Started with SQL Server 2014 Administration

By: Gethyn Ellis

Overview of this book

Table of Contents (13 chapters)
Getting Started with SQL Server 2014 Administration
Credits
About the Author
About the Reviewers
www.PacktPub.com
Preface
Index

Windows Azure SQL Database


Microsoft has both an SaaS and an IaaS offerings, and we will look at each of these offerings in the following paragraphs.

SaaS is a centrally managed and hosted piece of software that you pay for like a utility bill. Usually, the data used or consumed is used to calculate how much you pay. The more you use, the more you pay. Windows Azure SQL Database (formerly known as SQL Azure) is an example of SaaS.

IaaS is a service offered by a cloud provider whereby the hardware is hosted by the provider and you pay for what you use, much like the utility bill analogy used previously. The physical infrastructure is managed by the cloud solution provider, but you do have some control over how the virtual machine running your software is set up. You don't need to worry about managing and maintaining the physical hardware, and IaaS can make it a simple process to scale out your environment. Microsoft Azure also allows you to host your own virtual machine, with the Windows Server operating system and SQL Server 2014 installed. Amazon offered IaaS, providing virtual machines with SQL Server installed for a long time. Microsoft has recently followed suit with Microsoft Azure Virtual Machines. You can host Active Directory services in the cloud too. This means your off-premise Microsoft Azure Virtual Machines can be integrated nicely with your one-premise physical or virtual machines.

Creating a Windows Azure SQL database

To create a Microsoft Azure database, we need a Microsoft Azure account, which you can get as a free trial if you want to experiment with Microsoft Azure. However, you will need to enter some credit card information, even if you only want the free trial. To create your account, visit http://azure.microsoft.com/en-us/ and follow these steps:

  1. When you have created your Microsoft Azure account, you will need to log in to the management portal. You will come across a screen that looks similar to the following screenshot:

  2. Click on the SQL DATABASES option from the menu on the left-hand side. If you don't have any SQL databases created, you will have a screen that looks similar to the following screenshot:

  3. Click on the New button to start the Create Database wizard. You need to make some choices from the screen shown in the following screenshot:

  4. In this wizard, the database helpdesk has been called upon. The edition of the Windows Azure SQL Database defines what features are available and how much it costs. For the purposes of this example, a web edition database has been chosen. With the web edition of Windows Azure SQL database, you can have a database storage limit of up to 5 GB. If you chose the business edition, you can have a database storage limit of up to 150 GB. The next step is to choose the collation for your database, which in this case has been set to the default setting, as well as the subscription type that you wish to use to pay for your SaaS database. As a SQL database for Microsoft Azure is yet to be chosen, you need to select the New SQL database Server option. You then need to click on the next arrow button.

  5. On the next screen, you will need to enter some server settings and then create a login name; in this case, I entered Gethyn and a safe and secure password. I chose my region as Western Europe.

  6. Click on the check box to create the Microsoft Azure database. Once the database has been created successfully, you can view it under the SQL DATABASES section in the Microsoft Azure management portal.

Connecting to a Windows Azure SQL database with Management Studio

Now that a Windows Azure SQL database has been created, the next step is to connect the database in order to begin the design process, which involves creating tables to store your important business data. SQL Server 2014 comes with Management Studio, which is a robust tool for both administrators and developers to code and administer SQL Servers and SQL Server databases. We can use this tool to work with our SQL Azure Database.

SQL Server 2014 Management Studio has been installed in this case; you can install Management Studio from the SQL Server installation media if it is not installed already. Installing SQL Server Management Studio is outside the scope of this book. The management portal will provide us with a Microsoft Azure server name, and you can connect it to the server using the username and password created in the earlier steps.

Before you connect, you will need to set up firewall rules to allow your laptop to connect. Click on the Set up Windows Azure firewall rules for this IP address link.

The firewall rule can take a few minutes to take effect. Fire up SQL Server Management Studio 2014 and connect it to the Windows Azure SQL Database. You will need to specify the server name given to you when you created your Windows SQL Azure Database and also the login name and password. Once you click on Connect, you will then be connected to your Windows SQL Azure Database. You will see that Object Explorer looks different in comparison to a local SQL Server instance connection. You have three folders available: Databases, Security, and Management. This is all you need.

Migrating an existing database to Microsoft Azure

If you have an existing database on a local instance of SQL Server, there is a wizard you can use to help migrate a database to a Windows Azure SQL Database. In my case, I have a database called T3 that currently sits on my local SQL Server 2014 instance. You can then use the management studio wizard to move the database to Microsoft Azure.

You need to connect to the local SQL Server 2014 instance in Management Studio. To start the wizard, right-click on the database to move. Select the Tasks option and click on Deploy to Windows Azure SQL Database to start the wizard. The first screen of the wizard explains what the wizard will implement; you can safely click on Next on this screen.

You will then specify the Windows Azure SQL Database server name; for this example, I will use the credentials I created earlier as shown in the following screenshot:

You will need the Microsoft data-tier application framework installed for your SQL Server 2014 wizard to successfully deploy your database to a Windows Azure SQL Database. A free tool to do this can be downloaded from the Microsoft website at http://www.microsoft.com/en-gb/download/details.aspx?id=40735.

When you have successfully deployed your on-premise database to a Windows Azure SQL Database, you will get a confirmation screen that reports whether the operation has been successful or not. You will be able to view the newly deployed SaaS database by connecting to the cloud-based SQL database in Management Studio.