What do you get with a Packt Subscription?

Free for first 7 days. €18.99 p/m after that. Cancel any time!

Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!

50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

Thousands of reference materials covering every tech concept you need to stay up to date.

Subscribe now

View plans & pricing

Getting Started with SQL Server 2014 Administration

Chapter 1. SQL Server 2014 and Cloud

Cloud is a buzzword that has been around for a long time, and it can in fact have many different meanings. Generally, it is considered as a process where remote computers are used to provide services possibly over the Internet, rather than using local computer servers or personal computers. A cloud can be public when computing resources are provided and hosted by a third party over the Internet and shared by all the paying subscribers, or private when computers and servers are hosted and owned by a company or business. A cloud can be Software as a Service (SaaS), Infrastructure as a Service (IaaS), or even Hardware as a Service (HaaS). Microsoft's strategy seems to be pushing towards a cloud infrastructure, and SQL Server 2014 has some features that make hosting your data and/or infrastructure in the cloud easy to work with, set up, and manage. In this chapter, you will explore the options that are available when working with SQL Server in the cloud—Microsoft Cloud in particular. We will also look at setting up and configuring Microsoft Azure Storage to house your SQL Server data files and creating your own Microsoft Azure SQL Server virtual machines. You can find out more information about this at http://azure.microsoft.com/en-us/services/sql-database/.

In this chapter, you will learn about creating databases in Microsoft's cloud solution. This is called Microsoft Azure SQL Database, previously known as SQL Azure.

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:

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:
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:
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:
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.
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.
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.

Integrating Microsoft Azure Storage

SQL Server 2014 offers you the ability to store your SQL Server data files in Microsoft Azure. This will allow you to combine on-premise and cloud-based storage solutions for your SQL Server 2014 databases. The files are stored as blobs in Microsoft Azure. This offers the DBA a number of benefits, some of which are listed as follows:

Fast and easy migration
Low cost storage—potentially limitless storage
High Availability (HA) and Disaster Recovery (DR)
Security

Creating a database that uses the Microsoft Azure Storage integration

In order to use Microsoft Azure Storage for your SQL Sever database files, you will need to create a Microsoft Azure Storage account, and you will need to create a storage container.

To create a database that uses Microsoft Azure Storage, you need to follow the ensuing steps:

Create a Microsoft Azure Storage account and then create a container.
Following this, create a policy on the storage container and generate a Shared Access Signature (SAS) key.
Now, create a SQL Server Credential.
Finally, you can create a database in Microsoft Azure Storage.

Creating a Microsoft Azure Storage account and container

To use Microsoft Azure Storage for your SQL Server database, an account and a blob storage container will need to be created. Because you have already created your Microsoft Azure Account, you will now need to create a storage container for the databases' data files.

From the management portal, select the STORAGE option from the left-hand menu, which is shown in the following screenshot:

Click on the New button at the bottom of the screen and run through the wizard. The most important thing to note here is that you need to disable geo-replication. When geo-replication is set, the write order is not guaranteed, and this can lead to database corruption. Geo-replication allows you to keep multiple durable copies of data across different sites, but how the technology writes data to disk does not lend itself to how SQL Server will write data to the disk. Therefore, when using Microsoft Azure Storage for your database, you will not be able to use this option.

Click on the CREATE STORAGE ACCOUNT button to create the account as shown in the following screenshot. The storage account will take a minute or two to do this.

In the management portal, click on the newly created account that will be used for the storage configuration screen. At the top of the screen, you will see a list of options that contains Dashboard, Monitor, Configure, and Containers. Click on Containers, and click on the option to create a container. Give your container a name; I called mine sqldata.

The first step is complete; we then need to create a policy followed by SAS.

The container policy and Shared Access Signature

To simplify this process, Azure Storage Explorer is going to be used, which provides a nice graphical interface. You can download the software from the CodePlex website for free at http://azurestorageexplorer.codeplex.com.

After you have downloaded the Azure Storage Explorer, the following steps will guide you in using it to create a Shared Access Signature:

Install the .msi package on your computer, open up the Azure Storage Explorer, and connect to your storage container using the credentials supplied in the management portal. You will need the storage account that you created previously as well as a storage access key. Click on the storage account and then click on the Manage Access Keys icon. This can be found at the bottom of the storage container. You will need the key to connect.
When you have successfully connected to your storage account, the Azure Storage Explorer will look similar to the following screenshot:
When successfully connected to the storage account, you will see the SQLDATA container on the left-hand side; click on the security button.
When the security dialog box opens, you will need to first create a new policy. Click on the Shared Access Policy tab.
Click on the New button and give the policy a name. To keep things simple, I called mine sqldata and gave policy permissions to read, write, list, and delete; along with this, you can also give it a start date, a start time, an end date, and an end time.
You then need to click on the Save Policies option. Following this, click on the Shared Access Signature tab, which is in the middle tab of the Security dialog box.
The Container name textbox should be completed. Leave the blob name text box blank and then choose the policy that was just created from the drop-down list. After this, click on the Generate Signature button. You will need this when setting up the credential in the next section.

