Exploring SQL Server
2014 Database
Contents
Estimated time to complete lab is 60 minutes
With the confidence that he you can now navigate and administer the environment successfully, you can focus on provisioning the individual components that you require. Your first step is to create an Azure storage account. This storage account allows you a secure space to hold large files that don’t meet the schema of your structured database, or a place in which you can store Database backups should you need them. You will now create a storage account.
Connect to SQLONE computer
1. Click on SQLONE button on right side of the screen to connect to the SQLONE computer. If you see the following in the lower right corner of the screen, you can jump to step 5 below to set your screen resolution.
2. Click Send Ctrl-Alt-Del for SQLONE computer and then click
Switch user.
3. Click Send Ctrl-Alt-Del for SQLONE computer again and then click Other user.
Provisioning
a Windows
Azure
5. Right click on the desktop and click on Screen resolution. 6. Select 1366 x 786 (a good minimum screen size for using
SSMS) and click OK. 7. Click Keep Changes.
8. Resize the client holLaunchPad Online window for the lab to fit your screen resolution.
Create the storage account:
1. If you are not already in Azure Management Portal, open Internet Explorer from the start screen and browse to
https://manage.windowsazure.com/ then sign in using your Azure account.
Azure Storage is a fully-distributed data storage mechanism. Data is stored on independent nodes over multiple domains which significantly reduces the potential for data corruption and data loss. This provides confidence that your data stored in Azure will always be accessible. When combined with Geo-replication, where your information is also synchronized with a separate set of servers in a different part of the country, redundancy against natural disasters is also applied to your data store, minimizing risk to your business-as-usual operations.
2. Click on STORAGE from the blue navigation pane on the left
3. At the bottom left of the screen, click + New 4. Select Data Services, Storage and click Quick Create 5. For URL, use the first 7 characters of your Microsoft ID used for
accessing the Azure account followed by sqldbexport. For example, hdidemosqldbexport
6. In the Location/Affinity Group field select South North Central US
7. If asked select <<Your subscription identifier>> as the subscription
8. Choose the Locally Redundant option in the Replication field. Geo-redundancy enables Azure storage to store the data in two geographic locations. This ensures higher levels of redundancy to negate the effects of a natural disaster causing an outage to the server farm and increases the users’ confidence in the durability of their data. There are extra fees for this option. For the purposes of this walk-through, Geo-redundancy is not required.
9. Click on Create Storage Account
NOTE: this may take a couple of minutes to complete
10. Once the job completes, your storage account will be ready to use
Commented [A1]: North Central US available, but not South
2. Click the Copy button next to the Primary Access Key,
allow the webpage to access your clipboard if asked, and
click the checkmark button to close the Manage Access Keys window.
3. Open the Azure Storage Explorer from the Windows start screen
containers, blobs and the data within them from the desktop without having to log directly into your Azure portal.
4. On the welcome screen click Continue 5. Click the Add Account button
6. In the Storage Account Name field type the name of the storage account you entered in step 6 (this is visible in the
Manage Access Keys popup.)
7. In the Storage Account Key field, paste the account key you copied to your clipboard by using the 'CTRL+V' command and click Add Storage Account button.
If the copy and paste does not work, you may need to return to the Azure Management web page and manually select the contents of the Primary Access Key field, right click it and select Copy, and return to the Add Storage Account screen, right click the Storage Account Key field and select Paste
8. A message box explaining that the process for the first time creation of an account will appear. Click OK
You are now ready to provision a VM for SQL Server inside your Azure environment. This gives you the ability to quickly expand the capacity and capability of your data center by making more hardware available, or providing an offsite backup for your on-premises databases. Virtual Machines in Azure (VMs) are servers in the cloud rented by the minute (of machine time, not user time) which are
highly-customizable. In Azure, VMs come in a range of sizes and resource combinations. You can use the MAP tool (available at
http://technet.microsoft.com/en-us/solutionaccelerators/gg581074.aspx) to help decide which combination he needs.
1. If you are not already in the Azure Management Portal, open
Internet Explorer from the start screen and browse to https://manage.windowsazure.com/ then sign in using the
username and password you have been provided with.
2. In the blue navigation pane on the left, click on VIRTUAL
MACHINES
3. On the grey command bar at the bottom of the page, click
New.
4. Click Virtual Machine, and then click From Gallery.
You could alternatively upload a VHD image from his your on-premises deployment, and avoid having to rent the software license from Azure. Uploading a VHD image requires license mobility benefits using Software Assurance coverage in the existing product license (eg SQL Server.)
5. From Choose an Image, select SQL Server 2014 RTM
Enterprise. Click the arrow to continue.
Provisioning
a SQL Server
VM in
This image comes with Windows Server 2012 R2 and SQL Server 2014 set up with remote desktop access, Windows Update, Windows Firewall with Advanced Security, and .NET Frameworkd version 4 by default. You can configure the VM further after the initial set up to better meet Contoso’s needs (not covered in this tutorial.)
6. In Virtual Machine Name, enter a name for your new virtual machine.
7. In Size, select A3 (the smallest recommended size for this image.)
Possible VM sizes range from A0 (ExtraSmall - 768MB) to A9 (Extra Large with 16 cores and 112 GM memory) Larger sizes allow more/larger applications to be run on the VM but also cost more.
8. Enter a NEW USER NAME of AzureAdmin and NEW
PASSWORD of Pass@word12 and confirm the password in the
9. Click the forward arrow to continue.
10. Under Cloud Service select Create a new cloud service.
You could alternatively combine virtual machines under a single cloud service to provide more robust applications, but this tutorial only involves one virtual machine.
11. Use the default value for the Cloud Service DNS Name. 12. In Region/Affinity Group/Virtual Network, select South
North Central US.
13. Under Storage Account, select the storage account you created.
14. Under Availability Set, for the purposes of this tutorial use the default setting of None.
15. Under Endpoints, change the PUBLIC PORT value for the Remote Desktop endpoint from AUTO to 3389.
the port is important, but requires the port chosen to be opened in the local firewall.
16. Accept all other defaults in the Endpoints page.
Endpoints allow other applications or virtual networks to communicate with a virtual machine.
17. Click the forward arrow to continue.
18. Follow the links if you wish to read the license agreement and privacy statement for SQL Server
After the virtual machine and cloud service are created, the Management Portal lists the new virtual machine under Virtual Machines and lists the cloud service under Cloud Services. Both the virtual machine and the cloud service are started
automatically. This may take 10-15 minutes.
How to access the newly-created Azure VM through the Management Portal:
1. Click Virtual Machines, and then select the virtual machine you created earlier in the scenario.
2. On the command bar at the bottom of the page, click Connect. 3. If the Connect option is not visible, click on DASHBOARD at
the top of the page and the option should appear, click OK on the Azure prompt at the bottom of the screen.
4. Click Open to use the remote desktop protocol file that was automatically created for the virtual machine.
6. Select Use Another Account and enter the username
AzureAdmin and password Pass@word12, then click OK.
You have now remotely logged in to the new Virtual Machine. The Server Manager will start automatically but you do not need to do anything with it now. To leave the remote session, enter the start screen, click on the user icon in the top right corner and select Sign out.
The Azure VM is IaaS (Infrastructure as a Service) and so is highly flexible, allowing you to control the operating system and installed applications. You rent the Azure VM time (and optionally an SQL Server license) per running hour and can set up the environment to allow migration from on-premises SQL server with few-to-no code changes. Alternatively, you could use Azure SQL Database (PaaS – Platform as a Service) where Microsoft runs the SQL environment and organizations purchase the service on a database by database basis, which is suitable for new or simple-existing applications.
You are now in the position to connect to your Azure subscription from a local SQL server. To do this, he you needs to create some credentials
he you can use.
Download a publishing profile
When Azure creates a publishing profile, it automatically creates a management certificate with permissions on all subscriptions the user has access to, which it stores in its certificate store and in the .publishsettings file. The downloaded .publishsettings file can then be used as authentication for any user operations, and so should be kept somewhere secure. Alternatively, the certificate within the file can be extracted into the local certificate store and used as any other management certificate (not covered in this tutorial.)
1. In Internet Explorer, open a new window or tab and navigate to
https://manage.windowsazure.com/publishsettings
2. Save the publish settings file to C:\SQLCERTIFICATES\E4 using the default file name using the Save As button
After creating your credentials, you can now deploy and backup to the cloud.
You can take advantage of Windows Azure Blob Storage as a place to host his your data files. This hybrid environment would allow him you in the future to stand build up either on premise or hosted databases in the future on his your Virtual Machine in Windows Azure using his your
Windows Azure Storage as a dedicated data repository. By hosting the data in the storage environment it will simplify any database
movement between machines as they exist in a central location, and can take advantage of built-in ‘Attach’ and ‘Detach’ commands. His Your data also immediately benefits from the High Availability and DR provided by the Azure Storage services. You will now migrate databases into Azure blob storage.
You would use this option if you wanted to easily attach and detach the database to and from multiple computers, and to take advantage of features of Azure Storage such as high availability and unlimited storage. You can also encrypt your databases to increase security using TDE (Transparent Data Encryption), meaning all I/O operations are automatically encrypted.
Create a container to put the databases into
1. If you are not already in Azure Management Portal, open
Internet Explorer from the Windows start screen and browse
to https://manage.windowsazure.com/ then sign in using your Azure credentials
2. In Windows Azure Management Portal, click on Storage and select the storage account you created.
3. Click CONTAINERS
4. In the grey options bar, click ADD
Use Azure
5. Enter a name sql-datafiles for the new container and ensure
ACCESS is private
6. Click on the Check mark to create the container
Encrypt the database files
1. Open SQL Server 2014 Management Studio from the Windows start screen
2.3. In the File menu, select the Open and then File…
3.4. Browse to C:\SQLSCRIPTS\E4 and select
E4B-2#1-Encryption.sql
4.5. Click Open to open the script
5.6. Edit the file in the query window to replace <<password>>
with Pass@word12 to use for encryption in two places.
You can ignore the warning about not having a backed up database encryption key.
7.8. Close the query window without saving the changes.
If you do not close the query window, you will not be able to detach the database in the next step.
Detach the database files
1. In the Management Studio Object Explorer, expand the
Databases node and right-click ContosoSalesDB.
2. Select Tasks then Detach…
3. Tick on Drop Connections and click OK in the dialog box.
4. If asked, allow the webpage access to the clipboard
Use AZCopy to copy the database files into blob storage Note, the database files must be uploaded as page blobs to use as Azure Data Files, which cannot be done using Azure Storage Explorer. AZCopy is a command line tool designed to help move files to and from Azure and is available at
http://blogs.msdn.com/b/windowsazurestorage/archive/2013/0 9/07/azcopy-transfer-data-with-re-startable-mode-and-sas-token.aspx
1. In the Windows start screen, type cmd and press Enter to open the command line
2. Type the following command into the command line window:
3. Press Enter to run the command which navigates to where AZCopy is stored
4. Type the following command into the command line window: (note, the next step is replacing all the sections enclosed with <<>>)
cd "..\..\Program Files (x86)/)\Microsoft SDKs\Windows
AZCOPY C:\DemoDatabases http://<<your storage account>>.blob.core.windows.net/sql-datafiles ContosoSalesDB* /destKey:<<your storage access key>> /blobtype:page
In the query you just entered, replace <<your storage
account>> with the name of the storage account and
container on Azure, to replace <<your storage access key>> right click right after the : to paste in the value. 5. Press Enter to run the command
Wait until the transfer summary appears (this may take some time.)
Prepare Shared Access Signature authentication so Management Studio can read and write to the database files
Shared Access Signatures, or SAS keys, are used to provide access to Azure for less-trustworthy programs which should not be allowed full access to the subscription. These keys can have expiry dates or be revoked by the issuer should they become insecure, and also allow the programs fewer access rights.
c. Select the sql-datafiles container and then in the Blob section of the menu bar, click Security
d. Open the Shared Access Signatures tab. e. Leave the blob name blank and no policy selected. f. Mark the check boxes next to Read, Write, Delete, and List.
g. Click Generate Signature. h. Click on Copy to Clipboard.
i. Open Notepad from the start screen and paste the signature into the new file by pressing CTL+V
Note, the entire piece of text (pre-deletion) is a url for a web-browser to go to the container using the SAS key as permission. The remaining text (post-deletion) is a time-constrained secret access key. Alternatively, we could use the text from sr= on instead to make the signature not time-constrained.
Attach the database to Azure
1. Open Management Studio from the start screen, if not already open. Connect to SQLONE
2. In the File menu, click Open and select File...
3. In the file open dialog, navigate to C:\SQLSCRIPTS\E4 and select E4B-2#2-AttachO1.sql. Click Open to open the file. 4. In the query window that opens, replace <<your storage
account name>> and <<your container name>> with the
actual values each time they appear in the script. Do not yet execute the query.
5. Return to the Notepad file you created in the previous step. Select all the text and copy it by pressing CTL+C.
7. You now can and should close Notepad without saving.
The SAS key (the remaining text in the window) is secure information as anyone who has it can access the container and write/overwrite data there using their web browser, so the key should not be left in insecure places.
8. Run the Management Studio query by clicking Execute in the task bar.
This query may take some time to run.
See the new database from Management Studio
1. In the Management Studio Object Explorer, right-click on
Databases and select Refresh.
This refresh may take some time, but at the end,
ContosoSalesDB_Azure should appear in the list of databases.
2. Right-click on ContosoSalesDB_Azure and select New Query. 3. Type the following command into the query window:
4. Click Execute in the task bar.
This will produce a list of tables in the database. You can run any other transact SQL query on the database in this way. See the new database in Azure
1. In Management Studio, click Connect in the Object Explorer and select Azure Storage...
2. Enter the name of the storage account you set up previously as the storage account.
3. If you are not already in Azure Management Portal, open Internet Explorer from the start screen and browse to
https://manage.windowsazure.com/ then sign in using your Azure credentials.
4. In the Azure Management Portal navigation pane, click on
Storage and select the storage account you created previously.
5. Click on Manage Access Keys.
8. Click Connect.
9. Once the new connection has finished loading, expand the
Containers node and the node for the container you created.
The .mdf and .ldf files you uploaded will be visible.
You can use this option if you wanted to be able to later download the databases with a fully-functioning environment onto any computer, and/or to run these databases on an Azure Virtual Machine.
Create the VHD
1. In the start screen type compmgmt.msc and press enter. 2. In the left pane of the Computer Management window, select
Disk Management.
3. On the Action menu select Create VHD.
4. In the Create and Attach Virtual Hard Disk dialog box, type
5. Enter 30 MB as the virtual hard drive size. 6. Leave VHD and Fixed Size selected.
7. Click OK to finish the wizard.
8. Once the new disk is created (this may take some time), right-click on the new disk name in the lower-central pane and choose Initialize Disk (you may have to scroll down to see your disk, it will be the highest-numbered disk present, be
Unallocated and Not Initialized, and come before CD-ROM
9. In the Initialize Disk dialog box, accept defaults and click OK.
10. Right-click on the new disk block and select New Simple
11. Click Next for the Introduction page, Next for the Specify
Volume Size page.
13. Click on Next for the Format Partition page.
15. Click Format disk in the pop-up box then Start then OK twice then Close on the Format Disk wizard (this may disappear of its own volition. That is not a problem.)
16. Do not close Computer Management yet.
Place a backup of ContosoSalesDB onto the VHD
1. If Management Studio is not already open, open it from the start screen and connect to server type: Database Engine, server name: SQLONE using windows authentication. 2. In Management Studio object explorer, right-click on
Databases and select Restore Files and Filegroups...
3. In the General page of the wizard, type
ContosoSalesDB_AzureVHD in the To database... box.
6. In the Options page of the wizard, click on the Ellipses (…) button for Restore As for ContosoSalesDB.
7. Click OK in the pop-up box.
You can ignore the access error.
8. Scroll down and select the letter for the new drive you have created.
10. Click OK.
You have now created a copy of the database on the new vhd.
13. Right-click on the new ContosoSalesDB_AzureVHD database in Object Explorer and click Tasks -> Detach.
15. Return to Computer Management.
16. Right-click on the name of the disk you created (in the lower-central pane) and select Detach VHD.
17. Click OK. You may now close Computer Management.
Upload the VHD
Note, the vhd must be uploaded as a page blob, so this step cannot be done using Azure Storage Editor (which only supports uploading files as block blobs.)
a. If you are not already in Azure Management Portal, open Internet Explorer from the start screen and browse to https://manage.windowsazure.com/ then sign in using the username and password you were provided with. b. Click Storage on the navigation menu and select the
storage account you created previously.
This has copied the access key to your clipboard, we will use this later.
e. In the Windows Start screen, type cmd and click on
Command Prompt in the list of search results this creates.
f. Type the following command into the command window (note, to paste, right-click and select Paste, CTL+V will not work):
g. Press ENTER to run the command.
h. Copy or paste the following command into the command line (note, the next step is to replace all the values in the <<>>):
i. In the query you just entered, replace <<your storage
account>> and <<your storage access key>> with the
value you copied using right-click. j. Press ENTER to run the command.
k. Wait until the Transfer summary appears (this may take some time.)
cd "..\..\Program Files (x86)\Microsoft SDKs\Windows
Azure\AzCopy"
AZCOPY C:\SQLFILES\ http://<your storage
https://manage.windowsazure.com/ then sign in using your Azure credentials.
2. Click on Virtual Machines in the navigation pane, then DISKS.
3. In the grey options bar at the bottom, click CREATE. 4. Type in the NAME for the VHD as
ContosoSalesDB_AzureVHD.
5. Click the browse button next to VHD URL
7. Click Open.
8. Leave the check box next to The VHD contains an operating
12. In the Attach Disk dialog box, select the
ContosoSalesDB_AzureVHD disk you uploaded.
13. Click the check mark to attach the data disk.
1. Go to your minimized remote desktop session for your VM. If you happened to close it, click CONNECT in the options bar and log in again.
2. Press the Windows key and open SQL Server 2014
Management Studio for the start screen (type the first few
letters of Management and select the program from the list of options presented.)
3. In the remote session of Management Studio, connect using
5. Click Add.. to add a new MDF file.
6. Navigate to the highest-lettered drive and select the
ContosoSalesDB_Azure.mdf file.
7. Click OK to attach the database.
8. In the Object Explorer, expand the Databases node.
9. Right-click on ContosoSalesDB_copy.mdf and select New
Query.
10. Type the following command into the command window:
11. Click Execute.
You can run any query against this database now – it is attached and functioning normally.
You were able to provision your databases inside Windows Azure Storage providing you with future benefits including a central location to back up and restore databases from knowing that the information stored there is secure, and benefits from the native High Availability provisioning by the Windows azure platform. It also means data virtualization and movement has become easier to manage and is now a low cost to the OpEx budget. Storing data in this way also provides you an easy future growth plan due to the elastic scalability offered by this solution.
1. Open the Azure Management Portal at
https://manage.windowsazure.com/ and sign in with your credentials
2. Delete Azure Virtual Machines with their associated services and disks as follows:
a. Click on CLOUD SERVICES in the navigation pane b. Select the cloud service you want to delete (this will have
the same name as the virtual machine you created)
Azure
b. Click on the name of the storage account you want to delete the containers from
c. Click CONTAINERS at the page top and select the containers you want to delete
d. Click DELETE at the page bottom, confirming you do want to delete
© 2014 Microsoft Corporation. All rights reserved.
By using this Hands-on Lab, you agree to the following terms: The technology/functionality described in this Hands-on Lab is provided by Microsoft Corporation in a “sandbox” testing environment for purposes of obtaining your feedback and to provide you with a learning experience. You may only use the Hands-on Lab to evaluate such technology features and functionality and provide feedback to Microsoft. You may not use it for any other purpose. You may not modify, copy, distribute, transmit, display, perform, reproduce, publish, license, create derivative works from, transfer, or sell this Hands-on Lab or any portion thereof.
COPYING OR REPRODUCTION OF THE HANDS-ON LAB (OR ANY PORTION OF IT) TO ANY OTHER SERVER OR LOCATION FOR FURTHER REPRODUCTION OR REDISTRIBUTION IS EXPRESSLY PROHIBITED. THIS HANDS-ONLAB PROVIDES CERTAIN SOFTWARE
TECHNOLOGY/PRODUCT FEATURES AND FUNCTIONALITY, INCLUDING POTENTIAL NEW FEATURES AND CONCEPTS, IN A SIMULATED ENVIRONMENT WITHOUT COMPLEX SET-UP OR INSTALLATION FOR THE PURPOSE DESCRIBED ABOVE. THE TECHNOLOGY/CONCEPTS REPRESENTED IN THIS HANDS-ON LAB MAY NOT REPRESENT FULL FEATURE FUNCTIONALITY AND MAY NOT WORK THE WAY A FINAL VERSION MAY WORK. WE ALSO MAY NOT RELEASE A FINAL VERSION OF SUCH FEATURES OR CONCEPTS. YOUR EXPERIENCE WITH USING SUCH FEATURES AND FUNCITONALITY IN A PHYSICAL ENVIRONMENT MAY ALSO BE DIFFERENT.
FEEDBACK. If you give feedback about the technology features,
functionality and/or concepts described in this Hands-on Lab to Microsoft, you give to Microsoft, without charge, the right to use, share and commercialize your feedback in any way and for any purpose. You also give to third parties, without charge, any patent rights needed for their products, technologies and services to use or interface with any specific parts of a Microsoft software or service that includes the feedback. You will not give feedback that is subject to a license that requires Microsoft to license its software or documentation to third
Terms of
REPRESENTATIONS WITH REGARD TO THE ACCURACY OF THE RESULTS, OUTPUT THAT DERIVES FROM USE OF THE VIRTUAL LAB, OR SUITABILITY OF THE INFORMATION CONTAINED IN THE VIRTUAL LAB FOR ANY PURPOSE.
DISCLAIMER
