Windows Management and Scripting

A wealth of tutorials Windows Operating Systems SQL Server and Azure

Archive for the ‘Azure’ Category

Articles , Tutorials and reviews about Windows Azure and SQL Azure

Mirate or not to the SQL Azure

Posted by Alin D on June 27, 2012

Understanding the advantages and drawbacks of a SQL Server to SQL Azure migration is imperative for solution providers as they advise customers. This tip covers the features that make an SQL Azure migration attractive to some as well as the limitations that may have many customers deciding to stick with their existing database configuration.

SQL Azure feature limitations

SQL Azure is primarily a cloud-based database hosting service, not a full database management system. Consequently, many of the features customers rely on in SQL Server are not available in SQL Azure. For example, SQL Azure doesn’t include Analysis Services, Integration Services, Replication, Service Broker, SQL Server Agent or Master Data Services.

SQL Azure also falls short in its lack of extensibility. For example, you cannot implement Common Language Runtime (CLR) components on SQL Azure. That means there’s no support for CLR user-defined data types, including extensions to spatial types and methods. In fact, SQL Azure doesn’t support user-defined types or extended properties. If your customers rely heavily on SQL Server’s capacity for extensibility, they will not have an easy time with SQL Azure.

SQL Azure also lacks support for heaps, so every table must include a clustered index. Additionally, SQL Azure doesn’t support Windows Authentication, only SQL Server Authentication. Although neither of these limitations is insurmountable, solution providers need to be aware of them and all other feature-related issues before migrating customer databases.

SQL Azure migration downside: Physical constraints

When it comes to the physical implementation of databases, SQL Azure is a different animal from SQL Server, particularly where management is concerned. While SQL Azure lets you control such processes as schema creation, index tuning, query optimization and database security, it doesn’t provide for control of SQL Azure’s physical resources.

SQL Azure manages all the tasks related to physical resources, such as replication, load-balancing and performing backups. But, that means you cannot take actions such as assigning data files and indexes to specific hard drives or file groups. In fact, SQL Azure blocks any attempts to manipulate physical resources. As a result, you can’t set server options, run trace flags, or use utilities such as SQL Server Profiler or Database Tuning Advisor.

Performance can also be an important consideration if the SQL Azure database supports in-house applications. SQL Azure’s cloud-based structure is great if for Web-based apps for a global user-base. But in-house users have to use an Internet connection to access data and could see a significant decrease in performance, compared to an on-premises SQL Server database.

Also worth noting is that SQL Azure doesn’t support physical features such FILESTREAM storage, data compression or table partitioning. In addition, SQL Azure databases are limited in size to 150 GB, unlike SQL Server databases, which theoretically have no size limit.

Perhaps the biggest concern for many in IT when it comes to an SQL Azure migration is that the data is being stored off-site, and if that data is highly sensitive, the risks might be deemed too great to let it out of the control of an in-house storage solution. In some cases, compliance and regulatory issues might preclude a cloud-based solution altogether.

Transact-SQL differences in SQL Azure

The T-SQL used in SQL Azure is based on a subset of the language as it is implemented in SQL Server 2008. That means deprecated elements no longer supported in SQL Server 2008 are not supported in SQL Azure. And because SQL Azure uses a subset of T-SQL, not all language elements are available, even if they are supported in SQL Server.

As a result, before you make a SQL Azure migration, you must identify all T-SQL elements that can cause SQL Azure to choke. For example, if your data definition language (DDL) script includes statements to create user-defined types or define extended properties, you must remove those statements from your script.

There are a number of T-SQL statements that SQL Azure does not support, including those that try to control physical resources. Any DDL scripts that contain these statements — along with any applications that use them — will have to be corrected before you can implement a SQL Server database.

Preparing for an SQL Azure migration

Despite its limitations, SQL Azure offers plenty of benefits, and under the right circumstances, an organization can gain much by using the service. SQL Azure databases are easy to deploy and maintain, and you can get up to 100 MB of space for only $5/month. For a start-up that wants to try out an idea, SQL Azure allows them to quickly get up and running with none of the overhead and implementation headaches that accompany an on-premises solution. Not only are up-front costs avoided, SQL Azure scales up and down quickly and offers a level of availability that’s difficult to achieve in-house without a significant investment in resources.

So if your customers decide that the service is right for them, solution providers have several options for how to migrate the schema and data. One option is to use SQL Azure Data Sync, the SQL Azure Migration Wizard or the Generate and Publish Scripts Wizard in SQL Server Management Studio. All three options, except the pre-2008 versions of the Generate and Publish Scripts Wizard, allow for the migration of both the data and the schema. You can also use the bcp utility, Integration Services or the SQL Server Import and Export Wizard to copy data from SQL Server during an SQL Azure migration.

Regardless of the approach you take to move your schema and data to SQL Azure, the key to a successful migration lies in understanding what features and language elements are supported and what are not. If you help customers understand that SQL Azure is a basic database hosting service, and not a full-fledged management system, they’ll be better able to make a decision about whether to migrate their databases to SQL Azure and to prepare those database elements that can be migrated.

About these ads

Posted in Azure | Tagged: , , , , , , | Leave a Comment »

SQL Azure Security should not be an issue but loose of control should be took in consideration

Posted by Alin D on January 26, 2012

Historically, there have been loads of security-related concerns related to running key applications in the cloud, particularly essential systems. And some would-be users of Microsoft’s SQL Azure cloud-based database service are still holding back, but not because of any gaps in SQL Azure security. Rather, they’re nervous about surrendering control over their database environments, according to experts in the field.

“A lot of the issues just come from the fact that people get concerned about things they can’t control,” said Denny Cherry, an independent consultant specializing in all things SQL Server and the author of Securing SQL Server. Cherry cites as an example the fact that Microsoft takes care of all the physical security in the data center in addition to Windows security. “It makes them leery,” he explained. “There’s no way they can get a report of everyone logging in to the system in the last 30 days — Microsoft isn’t going to do that.”

Many SQL Azure enthusiasts and early adopters will argue that security concerns about the cloud version are no different than concerns surrounding traditional server-based SQL Server, especially with SQL Azure’s features designed to enforce security, including a server-side firewall, which lets database administrators (DBAs) manage and control connections from various sources to specific IP addresses or ranges. In addition, the cloud-based offering also supports SQL authentication while maintaining a secure connection to a database with SQL Server’s own protocol encryption.

herry himself draws little distinction between security best practices in the new SQL Azure world and what most DBAs are used to with traditional SQL Server environments. “SQL Server in itself is a secure platform, and SQL Azure is just another SQL Server instance. There aren’t really a whole lot of security issues with the platform itself.”

Usually, he said, the problems can be traced to a certain user or a developer who doesn’t know how to properly limit access rights. “We’re talking the same set of issues you see with regular databases,” Cherry said.

Standard security guidelines
That’s not to say there aren’t some remaining gaps or common miscues that undermine security in a SQL Azure environment. For example, there are limited auditing tools for SQL Azure, so some companies employ these capabilities in SQL Server environments to verify the concept of trusted users, according to Herve Roggero, one of the authors of the book Pro SQL Azure and managing partner for Blue Syntax Consulting, which provides consulting and development services around the Azure cloud platform..

“With on-premises SQL Server databases, you run auditing tools to warn you if things are taking place that aren’t supposed to … and in the cloud, there are no such auditing capabilities,” Roggero explained. “It’s a significant problem because it impacts compliance initiatives which require you have complete auditing of data, knowing when it is being changed and who is changing it. You don’t know that in SQL Azure.”

Another concern around SQL Azure security is how to easily get your data back if you decide the cloud isn’t the right path for your environment. “What if you decide this isn’t the solution for you and you want to change providers?” said K. Brian Kelley, database administrator and architect for AgFirst Farm Credit Bank. “Right now there are a lot of solutions that aren’t seamless, and that’s obviously a concern.”

Cherry said that if DBAs simply follow their standard security best practices, they should keep most of these lingering security concerns at bay. For instance, SQL Azure’s firewall by default is only accessible to Microsoft’s internal Azure servers. Cherry said users have to be careful to “poke only as few holes as possible in the firewall” to ensure optimal security.

They should also apply strong password protection and minimize the permissions required for applications to run. In addition, DBAs should enforce guidelines on minimum security rights, granting users the minimum rights they need to perform their jobs.

“Applying standard security practices when available is really important,” Roggero said. “Strong passwords for database accounts are key, as is use of database schema for help in securing specific tables. These are all things that were available before and add layers of security.”

Posted in Azure | Tagged: , , , , , , | Leave a Comment »

How future applications and enterprise platforms will be brought together with Windows Server 8 and Azure

Posted by Alin D on September 19, 2011