Creating a credential

Connect to the local SQL Server instance in Management Studio that will be used to house the database that will make use of the Microsoft Azure Storage to execute the following CREATE CREDENTIAL statement:

CREATE CREDENTIAL [https://gresqlstorage.blob.core.windows.net/sqldata]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = 'sr=c&si=SQLDATA&sig=PtQi1NXUuJz%2BGCUkpdgEBS4o4Lo60FjTbfJ2dNx3XX8%3D'

Tip

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

The CREATE CREDENTIAL statement uses the full Uniform Resource Identifier (URI) of the storage container for the credential name, including https:// address. The identity is mandatory and needs to be set as SHARED ACCESS SIGNATURE. The secret is the SAS that we created previously but not the full URI—everything up to the first ? needs to be removed.

Creating a database using Microsoft Azure Storage

To create a database that makes use of Microsoft Azure Storage, you will have to connect to the local database instance where the database will be created using Management Studio and Object Explorer. So, open a new query window and run the following CREATE DATABASES statement:

CREATE DATABASE TestDB1  
ON 
(NAME = TestDB1_data, 
   FILENAME = 'https://gresqlstorage.blob.core.windows.net/sqldata/TestDB1Data.mdf') 
 LOG ON 
(NAME = TestDB1_log, 
    FILENAME = 'https://gresqlstorage.blob.core.windows.net/sqldata/TestDB1Log.ldf') 
GO

This command will create a database that appears to be an on-premise database housed on the SQL Server 2014 instance, but the storage of the data and log files is in fact on Microsoft Azure Storage. This is a true hybrid database that spans both on- and off-premise technologies.

Microsoft Azure Virtual Machines

In this next section, we look at Microsoft's cloud-based Infrastructure as a Service offering. It is possible to quickly create a fully blown Windows Server 2012 virtual machine with SQL Server 2014 installed. Instead of building a virtualized environment on your premises, you rent a virtual machine from Microsoft. This approach does mean that you will have to undertake more of the administration tasks when it comes to managing the guest operating system, but you don't need to buy additional hardware.

Creating a Microsoft Azure Virtual Machine

The following steps will create a Microsoft-Azure-hosted virtual machine:

First, you will need a Microsoft Azure account to be able to create a SQL Server virtual machine in it. You can usually sign up for a free trial, so it won't cost you anything to test this out. Assuming that you have the account set up and you are signed in, you will be able to create your own virtual machine.
From the Microsoft Azure Management portal, you need to click on the Virtual Machine tab on the left-hand side menu. Click on the New button. Ensure that the virtual machine option is selected and then click on the From Gallery button as shown in the following screenshot. Microsoft supplies a list of templates that you can make use of in order to quickly deploy a virtual machine of your choice.
When you have clicked on FROM GALLERY, you can choose the template that you want to use. In this case, the SQL Server option has been chosen, so choose SQL Server 2014 on Windows Server 2012 R2. Then, click on the Next button as shown in the following screenshot:
Give the virtual machine a name. In my case, I have named my virtual machine GRESQL2014. Once you have chosen a name, specify an account username and password that will allow you to connect to the new virtual machine. Click on Next to continue.
You will then set up the configuration of the virtual machine. In my case, I'm going to accept the defaults offered here, but you can change them if you wish to. After this, click on Next.
In the next screen, you get to specify which endpoint should be created for your virtual machine; that is, you get to specify how you want to connect to and work with the new virtual machine. The defaults are Remote Desktop and PowerShell. Again, in my case, I am happy with both of these, so I will accept the defaults. However, you can add additional endpoints here if you want, too.

It will take a few minutes for Microsoft Azure to provision the virtual machines for you. When the machine is set up, you can set the remote desktop to the new virtual machine from your laptop or computer. From that point onwards, it's like working on any virtual or physical machine remotely. You can start Management Studio and connect it to your new SQL Server 2014 instance. You can create and manage databases just as you would with a server hosted in your data centers.

What you will learn

Design a SQL Server infrastructure combining onpremise servers and Windows Azure Storage

Create a backup strategy that allows you to store your SQL Server backup in the Windows Azure cloud

Improve database performance by using the InMemory OLTP features

Implement the delayed durability feature to improve transaction latency

Use a cloudbased replica to build an AlwaysOn Availability Group

Learn the other enhancements and new features that can help improve database performance

What do you get with a Packt Subscription?

Free for first 7 days. €18.99 p/m after that. Cancel any time!

Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!

50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

Thousands of reference materials covering every tech concept you need to stay up to date.

Subscribe now

View plans & pricing

Frequently bought together

Getting Started with SQL Server 2014 Administration

€22.99

Microsoft SQL Server 2014 Business Intelligence Development Beginner's Guide

€41.99

€35.99

Total € 100.97

JD (DBA - South Wales) Jun 27, 2014

The attractive thing about this publication is that it contains a lot of useful and relevant information in just little over 100 pages.For many of us DBAs we're comfortable with SQL Server 2012/2008 and we are hearing a lot of noise in the SQL community about the new features and enhancements that SQL Server 2014 bundles up.For me the book demystified a few apprehensions I had over cloud technology.It guided me through the new functionality with easy to follow instruction and screen shots.Those sceptical about plunging into the depths of Windows Azure, Virtualisation, Always-On etc will find this book a very useful tool in future decision-making. For example, the prospect of housing your data and SQL environments in the "abyss" may be a daunting one - what this book does is highlight the advantages and pinpoints areas that require careful consideration, such as when to use Delayed Durability.Chapter 3 'In-Memory Optimised Tables' is an in-depth topic but the author clearly defines the differing isolation levels and index choices, making it easily understandable for the everyday DBA.The authors personal experiences of using Cloud-based and virtual technology seems to be evident and there is a plethora of useful easy-to-follow descriptions throughout the entire book.It's a compact book, and as the title suggests - its a useful reference for 'getting started' with some of the features SQL 2014 has introduced. I would recommend this to both newbie DBAs and established SQL 2012/2008 DBAs looking to gain an insight into the latest offerings.

Amazon Verified review

Ian Stirk May 29, 2014

Hi,I have written a detailed chapter-by-chapter review of this book on www DOT i-programmer DOT info, the first and last parts of this review are given here. For my review of all chapters, search i-programmer DOT info for STIRK together with the book's title.Another SQL Server 2014 book quickly follows the product's release, how does it fare?This is a small book of less than 100 pages spread over six chapters. The introduction says "This book is for people who want to learn the new features of SQL Server 2014..." this is at odds with the title which doesn't mention it relating to only new features, I wonder how many people will be caught out by this misleading title?People that want to read only about new features may ignore it, and people that want a general administration book may buy it! "Getting Started with SQL Server 2014's New Administration Features" would have been a more honest title.Below is a chapter-by-chapter exploration of the topics covered.Chapter 1 SQL Server 2014 and CloudThe chapter opens with a few definitions, Windows Azure being Microsoft's public cloud offering, and Microsoft Azure SQL Database the underlying database. It notes that using the cloud reduces concerns about hardware resources, and provides improved scalability.The database provides Software as a Service (SaaS) functionality. A step-by-step walkthrough of how to create a Windows Azure SQL database is given, together with its various settings. Connecting to the newly created database via SQL Server Management Studio (SSMS) is shown next. Finally, the process of migration an existing on-premises database to Microsoft Azure is illustrated.The next section discusses storing your SQL Server database files in Microsoft Azure, combining on-premise and cloud storage solutions. The files are stored as blobs in Microsoft Azure. Various benefits include low cost and elastic storage, together with improved High Availability (HA) and Disaster Recovery (DR). There's a step-by-step walkthrough of creating a database that uses cloud storage (including creating the relevant storage account and storage container).The chapter ends with a walkthrough of creating a Microsoft Azure Virtual Machine (VM), instead of building the VM on-premises.All the sections provide walkthroughs with good descriptions and screenshots. All the sub-chapter headings would have been easier to understand if they specified their function e.g. "Integrating Microsoft Azure SQL Database" would have been better as "Storing your SQL Server database files in the cloud"....ConclusionThis book is a short introduction, from an administrator's viewpoint, to the new features of SQL Server 2014. It's generally practical in its approach, containing step-by-step walkthroughs, together with supporting screenshots. It's generally an easy read, with good but limited explanations.The book's title is incorrect, it should reflect the book relates to new features of SQL Server 2014 only. Some people (myself included) would actually prefer a book that contains only the updated features. I can imagine some people looking for an introductory book for general SQL Server administration, getting annoyed at purchasing a book that contains specifically new features only.One of the problems with reviewing this book is the "Getting Started" part of the title, it provides an apology for the lack of depth and omissions. This is compounded by the book's small size, only 114 pages. For example, there's nothing about the new security enhancements (e.g. connect any database, impersonate any login). I wanted more! I had to keep reminding myself this is a "Getting started" book...Although the diagrams are correctly placed, it might have been useful to provide them with titles - making subsequent chapter scanning easier.I'm not sure for whom this book is intended, if you're new to SQL Server administration, you will need a more general book, not just the new features. If you're experienced with SQL Server administration, you probably need a more detailed book - perhaps this is the book you read before the detailed one (or lookup the detail on the Internet)?As a brief introduction to the new administration features of SQL Server 2014, I can recommend this book, but I suspect you'll soon need a follow-on book containing more detail.

Vaibhav Gupta Jun 21, 2014

Hi ,This book provides introduction to new & important features of SQL Server 2014 and helps you understand why your organization should use this tool over previous versions. This book is for professional DBAs who understands basics of DBA practices and now looking forward to learn new features and going to migrate there environment to upgraded version.A good read !!!Regards,Vaibhav Gupta

A. Zubarev May 06, 2014

I have been working with SQL Server starting from version 6 in late 90s. Ironically, I had never suspected I will end up using SQL Server or using it throughout the rest of my entire career, and I have no plans defecting. The short story is I joined an incubator company which later became a spin-off a large enterprise where there were almost no Microsoft products used (I initially worked on a Mainframe DB2, then on Unix as a C programmer using Informix and Oracle). At that small start up initially like company all the repeated attempts to install an Oracle database by a vendor have failed. Our CEO was enraged, the deadlines were pressing when my fellow contractor brought a set of freshly purchased MSDN CDs one morning into our office. He exchanged a few words I did not catch with our project manager and vanished in the coldness of our server room. Short time later he re-emerged with a smile on his face and a bunch of us emotionless hearing him say we have a SQL Server database setup for us to use in our belated POC. Frankly, our old-school CEO and many other managers took this optimism with a lot of skepticism. Months later the project went live, on time, all written in VB4 and T-SQL code. And it worked very well.Back then our primary goals were data processing speeds, storage capacity, CPU utilization, scaling and transactional sanity. Fast forward into today all the aforementioned concerns remain as valid. However, what changed is that there are more options to tackle these issues up, in different ways, faster and more efficiently if not with a lower budget. What is new and improved under a DBA or developer’s belt in terms of tooling and capabilities Getting Started with SQL Server 2014 Administration book is going to cover.Indeed, the book has the right timing and is right on the spot covering well the unique and most massive advancement in the entire RDBMs world – InMemory data processing, delayed durability And it does not stop there, the reader will get known how to create databases in Windows Azure, backup into the Cloud or restore from it, creating hybrid AlwaysOn environments, performance enhancements and a few more topics. So this is a book SQL Server 2014 Enterprise specific and for a modern DBA.Alas, the book does not cover any other aspects of even a junior DBA, e.g. there is no mention on how to install SQL Server 2014 on premises, securing it up, choosing a backup strategy, monitoring. Likewise, several other topics that are actually new in this build are also amiss as Lock Priorities, T-SQL (1 change), tempdb performance and the new security roles.To continue on the negatives, one general comment I have is that the book has no enough in-depth feature coverage for non Enterprise grade releases, should have more examples, the book should have been expanded beyond the 80 or so pages of useful content to include the management tools overview, performance troubleshooting, auditing, etc. even for a ‘getting started’ book. Thing is, the life of even a novice DBA would be very demanding in terms of achieving acceptable application response time, feature suggestion, or just the time to fix an issue.I am giving this book a 2.5 rounded up out of 5 mark as I can hardly see how it outweighs the benefits of the MSDN help or the waste amount of online articles.Disclaimer: I received the copy of the book for free from the publisher.

AmazonCustomer Feb 21, 2018

Just 100 pages and the information are little. Book price is too much.

Getting Started with SQL Server 2014 Administration: Optimize your database server to be fast, efficient, and highly secure using the brand new features of SQL Server 2014

What do you get with a Packt Subscription?

Getting Started with SQL Server 2014 Administration

Chapter 1. SQL Server 2014 and Cloud

Windows Azure SQL Database

Creating a Windows Azure SQL database

Connecting to a Windows Azure SQL database with Management Studio

Migrating an existing database to Microsoft Azure

Integrating Microsoft Azure Storage

Creating a database that uses the Microsoft Azure Storage integration

Creating a Microsoft Azure Storage account and container

The container policy and Shared Access Signature

Creating a credential

Tip

Creating a database using Microsoft Azure Storage

Microsoft Azure Virtual Machines

Creating a Microsoft Azure Virtual Machine

Summary

Page 1 of 5

What you will learn

Product Details

What do you get with a Packt Subscription?

Product Details

Frequently bought together

Table of Contents

Recommendations for you

Customer reviews

People who bought this also bought

About the author

FAQs

Getting Started with SQL Server 2014 Administration: Optimize your database server to be fast, efficient, and highly secure using the brand new features of SQL Server 2014

What do you get with a Packt Subscription?

What you will learn

Product Details

What do you get with a Packt Subscription?

Product Details

Packt Subscriptions

Frequently bought together

Table of Contents

Recommendations for you

Customer reviews

People who bought this also bought

About the author

FAQs