If IT managers in Windows shops had any doubts about Windows Server 8 and Azure serving as fundamental building blocks for their company’s cloud strategy, they don’t have any now.

From dawn to dusk at the BUILD conference here this week, Microsoft executives pounded home the message that Server 8 and Azure will have a hand-in-glove relationship that will anchor most of the company’s enterprise platforms and applications.

They also consistently pushed the cloud platform as a scalable, highly available option for application developers and IT shops, emphasizing the company is also building features into the server product that are informed by its experience engineering and managing the hosting service.

Al Gillen, an analyst at  IDC in  Framingham, Mass., commented on the increasingly strong relationship between Azure and Windows Server 8.

“We expect customers will increase their usage of Windows Azure due to synergies that Microsoft is building into both products, easing the adoption of a hybrid computing model,” Gillen said.

Azure is expected to serve as a platform for supplying data and services for Windows-based applications, as well as an identity manager for helping federate identity across a variety of Web services, according to Microsoft.

Satya Nadella, president of Microsoft’s Server & Tools Business division, said the availability of several new releases, including the Windows Azure Toolkit for Windows 8, the September release of Service Bus, and Windows Azure Storage, and said they were on pace for updates on a monthly basis.

The Azure Toolkit for Windows 8 contains code samples, documentation and components for building Metro style applications that use Windows Azure for connectivity and notifications.

At BUILD, Mark Russinovich, a Microsoft Azure technical fellow demonstrated the basics of Azure’s Platform-as-a-Service (PaaS) model and how it can best be leveraged to build applications in the cloud. Because Azure handles resource management, provisioning and monitoring, he said, developers and IT pros need only worry about what they produce.

Russinovich also noted the durable, scalable and high-availability nature of Azure storage. Data is replicated three times, and reflected to multiple data centers via “geo-replication” – so even if one goes down, the data is backed up elsewhere, he said.

Driving home the point about how far reaching cloud computing would influence its business, president and CEO Steve Ballmer said Microsoft’s major businesses — Windows, Windows Phone, Xbox, Office, Bing and Dynamics — would all be redesigned to work better with the cloud.

“You [Windows developers] need to think about the business opportunities cloud can offer,” Ballmer said. “Windows Azure, Windows Live and cloud all working together is an area where developers can add a lot of value,” Ballmer said.

Posted in Azure | Tagged: , , , , , , | Leave a Comment »

When and When not use the Windows Azure`s VM Role

Posted by Alin D on June 29, 2011

Windows Azure now includes a Virtual Machine role that allows organizations to host their own virtual machines in the cloud.

Microsoft introduced this role as a way to ease the migration of applications to cloud computing. So instead of waiting until your code is “cloud-ready”, you can use this role to move applications to the cloud while refactoring old code.

Where the VM role fits within Azure

Windows Azure currently has three roles: Web, Worker and Virtual Machine (VM). The Web role is used for web application programming on Internet Information Services (IIS) 7.0, while the Worker role is basically for any type of process that runs in the background with a front-end interface.

The VM role is the newbie, and it uses a virtual hard disk (VHD) image of a Windows 2008 R2 server. The image is created internally on your network using Hyper-V technology and then uploaded to Windows Azure. This image can be customized and configured to run whatever software you would like to run in the cloud.

Before pushing virtual machines out to the cloud, however, it’s important to understand the pricing, licensing and perquisites involved. Any instance of a VM role is priced by the compute hour, and licensing of the role is included in the cost

Compute Instance Size CPU Memory Instance storage I/O performance Cost per hour
Extra small 1.0 GHz 768 MB 20 GB Low $0.05
Small 1.6 GHz 1.75 GB 225 GB Moderate $0.12
Medium 2 x 1.6 GHz 3.5 GB 490GB High $0.24
Large 4 x 1.6 GHz 7 GB  1,000 GB High $0.48
Extra large 8 x 1.6 GHz 14 GB 2, 040 GB High $0.96

 This table can be found on Microsoft’s Azure Compute page.

All virtual machines are created using Hyper-V Manager on a Windows Server 2008 operating system, where R2 is recommended. You’ll also find that Hyper-V, IIS 7.0, Windows Azure SDK, ASP.NET are all required, with an optional install of Visual Studio 2010 also available. (More requirements for the Azure VM role are listed on MSDN.)

Where the VM can be used

So why would you want to implement the VM role? Well, let’s say you’ve done your due diligence and decided on Windows Azure as your cloud platform of choice. You are ready to move forward but have a lot of existing legacy applications that are written differently and may not work on the Azure platform. A rewrite of this code could have a lengthy roadmap even if you are utilizing agile programming. In my opinion, this is where the VM role should be used.

The VM role gives you complete control over the system where your code runs, so while you are rewriting code to work in Azure, you could also create and deploy customized VHD images to the cloud immediately. In other words, the VM role can be used to migrate an on-premise application to run as a service in the Windows Azure cloud.

Another ideal time to implement the VM role is when you aren’t sure if you want stay with Windows Azure for the long-term. What if you decide to change vendors? Windows Azure is a Platform as a Service (PaaS), which is simply a framework for developers to create applications and store data.

Basically, once you develop your product for Windows Azure, it runs on Windows Azure. But if your company takes a new direction and wants to leverage a different cloud platform from Amazon or VMware, guess what? You’ll have to recode because you won’t be able to move that application. The VM role acts as a bridge that connects PaaS with Infrastructure as a Service (IaaS); it gives Microsoft an IaaS platform and provides you with the portability to move vendors if a change of direction is needed.

When not to use the Azure VM role

While the use cases above make sense, to me a VM role in the cloud doesn’t seem like the best option for the long-term. For starters, if you push virtual machines to the cloud, you need good speeds to upload. So the bigger the VM, the longer that upload process will take. Secondly, Microsoft doesn’t maintain your virtual machines for you; you are responsible for patching and uploading the changes as a differencing disk.

When you look at it that way, maintaining a VM role for an extended period of time seems like a nightmare. Not only could the uptake be tremendous, but differencing disks are not my favorite virtual machine technology anyway as they are prone to corruption. Snapshot technology is much easier to deal with.

So while the Windows Azure VM role is good to have in the Azure platform, in my opinion it’s not a great long-term PaaS solution. What it can do is help bridge the gap while you are busy coding for a true Platform as a Service.

Posted in Azure | Tagged: , , , , , , , , , , , | Leave a Comment »

Working with SQL Azure Databases fromVisual Studio 2008

Posted by Alin D on May 29, 2011

SQL Azure Services fall under the umbrella of the Windows Azure Platform. Windows Azure Platform is one of the four online services that include The Windows Azure storage and compute, and Azure AppFabric offered by Microsoft. The other services are Bing, Live, and Microsoft Advertising.

In this chapter, we will be looking at how SQL Azure data may be accessed from the client premises by applications using well-known Microsoft technologies for data access. The emphasis will be more on administering the SQL Azure databases using the client and server APIs. We will also discuss the best practices of accessing data from the client. For the practical elements of this chapter, we will be mostly using Visual Studio 2008 SP1, and with some comments on using Visual Studio 2010. The following topics will be covered:

  • SQL Azure architecture
  • Microsoft data access technologies
  • Easy way to connect to SQL Azure with Microsoft data access technologies
  • Preferred way to connect to SQL Azure
  • Connecting to SQL Azure using server APIs
  • Creating database objects using ADO.NET

SQL Azure architecture

SQL Azure has a four-layered architecture, a Client Layer followed by a Services Layer, a Platform Layer, and finally the Infrastructure Layer. A schematic of the architecture is shown in the following diagram:

The Infrastructure Layer has all the associated hardware in the Microsoft Data Center as described in Chapter 1, Cloud Computing and Microsoft Azure Services Platform, managed automatically by the SQL Azure Fabric. The Platform Layer, which is above the Infrastructure Layer, has all the SQL Server clusters managed by the fabric as well as the Management Services. The Services Layer above the Platform Layer does all of the business logic, provisioning, connection routing, billing, and so on. The Client Layer above the Platform Layer can be at the client site or at the client’s hosted site with all the applications that need access to SQL Azure.

Microsoft client technologies such as ODBC, ADO.NET, residing in the client site (or, in the Windows Azure Platform) converse with SQL Azure using TDS while WCF Data Services uses HTTP/REST. Applications such as PHP, SQL Server Applications and Tools, and WCF Data Services can work with ODBC and ADO. NET. Hence, TDS is the carrier of choice for information from SQL Server Tools, as well as the various applications.

The four-layered architecture shown in the previous diagram is based on the Microsoft documentation. As seen here, all applications access the Services Layer using TDS+Secure Sockets Layer (SSL) mediated by either ODBC or ADO.NET. The Services Layer routes the client requests to the Platform Layer where the SQL Server comes in, to process the client requests mediated by the SQL Azure Fabric.

The following diagram shows the TDS Gateway Layering in SQL Azure with some more details, copied, with permission, from a PowerPoint presentation by Jeff Currier to PDC 2009. This corresponds to the Services Layer of the previous image.

The Gateway layer enforces the authentication and authorization policy. This security enforcing layer isolates the application from the databases. The TDS Gateway takes care of a number of tasks, such as provisioning the endpoint, administering the AdminService, connection management, and so on. SQL Azure login requests arrive at the Gateway, which then accesses the Master and User DBs. Credentials to the User DB are matched to those on the Master and, after validation, a TDS session opens to forward requests to User DB. Make sure you watch the video presentation (Microsoft SQL Azure Database: Under the Hood) by Jeff Currier at the PDC here: http://microsoftpdc.com/Sessions/SVC12.

Application access to SQL Azure

Onsite client applications access the SQL Azure Services (databases) on the cloud using standard client libraries such as ODBC, ADO.NET (using TDS protocol), and so on. What this means, is that, all of these technologies are familiar to application developers and they are not required to learn new developmental techniques. In addition to Microsoft technologies, open source programming languages can also be used to develop Azure applications. We will look at the details of how client libraries access SQL Azure in this chapter. We will also learn how to manipulate database objects after accessing SQL Azure using T-SQL.

The client applications using TDS protocol arrive at the SQL Azure databases after passing through a load balancer, which forwards the TDS packets to the TDS Gateway layer, which then passes them on to the SQL Azure databases.

TDS and SQL Azure

Tabular Data Stream (TDS) was the technology originally created by Sybase to allow applications to access data stored in relational tables. Before SQL Azure came into existence, its predecessor SQL Data Services (SDS) was only able to access data using HTTP(s) or REST. Leveraging TDS, Microsoft skillfully morphed SDS into SQL Azure, so that the SQL Servers can be accessed in their native protocol and T-SQL code can be run in the cloud.

Presently, as shown in the following diagram based on the Microsoft documentation, data can be accessed by HTTP(s), as it is done by web facing applications (Scenario B) as well as using TDS from onsite applications and tools (Scenario A). Ultimately, however, it is TDS + SSL that finally reaches SQL Azure.


Microsoft data access technologies

 

There are two ways to interact with data using Visual Studio, either by designing an application using datasets and data adapters, or by performing direct operations on the data source, in this case the SQL Azure database. When and why you use one or the other depends on the situation you are trying to address. In situations where you are performing a database lookup, creating and modifying database structures, such as tables, views, stored procedures, or executing queries to get a single aggregate value, and so on, you directly interact with the database. This is because if you are creating or modifying database objects, you obviously cannot use datasets. On the other hand, if you are trying to access the data on the SQL Azure server from the web, you will be connecting using HTTP/HTTPS or REST-based technologies and will be using datasets and data adapters. The SQL Azure architecture accommodates both types of interaction. In this chapter, we will be looking at direct interaction with the database.

Using Visual Studio, you use data commands to work directly with the database. The steps to execute commands are as follows:

1. Create a connection.

2. Configure a command that uses the connection with a SQL statement or the name of a stored procedure.

3. Execute the command.

4. Retrieve the data that the command produces by a data reader.

Connecting to the database

In connecting to the SQL Azure database, you can use the following clients that are supported by Visual Studio 2008 by default:

  • SqlConnection
  • OdbcConnection
  • OledbConnection
  • EntityConnection

Data providers

When you use the assembly System.Data (C:WindowsMicrosoft.NET Frameworkv2.0.50727System.Data.dll), you can access the namespaces as shown in the following screenshot:

In order to access the EntityConnection, however, you should reference the System.Data.EntityClient.

The best way to understand the various namespaces/classes that help in connecting to the SQL Azure is to look at the namespaces/classes in the Object Browser.

The SqlConnection class member details are easily seen in the Object Browser when you search for SqlConnection.

Similarly, you search for OdbcConnection in the Object Browser to get all the related members.

Additionally, you can look up the details for OledbConnection and EntityConnection classes by referencing the corresponding namespaces. It is recommended that you review some of the members that create and manage database objects that we will be using later.

Connection string

In order to access the SQL Server you need to know the connection string. The connection string is a list of key/value pairs specific to each type of provider (SqlClient, ODBC, and OLEDB). If you know the connection string, you can directly specify it in the code, as shown here for SqlConnection without waiting for the intellisense to guide you, by typing it as an argument to SqlConnection(). Similar arguments may be made for the other providers.

This is easily available in the Visual Studio IDE as an intellisense drop-down. Intellisense is a great help in being productive.

In the following screenshot, the connection string to the SQL Azure is provided but partially hidden in the view. The screenshot shows everything that is accessible to the SqlConnection:

Following this, you will need to open the connection, which happens to be one of the methods of SqlConnection. The connection is necessary for you to create a command that you can execute.

Commands

The commands that we mainly use in SQL Azure are as follows:

  • SqlCommand
  • OleDbCommand
  • OdbcCommand
  • EntityCommand

Once the connection is established, you can create commands that can be run on the data source by setting up the code to create a command as follows:

CommandType shown in the previous screenshot is another property associated with commands. You should exercise caution when this property is of type Text. This is where SQL injection attacks take place. A preferred type to deter injection attacks is StoredProcedure or a parameterized query.

What do the commands accomplish? SQL commands are issued to accomplish several tasks such as the following:

1. You can execute to return result sets that can be read with an associated reader such as:

°°SqlDataReader

°°OleDbDataReader

°°OdbcDataReader

°°EntityDataReader

Search for everything that the SqlDataReader can do in the Object Explorer.

2. Execute Data Definition Language (DDL) commands to create, edit, and delete tables, stored procedures, and so on, provided you have permissions to do these operations.

3. Get database information by executing dynamic SQL commands to update, insert, and delete records.

4. Execute commands to return a single scalar value like an aggregate value.

5. Execute to return XML values and query the XML code.

In the following section, you will use connection builders to create tamper-free code that will prevent code injection by external means.

Using connection string builders

 

The previous section showed you how to use the connection strings to access the database. However, it is not good practice to send the strings in the form shown, as it can be tampered with. Connection strings must be carefully protected and secured. This is especially true when you are accessing them over the internet. One of the security considerations to prevent SQL injection attacks is to prevent externally injected script getting into the connection string. The injected script, while being syntactically correct, can introduce malicious code. If the connection string is obtained at runtime from user inputs, this is even more important.

While connecting to SQL Azure over the internet, make sure that the ADO.NET Encrypt (=true) and TrustServerCertificate (=false) connection properties are in place. This will ensure an encrypted connection and prevents the man-in-the-middle attacks (http://msdn.microsoft.com/en-us/library/ff394108.aspx).

In versions earlier to ADO.NET 3.5, compile-time checking of connection strings, formed by concatenating string values, did not occur, so at runtime, additional values of a malicious nature could be injected (for example, by adding a semi-colon followed by a key value pair). Review this article, for example: http://www. codeproject.com/KB/database/Connection_Strings.aspx.

Also, different providers supported a different syntax for connection string keywords (Password or PWD, and so on) making it difficult to string keywords manually and validate them. In ADO.NET 2.0, the new connection string builders for each .NET framework provider were introduced. The data providers, since then, included a connection string builder class, which would build a string for only acceptable key values for that provider. This acts as a filter for inserted code, allowing only those acceptable to the provider. You will find the details of these functions in SqlConnectionStringBuilder.

The different connection string builders trace their roots to the DBConnectionstringBuilder class. The .NET framework Connection String Builder class has the following connection string builders (EntityClient provider was added in Framework 3.5):

Provider Connection String Builder
System.Data.SqlClient SqlConnectionStringBuilder
System.Data.OleDb OleDbConnectionStringBuilder
System.Data.Odbc OleDbConnectionStringBuilder
System.Data.OracleClient OracleConnectionStringBuilder
System.Data.EntityClient EntityConnectionStringBuilder

In a manner similar to the connection strings, you can get a full appreciation of the properties that these classes support by looking them up in the Object Browser.

Using the connection builder is a recommended way of forming a connection string, not only for SQL Azure, but also for any place where a connection string is going to be used.

Accessing SQL Azure data using the Server Management Objects (SMO)

The Server Management Object model diagram available here: http://msdn. microsoft.com/en-us/library/ms162209.aspx reveals a rich feature set that this model offers to manage the SQL Server. SMO is based on SQL Server Foundation Classes (SFC). This model is based on the SQL Server API and replaces the older SQL-DMO (Distributed Management Objects) and is very comprehensive with many new features announced for SQL Server 2008. With SQL Azure you can use SMO but only a subset of the features is supported. Features like snapshots, trace, replay SQL Server events, service broker, and so on are not supported.

The SMO model is built on a hierarchy of objects with the server at the very top. The rest of the objects are all instance class objects. Using this model you can program all aspects of the SQL Server. The objects are only loaded when specifically referenced.

To access a server you need to establish a connection first. This applies to using SMO as well. You create an instance of the server object and establish its connection to an instance of the SQL Server. In the present context, the connection we would like to establish is to the SQL Azure database. Following the creation of a server object, a ServerConnection object is created, a variable that can be used again and again. One difference with the Client APIs described earlier is that it is not necessary to call a Connect method. SMO will automatically connect when required and after the operation it is going to perform is finished, it releases the connection to the pool. It is also possible to call a Non-pooled Connection property of ServerConnection object.

If you are in doubt at any time using a method or property, make sure you access the SMO in the Object Browser.

In order to view this in the Object Browser, after adding the three references, right-click Microsoft.SqlServer.Smo and choose View in the Object Browser. The assembly node is then revealed in the Object Explorer. This is because, sometimes, even though the assembly is added to the project in the Solution Explorer, it does not get displayed in the Object Browser.

We will look at connecting to an SQL Azure database using SMO in this chapter with a practical example.

Accessing SQL Azure from Visual Studio 2010 Express

 

Visual Studio 2010 Express is a free program from the Visual Studio suite that can work with SQL Azure. The details of downloading this program are described here: http://hodentek.blogspot.com/2010/06/get-these-web-development-tools-for.html. You may download and install the program in a few easy steps starting from here: http://www.microsoft.com/express/Web/. After installing, you will have a shortcut in Start | All Programs from where you can launch the application. Visual Studio 2010 Express installs a Microsoft Visual Web Developer 2010 Express and a Microsoft Visual Studio 2010 Express for Windows Phone. You will be using the web developer.

• Launch the application as an administrator (Run as administrator option after a right-click).

• We will connect to SQL Azure from, for example, a web application.

• Click on File | New Project and create an ASP.NET Web Application.

• Change the default name from WebApplication1 to one of your own, say ConSQLAzure.

• Click the menu item Data and click Add New Data Source.

• In the New Data Source window, click on the Database icon and click Next>.

• In the Choose Your Data Connection window, you may make a new connection using the New Connection… button.

• Click the New Connection… button to display the Add Connection window, as shown in the following screenshot:

 

The easy way to connect to SQL Azure using ADO.NET 3.5, ODBC, and OLE DB

You will now see how easy it is to connect to an SQL Azure database using Microsoft client programs such as ADO.NET, ODBC, and OLE DB. In connecting to the database, the single most important item is the connection string. The connection strings for SQL Azure are readily available at the portal as described in Chapter 2, SQL Azure Services. However, we may need to use appropriate arguments while constructing the connection string for OLE DB.

In the following steps a connection is opened and later closed for the Bluesky database, created in the first chapter with the ADO.NET 3.5, ODBC, and OLE DB.

• Run Visual Studio 2008 as an administrator from its shortcut in Start | All Programs.

• From File | New Project… (CTRL + N) |Visual Basic, choose from Visual Studio installed templates; a Windows Forms Application project in the default Framework option (3.5). Change the default name and click on OK. This adds a form Form1.vb and a My Project folder to the project (herein named TestConnect).

• Drag and drop three buttons on to the Form1.vb, as shown in the following screenshot:

Using ADO.NET to connect to the SQL Azure database

The SqlClient is used for establishing an ADO.NET connection to the SQL Azure database as described previously.

In the following steps, you will be writing the code to one of the form’s button click event that establishes a connection to the SQL Azure database.

1. To the click event of the button Connect Using ADO.NET, insert the code shown here:

Imports System.Data.SqlClient

‘Imports System.Data.Odbc

‘Imports System.Data.OleDb

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles Button1.Click

Dim conn As New SqlClient.SqlConnection

conn.ConnectionString = “Server=tcp:Your SQL Azure Server Name.database.windows.net;” & _

“Database=Bluesky;User ID=Your User ID@ Your SQL Azure Server Name;Password=Your Password;” & _

“Trusted_Connection=False;Encrypt=True;”

conn.Open()

If conn.State = ConnectionState.Open Then

MessageBox.Show(“Connection Opened”)

End If

conn.Close()

MessageBox.Show(“Connection Closed”)

End Sub

End Class

The trick to insert the connection string easily without errors, is to copy and paste from the portal.

2. Open IE browser and log in to your Windows Live ID account. Open http://Sql.Azure.com in your IE Browser. The Your User ID in the portal is the same as the project name in the portal. Click on the Database Name you want to connect to in the portal after highlighting the project.

3. You should see the tabbed folder with the tabs Databases and Firewall settings.

4. Click on Connection Strings in the Databases tabbed page and click on Copy to Clipboard, the ADO.NET connection string.

5. Paste the code into the statement, conn.ConnectionString=” “.

6. Now replace my password with your password.

7. Build and run the form.

8. Verify that you can connect to the database.

Alternatively, you can also establish an ADO.NET connection to the database concatenating the following parameters:

Initial Catalog=Bluesky

Data Source=tcp:Your Server Name.database.windows.net

User ID=Your Project Name @Your Server Name

Password=Your Password;

Trusted_Connection=False

Encrypt=True

Using ODBC to connect to the SQL Azure Database

This is no different from connecting to ADO.NET as shown in the previous steps except that, you must include the imports System.Data.ODBC in your code for the click event of the button Connect Using ODBC. Again, cutting and pasting from the portal is the easiest way.

From the portal, cut and paste the connection string for ODBC, as described in the previous steps, into the code for the click event of the button marked Connect Using ODBC, as shown here, and change the password:

‘Imports System.Data.SqlClient

Imports System.Data.Odbc

‘Imports System.Data.OleDb

Public Class Form1

+ Private Sub Button1_Click …(shown collapsed)

Private Sub Button2_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles Button2.Click

Dim conn As New Odbc.OdbcConnection

conn.ConnectionString = “Driver={SQL Server Native Client 10.0};” & _

“Server=tcp:Your server Name.database.windows. net;Database=Bluesky; ” & _

“Uid=Your Project Name@Your Server Name;Pwd=Your Password;Encrypt=yes;”

conn.Open()

If conn.State = ConnectionState.Open Then

MessageBox.Show(“Connection Opened”)

End If

conn.Close()

MessageBox.Show(“Connection Closed”)

End Sub

End Class

Again, build the project and verify that you can connect to the database.

Using OLE DB to connect to the SQL Azure database

Only ADO.NET and ODBC are supported on the SQL Azure platform. However, client connection to the SQL Azure database using OLE DB is possible. You need to construct the correct string that is supported by the OLE DB provider, SQLOLEDB. The connection string parameters are:

Provider = SQLNCLI10.1;

Server = tcp:Your Server Name.database.windows.net;

Database = Bluesky;

UID= Your Project Name @ Your Server Name;

Password = Your Password;

Enter the code to the click event of the button marked Connect Using OLE DB:

‘Imports System.Data.SqlClient

‘Imports System.Data.Odbc

Imports System.Data.OleDb

+ Private Sub Button1_Click …(shown collapsed)

+ Private Sub Button2_Click …(shown collapsed)

Private Sub Button3_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles Button3.Click

Dim conn As New OleDbConnection

conn.ConnectionString = “Provider=SQLNCLI10.1;” & _

“Server=tcp: Your server Name.database.windows.net;” & _

“Database=Bluesky;” & _

“UID= Your Project Name@Your Server Name;” & _

“Password= Your Password;”

Try

conn.Open()

If conn.State = ConnectionState.Open Then

MessageBox.Show(“Connection Opened”)

End If

conn.Close()

MessageBox.Show(“Connection Closed”)

Catch ex As OleDb.OleDbException

MessageBox.Show(ex.Message.ToString)

Finally

End Try

End Sub

End Class

Again, verify that you can establish the connection by building the project and running the form.

In the previous code, a Try…Catch exception handling routine is added, which helps in trapping errors arising, while connecting to the database.

Using ADO.NET to connect to a SQL Azure database in C#

Although the code shown earlier is in VB, it could be easily written in C#, as shown here for one of the cases:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace TestConCSharp

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void button1_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection();

con.ConnectionString=”Server=tcp:Your Server Name. database.windows.net;Database=Bluesky;User ID=Your Project Name@ Your Server Name;Password=Your Password;Trusted_Connection=False;Encrypt=T rue;”;

con.Open();

if (

conn.State==ConnectionState.Open

)

MessageBox.Show(“Connection Opened”);

else

MessageBox.Show(“Connection not open”);

con.Close();

MessageBox.Show(“Connection closed”);

}

}

}

 

Using SQL Server Management Objects (SMO) to connect to SQL Azure

For working with the SQL Server 2008, Microsoft has provided a collection of namespaces (SMO), which contain different classes, interfaces, and so on that help to programmatically manage the server. We will now use the elements of this namespace to access the SQL Azure server. This is a powerful tool, as it is based on the SQL Server API object model.

In the following steps, we will create a Windows Forms Application (even a console application can be used) and add references to the Server API that works with SMO and show how a connection can be established to SQL Azure.

1. Create a Windows Forms Application project (herein SmoSqlAzure) and to the default drag-and-drop a button.

2. Right-click on the References node and from the drop-down click on Add Reference….

3. The Add Reference window gets displayed, as shown in the following screenshot:

4. In the Add Reference window under .NET scroll down and add the following references:

Microsoft.SqlServer.ConnectionInfo

Microsoft.SqlServer.Management.sdk.Sfc

Microsoft.SqlServer.Management.Smo

5. The project folders in the Solution Explorer should appear, as shown, after the references are added.

6. To the code page of Form1.vb, add the following code:

Imports Microsoft.SqlServer.Management.Smo

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles Button1.Click

‘Provide SQL Azure connection Parameters as follows

‘Use the ConnectionContext of the server

Dim srv As New Server(“Your Server Name.database.windows. net”)

srv.ConnectionContext.LoginSecure = False

srv.ConnectionContext.Login = “Your ProjectName@Your Server Name”

srv.ConnectionContext.Password = Your Password”

srv.ConnectionContext.DatabaseName = “Your Database Name”

‘create a strcutured exception block as shown

Try

srv.ConnectionContext.Connect()

Catch ex1 As Exception

MessageBox.Show(ex1.InnerException.ToString)

Finally

MessageBox.Show(srv.ConnectionContext.IsOpen)

End Try

If (srv.ConnectionContext.IsOpen) Then

srv.ConnectionContext.Disconnect()

MessageBox.Show(srv.ConnectionContext.IsOpen)

End If

End Sub

End Class

The code shows some of the members of the SMO such as ConnectionContext, IsOpen, Connect, Disconnect, and so on.

Creating database objects using ADO.NET

Here, you will now connect to the SQL Azure with your connection string stored in the application’s settings file. You will also create and drop a database, create a table, and populate it, and so on. Although only the code for ADO.NET is demonstrated, ODBC and OLE DB may also be used.

Using connection string information in application settings

The very first thing, in this task, is to save the connection string information to the application settings. There are two databases in the SQL Azure server we have been working with, the master database and the database named Bluesky. In the next step, we will store the master database’s connection string to the settings file.

1. Create a Windows Application project using the Windows Forms Application template and give it a name of your own (herein it is CreateDBObjects).

2. The program creates a project folder in the Solution Explorer, as shown in the following screenshot:

3. Add four buttons and a label and configure their visual appearance as follows:

The buttons are numbered from 1 to 4 and must be associated with the code that follows.

Inserting connection string information to the application settings file

In the following steps you will copy the connection string information from the SQL Azure portal and save it in the configuration file of your application.

1. Copy the connection string from the SQL Azure portal as you have done before (shown here details are masked).

Server=xxxxxxxxxx.database.windows.net; Database=Bluesky;User ID=yyyyyy@xxxxxxx;Password=myPassword;Trusted_ Connection=False;Encrypt=True;

2. From the Projects menu click open the projects properties page.

3. Click on the Settings tab in this window. This opens the Settings page of the application, as shown in the following screenshot:

4. Set the following values for the four fields in the previous window:

Name: Give a name (herein, it is mdbs).

Type: String (no change).

Scope: Application (choose from drop-down).

Value: Enter the connection string of the master database you copied from the portal here. Make sure it is all in one line. If necessary, click on the value field, which opens a wider and a longer window.

5. Build the project. An app.config file gets added to the project.

6. Click on the app.config file.

7. The app.config file gets displayed as shown in the follwoing screenshot. Your connection string information will appear in the indicated position between <value> and </value>. Review the file and make sure there are no syntax errors, characters, such as “& _”, which are common line continuation character strings in VB.NET, extra white spaces, and so on.

Connect to the database on the server using the settings

 

In this code, you will be connecting to the database using the connection string you saved to the configuration file, in the previous section.

1. Click on the app.config file.

2. To the click event in the code page of Form1.vb, add the following code:

Imports System.Data.SqlClient

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

GetConnected()

End Sub

Private Sub GetConnected()

‘Use the connection string in the settings page

Dim constrg = New CreateDBObjects.My.MySettings

Dim con As New SqlConnection

‘the current connection will use value in the ‘settings

con.ConnectionString = constrg.mdbs

con.Open()

If con.State = ConnectionState.Open Then

MessageBox.Show(“Connection is open now”)

End If

con.Close()

End Sub

3. Build the project and run the form.

4. Verify that you can connect to the SQL Azure server.

5. The database you will be connecting to will be the database in the connection string you supplied.

Summary

 

In this article, the SQL Azure architecture was briefly reviewed. Accessing SQL Azure to manipulate objects, therein using client technologies such as ADO.NET, ODBC, and OLE DB, was described with examples. Connecting to SQL Azure for data manipulation, using Server APIs with examples, was considered using Visual Studio 2008 SP1. Also considered was the best practice of using ConnectionBuilder to thwart SQL injection attacks in detail. Examples of using ADO.NET for creating database objects were detailed using both Client APIs as well as Server APIs. The method to connect to SQL Azure using the entity framework was not described here, but is described with a complete example in next article, Database applications on Windows Azure Platform accessing SQL Server databases.

Posted in Azure | Tagged: , , , , , , , , , , , , , | Leave a Comment »

What Microsoft SQL Azure cloud platform means for the database administrator

Posted by Alin D on May 20, 2011

The reality, though, is that SQL Azure — the version of SQL Server that lives in Microsoft’s Azure cloud computing service — is a pretty important product.

Especially for organizations that only use SQL Server to support third-party applications, cloud-based databasespromise less overhead, more turnkey operations and other advantages. There are potential downsides, of course, but this column isn’t about whether Microsoft SQL Azure (or any cloud service) is right for you; it’s about what the “reluctant DBA” needs to know if his organization wants to push their data into Azure.

The neat thing about Microsoft SQL Azure is that, by and large, you manage it a lot like you would a local SQL Server installation, only you don’t worry at all about the actual hardware. You still create logins; you still have databases; you still rebuild indexes. Azure isn’t like the “shared hosted SQL Server” that hosting providers have offered for years; with Azure, it doesn’t look like you’re sharing anything with anyone. In many respects, it’s as if some Microsoft ninjas broke into your data center, picked up your SQL Server computers, and carried them off to some other data center. Apart from the location of the hardware, not much changes.

That said, the Azure team — like most of Microsoft these days — is buying into Windows PowerShell in a big way, and the SQL Server technology teams are continuing to expand their support for, and reliance on, this task automation framework. So if you’re planning to automate any of your SQL Server tasks, start wrapping your head around PowerShell now.

If, however, you’re only looking to manage and maintain a few Azure-based databases from time to time, then the graphical user interface tools you know and love can continue to do the job. The allure of Azure, aside from moving the computing activity out of your own data center, is that it can make your data available anywhere in the world through the back-end magic of the platform itself.

What Microsoft SQL Azure doesn’t do is change how you plan, secure, maintain or troubleshoot your databases. Backups don’t even become less critical: You’re not likely to face a disaster recovery scenario with Azure, but you still may want the ability to roll a table back to a specific point in time to undo some unwanted change. Backups are still the key to that.

From a DBA perspective, you’ll face the following tasks:

  • Rebuilding and reorganizing indexed on a regular basis. Azure doesn’t change the way indexes work or how SQL Server uses them.
  • Grabbing backups with whatever frequency is appropriate for your organization.
  • Managing logins and database users, as well as database roles, application roles and other security principals within SQL Server itself.

If you’re already used to performing these tasks in a local SQL Server installation, then performing them in an Azure-based database won’t be too much of a challenge. That’s actually one of the biggest selling points of Microsoft SQL Azure compared with other companies’ cloud offerings: It works (more or less) just like the SQL Server product you’re already used to.

 

Posted in Azure | Tagged: , , , , , , , , , | Leave a Comment »

How to connect to SQL Azure from SSMS

Posted by Alin D on May 11, 2011

Connecting to SQL Azure from SSMS is like connecting to any of the local SQL servers. To do this, you will need to perform the following steps:

1. Open the SSMS (the version used here is the one that came with SQL Server 2008 R2 November CTP installation) from its shortcut in Start in the Windows 7 desktop.

2. The Connect to Server dialog box will pop up wherein you need to insert the required information. The Server name and Login fields are author authentication information, shown pixelated. The Server name was automatically provided by Windows Azure while provisioning and the Login and Password of the administrator were provided by the user.

Make sure that the authentication is using SQL Server Authentication, as shown in the following screenshot:

3. Next, click on Options>>. This opens the Connection Properties tabbed page of the Connect to Server window, as shown in the next screenshot.

4. Click on the drop-down handle for the database, which displays the two options, shown in the following screenshot:

5. Click the <Browse Server…> menu item.

6. You will get the Browse for Databases window, as shown in the following screenshot. Click on Yes.

7. If you installed SQL Server 2008 R2 November CTP (or SQL Server 2008 R2) and did not access SQL Azure, then you will not be able to browse to the server in some instances. In this case, type in the server name and the password and click on Connect. You should then get connected to the SQL Azure Server.

8. The Connect to Server window will be displayed, as shown in the following screenshot, with the nodes expanded:

9. There are two databases on this server. The system database master is created by the SQL Azure provisioning system, and the Bluesky was user created.

10. Click on master or Bluesky to enable the OK button, and then click on OK.

11. The Connect to server window then gets updated with this information.

12. Click on Connect. Now, you will be able to see the databases on the server, as shown in the following screenshot. The nodes in the two databases are expanded. While in the user-created database there are four default users, the master database has one more user, the Server Administrator.

Once you are in SSMS with your SQL Azure Server displayed, as shown in the previous screenshot, you can work with the server as you would with any other SQL Server. You need to remember though, that SQL Azure statements/queries are executed in the cloud even though you may see the queries and query results in SSMS. Also, remember that not all features of SQL Server 2008 are supported in the cloud. Refer to the discussion in the earlier part of this chapter regarding features that are supported.

What we have seen till now are steps for ideal conditions of connection. In practice, you may encounter a variety of messages such as the ones shown here:

• Sometimes the SQL Azure may not respond immediately, in which case you may get the following message:

Sometimes you may have established a connection, which remained for a considerable time without being used, resulting in the following message, and you may have to try to reconnect.

The firewall should be established when you configure it in the server and it may take about five minutes to get established. This is the first administrative task in SQL Azure. If you were to try to connect to the server using SSMS without setting up the firewall you may get the next message. As observed, in practice there are times when the process may take more than five minutes. In all these cases, the recommendation is to try again until you establish a connection. As long as the firewalls are in place and you are connecting to the server from an allowed site, you should be able to connect to the SQL Azure Server.

 

Posted in Azure | Tagged: , , , , , , | Leave a Comment »

SQL Azure Services – A full overview

Posted by Alin D on May 11, 2011

SQL Azure is a database service in the cloud on Microsoft’s Windows Azure platform well-suited for web facing database applications as well as a relational database in the cloud.

The present version mostly deals with the component analogous to a database engine in a local, on-site SQL Server. Future enhancements will host the other services such as Integration Services, Reporting Services, Service Broker, and any other yet-to-be defined services. Although these services are not hosted in the cloud, they can leverage data on SQL Azure to provide support. SQL Server Integration Services can be used to a great advantage with SQL Azure for data movement, and highly interactive boardroom quality reports can be generated using SQL Azure as a backend server.

Infrastructure features

SQL Azure is designed for peak workloads by failover clustering, load balancing, replication, and  scaling out, which are all automatically managed at the data center. SQL Azure’s infrastructure architecture is fashioned to implement all of these features.

High availability is made possible by replicating multiple redundant copies to multiple physical servers thus, ensuring the business process can continue without interruption. At least three replicas are created; a replica can replace an active copy facing any kind of fault condition so that service is assured. At present, the replicated copies are all on the same data center, but in the future, geo-replication of data may become available so that performance for global enterprises may be  improved. Hardware failures are addressed by automatic failover.

Enterprise data centers addressed the scaling out data storage needs, but incurred administrative overheads in maintaining the on-site SQL Servers. SQL Azure offers the same or even better functionality without incurring administrative costs.

How different is SQL Azure from SQL Server?

SQL Azure (version 10.25) may be viewed as a subset of an on-site SQL Server 2008 (version 10.5) both exposing Tabular Data Stream (TDS) for data access using T-SQL. As a subset, SQL Azure supports only some of the features of SQL Server and the T-SQL feature set. However, more of T-SQL features are being added in the continuous upgrades from SU1 to SU5. Since it is hosted on computers in the Microsoft Data Centers, its administration—in some aspects—is different from that of an on-site SQL Server.

SQL Azure is administered as a service, unlike on-site servers. The SQL Azure server is not a SQL Server instance and is therefore administered as a logical server rather than as a physical server. The database objects such as tables, views, users, and so on are administered by SQL Azure database administrator but the physical side of it is administered by Microsoft on its data centers. This abstraction of infrastructure away from the user confers most of its availability, elasticity, price, and extensibility features. To get started with SQL Azure, you must provision a SQL Azure Server on Windows Azure platform as explained in the After accessing the portal subsection, later in the article.

SQL Azure provisioning

Provisioning a SQL Azure Server at the portal is done by a mere click of the mouse and will be ready in a few minutes. You may provision the storage that you need, and when the need changes, you can add or remove storage. This is an extremely attractive feature especially for those whose needs start with low storage requirements and grow with time. It is also attractive to those who may experience increased load at certain times only.

SQL Azure databases lie within the operational boundary of the customer-defined SQL Azure Server; it is a container of logical groupings of databases enclosed in a security firewall fence. While the databases are accessible to the user, the files that store the relational data are not; they are managed by the SQL Azure services.

A single SQL Azure Server that you get when you subscribe, can house a large number (150) of databases, presently limited to the 1 GB and 10 GB types within the scope of the licensing arrangement.

• What if you provision for 1 GB and you exceed this limit?

Then either you provision a server with a 10 GB database or get one more 1 GB database. This means that there is a bit of due diligence you need to do before you start your project.

• What if the data exceeds 10 GB?

The recommendation is to partition the data into smaller databases. You may have to redesign your queries to address the changed schema as cross-data­base queries are not supported. The rationale for using smaller databases and partitioning, lies in its agility to quickly recover from failures (high availabil­ity/fault tolerance) with the ability to replicate faster while addressing the issue of covering a majority of users (small business and web facing). How­ever, responding to the requests of the users, Microsoft may provide 50 GB databases in the future (the new update in June 2010 to SQL Azure Services will allow 50 GB databases).

• How many numbers of SQL Azure Servers can you have?

You can have any number of SQL Azure Servers (that you can afford) and place them in any geolocation you choose. It is strictly one server for one subscription. Presently there are six geolocated data centers that can be chosen. The number of data centers is likely to grow. Best practices dictate that you keep your data nearest to where you use it most, so that performance is optimized. The SQL Azure databases, being relational in nature, can be programmed using T-SQL skills that are used in working with on-site SQL Servers. It must be remembered though, that the SQL Azure Servers are not physical servers but are virtual objects. Hiding their physical whereabouts but providing adequate hooks to them, helps you to focus more on the design and less on being concerned with files, folders, and hardware problems. While the server-related information is shielded from the user, the databases themselves are containers of objects similar to what one finds in on-site SQL Servers such as tables, views, stored procedures, and so on. These database objects are accessible to logged on users who have permission.

After accessing the portal

To get started with SQL Azure Services, you will need to get a Windows Azure platform account, which gives access to the three services presently offered. The first step is to get a Windows Live ID and then establish an account at Microsoft’s Customer Portal. In this article, you will be provisioning a SQL Azure Server after accessing the SQL Azure Portal.

Server-level administration

Once you are in the portal, you will be able to create your server for which you can provide a username and password. You will also be able to drop the server and change the password. You can also designate in which of the data centers you want your server to be located. With the credentials created in the portal, you will become the server-level principal; the equivalent of sa of your server. In the portal, you can also create databases and firewall fences that will only allow users from the location(s) you specify here. The user databases that you create here are in addition to the master database that is created by SQL Azure Services; a repository of information about other databases. The master database also keeps track of logins and their permissions. You could get this information by querying the master for sys.sql_logins and sys.database views.

If you are planning to create applications, you may also copy the connection strings that you would need for your applications, which are available in the portal. You would be typically using the Visual Studio IDE to create applications. However, SQL Azure can be used standalone without having to use the Windows Azure service. Indeed some users may just move their data to SQL Azure for archive.

Once you have provisioned a server, you are ready to create other objects that are needed besides creating the databases. At the portal, you can create a database and set up a firewall fence, but you will need another tool to create other objects in the database.

Setting up firewall rules

Users accessing SQL Azure Server in the Cloud need to go through two kinds of barriers. Firstly, you need to go through your computer’s firewall and then go in through the firewall that protects your SQL Azure Server. The firewall rules that you set up in the portal allow only users from the location you set up for the rule, because the firewall rules only look at the originating IP address.

By default, there are no firewall rules to start with and no one gets admitted. Firewall rules are first configured in the portal. If your computer is behind a Network Address Translation (NAT) then your IP address will be different from what you see in your configuration settings. However, the user interface in the portal for creating a firewall discovers and displays the correct IP address most of the time.

A workaround is suggested here for those cases in which your firewall UI incorrectly displays your IP Address: http://hodentek.blogspot.com/2010/01/firewall-ip-address-setting-in-sql.html.

Firewalls can also be managed from a tool such as SSMS using extended stored procedures in SQL Azure. They can be managed programmatically as well from Visual Studio.

In order for you to connect to SQL Azure, you also need to open your computer’s firewall, so that an outgoing TCP connection is allowed through port 1433 by creating an exception. You can configure this in your computer’s Control Panel. If you have set up some security program, such as Norton Security, you need to open this port for outgoing TCP connections in the Norton Security Suite’s UI.

In addition, your on-site programs accessing SQL Azure Server and your hosted applications on Windows Azure may also need access to SQL Azure. For this scenario, you should check the checkbox Allow Microsoft Services access to this server in the firewall settings page.

The firewall rule only checks for an originating IP address but you need to be authenticated to access SQL Azure. Your administrator, in this case the server-level principal, will have to set you up as a user and provide you with appropriate credentials.

Administering at the database level

SQL Azure database administration is best done from SSMS. You connect to the Database Engine in SSMS, which displays a user interface where you enter the credentials that you established in the portal. You also have other options to connect to SQL Azure (Chapter 3, Working with SQL Azure Databases from Visual Studio 2010 and Chapter 4, SQL Azure Tools). In SSMS, you have the option to connect to either of the databases, the system-created master or the database(s) that you create in the portal. The Object Explorer displays the server with all objects that are contained in the chosen database. What is displayed in the Object Explorer is contextual and the use of the USE statement to change the database context does not work. Make sure you understand this, whether you are working with Object Explorer or query windows. The server-level administrator is the ‘top’ administrator and he or she can create other users and assign them to different roles just like in the on-site SQL Server. The one thing that an administrator cannot do is undertake any activity that would require access to the hardware or the file system.

Role of SQL Azure database administrator

The SQL Azure database administrator administers and manages schema generation, statistics management, index tuning, query optimization, as well as security (users, logins, roles, and so on). Since the physical file system cannot be accessed by the user, tasks such as backing up and restoring databases are not possible. Looking at questions and concerns raised by users in forums, this appears to be one of the less appealing features of SQL Azure that has often resulted in remarks that ‘it is not enterprise ready’. Users want to keep a copy of the data, and if it is a very large database, the advantages of not having servers on the site disappear as you do need a server on-site to back up the data. One suggested recommendation by Microsoft is to use SQL Server Integration Services and bulk copying of data using the SQLCMD utility.

SQL Azure databases

These databases are no different from those of on-site SQL Server 2008 except that the user database node may not have all the nodes of a typical user database that you find in the on-site server. The nodes Database Diagrams, Service Broker, and Storage will be absent as these are not supported. In the case of the system database node, only the master will be present. The master in SQL Azure is a database that contains all information about the other databases.

You can only access the SQL Server with SQL Server Authentication, whereas you have an additional option, Windows Authentication in the case of an on-site SQL Server. All the allowed DDL, DML operations can be programmed using templates available in SSMS. Some of the more common ones, as well as access to the template explorer, which provides a more complete list, are detailed later in the chapter.

User administration and logins

Security is a very important aspect of database administration and it is all the more important in the case of the multi-tenant model used in hosting SQL Azure to control access.

The server-level administrator created in the portal is the top level administrator of SQL Azure Server. While he  can create other databases in the portal, he will have to create other database objects including users and their login, using the SSMS.

Server-level administration

The master database is used to perform server-level administration, as the master database keeps records of all logins and of the logins that have permission to create a database. You must first establish a connection to the master database while creating a New Query to carry out tasks to CREATE, ALTER, or DROP LOGINS or DATABASES. The server-related views: sys.sql_logins and sys.databases can be used to review logins and databases. Whenever you want to change the context of a database, you have to login to the database using the Options in the SSMSs UI, Connect to Server.

Creating a database using T-SQL is extremely simple as there are no file references to be specified and certain other features that are not implemented. The following syntax is for creating a database in an on-site SQL Server instance:

CREATE DATABASE database_name

[ON

[ PRIMARY ] [ <filespec> [ ,...n ]

[ , <filegroup> [ ,...n ] ]

[ LOG ON { <filespec> [ ,...n ] } ]

]

[ COLLATE collation_name ]

[ WITH <external_access_option> ]

]

[;]

To attach a database

CREATE DATABASE database_name

ON <filespec> [ ,...n ]

FOR { ATTACH [ WITH <service_broker_option> ]

| ATTACH_REBUILD_LOG }

[;]

<filespec> ::=

{

(

NAME = logical_file_name ,

FILENAME = { ‘os_file_name’ | ‘filestream_path’ }

[ , SIZE = size [ KB | MB | GB | TB ] ]

[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]

[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ]

) [ ,...n ]

}

<filegroup> ::=

{

FILEGROUP filegroup_name [ CONTAINS FILESTREAM ] [ DEFAULT ]

<filespec> [ ,...n ]

}

<external_access_option> ::=

{

[ DB_CHAINING { ON | OFF } ]

[ , TRUSTWORTHY { ON | OFF } ]

}

<service_broker_option> ::=

{

ENABLE_BROKER

| NEW_BROKER

| ERROR_BROKER_CONVERSATIONS

}

Create a database snapshot

CREATE DATABASE database_snapshot_name

ON

(

NAME = logical_file_name,

FILENAME = ‘os_file_name’

) [ ,...n ]

AS SNAPSHOT OF source_database_name

[;]

Look how simple the following syntax is for creating a database in SQL Azure:

CREATE DATABASE database_name

[(MAXSIZE = {1 | 10} GB )]

[;]

However, certain default values are set for the databases, which can be reviewed by issuing the query after creating the database:

SELECT * from sys.databases

Managing logins

After logging in as a server-level administrator to master, you can manage logins using CREATE LOGIN, ALTER LOGIN, and DROP LOGIN statements. You can create a password by executing the following statement for example, while connected to master:

CREATE LOGIN xfiles WITH PASSWORD = ‘@#$jAyRa1′

You need to create a password before you proceed further. During authentication, you will normally be using Login Name and Password, but due to the fact that some tools implement TDS differently, you may have to append the servername part of the fully qualified server name <servername>.<database name>.<windows>.<net> to the Username like in login_name@<servername>. Note that both <login_name> and <login_name>@<servername> are valid in the Connect to Server UI of SSMS.

Connecting to SQL Azure using new login

After creating a new login as described here, you must confer database-level permissions to the new login to get connected to SQL Azure. You can do so by creating users for the database with the login.

Logins with server-level permissions

The roles loginmanager and dbmanager are two security-related roles in SQL Azure to which users may be assigned, that allows them to create logins or create databases. Only the server-level principal (created in the portal) or users with loginmanager role can create logins. The dbmanager role is similar to the dbcreator role and users in this role can create databases using the CREATE DATABASE statement while connected to the master database.

These role assignments are made using the stored procedure sp_addrolemember as shown here for users, user1 and user2. These users are created while connected to master using, for example:

CREATE USER User1 FROM LOGIN ‘login1′;

CREATE USER User2 FROM LOGIN ‘login1′;

EXEC sp_addrolemember ‘dbmanager’, ‘User1′;

EXEC sp_addrolemember ‘loginmanager’, ‘User2′;

Migrating databases to SQL Azure

As most web applications are data-centric, SQL Azure’s databases need to be populated with data before the applications can access the data. More often, if you are trying to push all of your data to SQL Azure, you need tools. You have several options, such as using scripts, migration wizard, bulk copy (bcp.exe), SQL Server Integration Services, and so on. More recently (April 19, 2010 update) Data-tier applications were implemented for SQL Azure providing yet another option for migrating databases using both SSMS as well as Visual Studio.

Troubleshooting

There may be any number of reasons why interacting with SQL Azure may not always be successful. For example, there may just be a possibility that the service level agreement that assures 99.99 percent may not actually be possible, there may be a problem of time-out that is set for executing a command, and so on. In these cases, troubleshooting to find out what might have happened becomes important. Herein, we will see some of the cases that prevent interacting with SQL Azure and the ways and means of troubleshooting the causes.

• Login failure is one of the common problems that one faces in connecting to SQL Azure. In order to successfully login:

°°You need to make sure that you are using the correct SSMS.

°°Make sure you are using SQL Server Authentication in the Connect to Server dialog box.

°°You must make sure your login name and password (type in exactly as you were given by your administrator) are correct. Password is case sensitive. Sometimes you may need to append server name to login name.

°°If you cannot browse the databases, you can type in the name and try.

If your login is not successful, either there is a problem in the login or the database is not available.

If you are a server-level administrator you can reset the password in the portal. For other users the administrator or loginmanager can correct the logins.

• Service unavailable or does not exist.

If you have already provisioned a server, check the following link: http:// http://www.microsoft.com/windowsazure/support/status/servicedashboard. aspx, to make sure SQL Azure Services are running without problem at the data center.

Use the same techniques that you would use in the case of SQL Server 2008 with network commands like Ping, Tracert, and so on. Use the fully qualified name of the SQL Azure Server you have provisioned while using these utilities.

• You assume you are connected, but maybe you are disconnected.

You may be in a disconnected state for a number of reasons, such as:

°°When a connection is idle for an extended period of time

°°When a connection consumes an excessive amount of resources or holds onto a transaction for an extended period of time

°°If the server is too busy

Try reconnecting again. Note that SQL Azure error messages are a subset of SQL error messages.

T-SQL support in SQL Azure

Transact-SQL is used to administer SQL Azure. You can create and manage objects as you will see later in this chapter. CRUD (create, read, update, delete) operations on the table are supported. Applications can insert, retrieve, modify, and delete data by interacting with SQL Azure using T-SQL statements.

As a subset of SQL Server 2008, SQL Azure supports only a subset of T-SQL that you find in SQL Server 2008.

The supported and partially supported features from Microsoft documentation are reproduced here for easy reference.

The support for Transact-SQL reference in SQL Azure can be described in three main categories:

• Transact-SQL language elements that are supported as is

• Transact-SQL language elements that are not supported

• Transact-SQL language elements that provide a subset of the arguments and options in their corresponding Transact-SQL elements in SQL Server 2008

The following Transact-SQL features are supported or partially supported by SQL Azure:

• Constants

• Constraints

• Cursors

• Index management and rebuilding indexes

• Local temporary tables

• Reserved keywords

• Stored procedures

• Statistics management

• Transactions

• Triggers

• Tables, joins, and table variables

• Transact-SQL language elements

• Create/drop databases

• Create/alter/drop tables

• Create/alter/drop users and logins

• User-defined functions

• Views

The following Transact-SQL features are not supported by SQL Azure:

• Common Language Runtime (CLR)

• Database file placement

• Database mirroring

• Distributed queries

• Distributed transactions

• Filegroup management

• Global temporary tables

• Spatial data and indexes

• SQL Server configuration options

• SQL Server Service Broker

• System tables

• Trace flags

T-SQL grammar details are found here: http://msdn.microsoft.com/en-us/ library/ee336281.aspx.

 

Posted in Azure | Tagged: , , , , , , | 1 Comment »

SQL Azure Performance – Query Optimization

Posted by Alin D on March 16, 2011

SQL Azure Performance Tuning is a much tougher task than SQL Server, – SQL Azure lacks some of the basic tools available to SQL Server DBA’s. There really isn’t too much to do tuning your SQL Azure instance as almost all the settings available for SQL Server are automatically managed by the SQL Azure platform. However, there is still some performance tuning to be done – mostly in the execution of the operations performed by the database. A major tool missing from SQL Azure is the SQL Server Profiler, despite this however there is still some optimization that can be performed on your SQL Azure queries – you can do this by either using SSMS or SET STASTICS.

SQL Azure Query Optimization Using SSMS

The first step to query optimization is determining the efficiency of the query. In SSMS, the Execution Plan graphically shows the cost of excuting a query. To see the Execution Plan, first toggle on “Include Actual Execution Plan” by selecting Query >  Include Actual Execution Plan or from the toolbar as shown below:

SQL Azure Performance

Then when you execute the query, you will also be presented with an Execution Plan tab besides the Results and Messages tabs:

SQL Azure Performance

A good resource on reading Execution Plans is the MSDN resource here.

In the above query I have made the most basic mistake – reading more data than will be required, it is rare we will need to return all the columns in a table, it’s also rare that we will need every row in a table (so always use WHERE to minimize the rows that are read) .

SQL Azure Query Optimization Using SETSTASTICS

The TSQL SETSTATISTICS command will monitor the execution of a query and then provide statistics on either the time taken to execute the query or the I/O expense of the query.

To test the time of the query execution use the Command SET STATISTICS TIME ONE at the start of a query. This will then output the parse, compile and execution times for the query to the Messages tab:

SQL Azure Performance Query

To test the I/O expense of the query execution use the Command SET STATISTICS IO ONE at the start of a query. This will then output the IO performance for the query to the Messages tab:

SQL Azure Performance Query

Posted in Azure | Tagged: , | 1 Comment »

Virtual Machine role in Windows Azure

Posted by Alin D on February 17, 2011

Windows Azure now includes a Virtual Machine role that allows organizations to host their own virtual machines in the cloud.

Microsoft introduced this role as a way to ease the migration of applications to cloud computing. So instead of waiting until your code is “cloud-ready”, you can use this role to move applications to the cloud while refactoring old code.

Where the VM role fits within Azure
Windows Azure currently has three roles: Web, Worker and Virtual Machine (VM). The Web role is used for web application programming on Internet Information Services (IIS) 7.0, while the Worker role is basically for any type of process that runs in the background with a front-end interface.

The VM role is the newbie, and it uses a virtual hard disk (VHD) image of a Windows 2008 R2 server. The image is created internally on your network using Hyper-V technology and then uploaded to Windows Azure. This image can be customized and configured to run whatever software you would like to run in the cloud.

Before pushing virtual machines out to the cloud, however, it’s important to understand the pricing, licensing and perquisites involved. Any instance of a VM role is priced by the compute hour, and licensing of the role is included in the cost (see the table below).

Compute Instance Size CPU Memory Instance storage I/O performance Cost per hour
Extra small 1.0 GHz 768 MB 20 GB Low $0.05
Small 1.6 GHz 1.75 GB 225 GB Moderate $0.12
Medium 2 x 1.6 GHz 3.5 GB 490GB High $0.24
Large 4 x 1.6 GHz 7 GB 1,000 GB High $0.48
Extra large 8 x 1.6 GHz 14 GB 2, 040 GB High $0.96

Note:This same table can be found on Microsoft’s Azure Compute page.

Note:This same table can be found on Microsoft’s Azure Compute page.

All virtual machines are created using Hyper-V Manager on a Windows Server 2008 operating system, where R2 is recommended. You’ll also find that Hyper-V, IIS 7.0, Windows Azure SDK, ASP.NET are all required, with an optional install of Visual Studio 2010 also available. (More requirements for the Azure VM role are listed on MSDN.)

Where the VM role makes sense

So why would you want to implement the VM role? Well, let’s say you’ve done your due diligence and decided on Windows Azure as your cloud platform of choice. You are ready to move forward but have a lot of existing legacy applications that are written differently and may not work on the Azure platform. A rewrite of this code could have a lengthy roadmap even if you are utilizing agile programming. In my opinion, this is where the VM role should be used.

The VM role gives you complete control over the system where your code runs, so while you are rewriting code to work in Azure, you could also create and deploy customized VHD images to the cloud immediately. In other words, the VM role can be used to migrate an on-premise application to run as a service in the Windows Azure cloud.

Another ideal time to implement the VM role is when you aren’t sure if you want stay with Windows Azure for the long-term. What if you decide to change vendors? Windows Azure is a Platform as a Service (PaaS), which is simply a framework for developers to create applications and store data.

Basically, once you develop your product for Windows Azure, it runs on Windows Azure. But if your company takes a new direction and wants to leverage a different cloud platform from Amazon or VMware, guess what? You’ll have to recode because you won’t be able to move that application. The VM role acts as a bridge that connects PaaS with Infrastructure as a Service (IaaS); it gives Microsoft an IaaS platform and provides you with the portability to move vendors if a change of direction is needed.

When not to use the Azure VM role
While the use cases above make sense, to me a VM role in the cloud doesn’t seem like the best option for the long-term. For starters, if you push virtual machines to the cloud, you need good speeds to upload. So the bigger the VM, the longer that upload process will take. Secondly, Microsoft doesn’t maintain your virtual machines for you; you are responsible for patching and uploading the changes as a differencing disk.

When you look at it that way, maintaining a VM role for an extended period of time seems like a nightmare. Not only could the uptake be tremendous, but differencing disks are not my favorite virtual machine technology anyway as they are prone to corruption. Snapshot technology is much easier to deal with.

So while the Windows Azure VM role is good to have in the Azure platform, in my opinion it’s not a great long-term PaaS solution. What it can do is help bridge the gap while you are busy coding for a true Platform as a Service.

Posted in Azure | Tagged: , , , , , , | 1 Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 167 other followers

%d bloggers like this: