This guide shows you how to create a logical server and database instance on Azure SQL Database and connect to the database using the following .NET Framework data provider technologies: ADO.NET, ODBC, and EntityClient Provider.

What is SQL Database

SQL Database provides a relational database management system for Azure and is based on SQL Server technology. With a SQL Database instance, you can easily provision and deploy relational database solutions to the cloud, and take advantage of a distributed data center that provides enterprise-class availability, scalability, and security with the benefits of built-in data protection and self-healing.

The MAXSIZE is specified when the database is first created and can later be changed using ALTER DATABASE. MAXSIZE provides the ability to limit the size of the database.

For each SQL database created on Azure, there are actually three replicas of that database. This is done to ensure high availability. Failover is transparent and part of the service. The Service Level Agreement provides 99.9% uptime for SQL Database.

In Server, select New SQL Database Server.

Click the arrow to go on to the next page.

In Server Settings, enter a SQL Server authentication login name.

SQL Database uses SQL Authentication over an encrypted connection. A new SQL Server authentication login assigned to the sysadmin fixed server role will be created using the name you provide.

The login cannot be an email address, Windows user account, or a Windows Live ID. Neither Claims nor Windows authentication is supported on SQL Database.

Provide a strong password that is over eight characters, using a combination of upper and lower case values, and a number or symbol.

Choose a region. Region determines the geographical location of the server. Regions cannot be easily switched, so choose one that makes sense for this server. Choose a location that is closest to you. Keeping your Azure application and database in the same region saves you on egress bandwidth cost and data latency.

Be sure to keep the Allow Azure Services to access the server option selected so that you can connect to this database using the Management Portal for SQL Database, storage services, and other services on Azure.

Click the checkmark at the bottom of the page when you are finished.

Notice that you did not specify a server name. SQL Database auto-generates the server name to ensure there are no duplicate DNS entries. The server name is a ten-character alphanumeric string. You cannot change the name of your SQL Database server.

After the database is created, click on it to open its dashboard. The dashboard provides connection strings that you can copy and use in application code. It also shows the management URL that you'll need to specify if you are connecting to the database from Management Studio or other administrative tool.

In the next step, you will configure the firewall so that connections from applications running on your network are allowed access.

Configure the firewall for the logical server

Click SQL Databases, click Servers at the top of the page, and then click on the server you just created.

Click Configure.

Copy the current client IP address. If you are connecting from a network, this is the IP address that your router or proxy server is listening on. SQL Database detects the IP address used by the current connection so that you can create a firewall rule to accept connection requests from this device.

Paste the IP address into both the START IP ADDRESS and END IP ADDRESS to establish the range addresses that are allowed to access the server. Later, if you encounter connection errors indicating that the range is too narrow, you can edit this rule to widen the range.

If client computers use dynamically assigned IP addresses, you must specify a range that is broad enough to include IP addresses assigned to computers in your network. Start with a narrow range, and then expand it only if you need to.

Enter a name for the firewall rule, such as the name of your computer or company.

Click the checkmark next to the rule to save it.

Click Save at the bottom of the page to complete the step. If you do not see Save, refresh the browser page.

You now have a database instance, logical server, a firewall rule that allows inbound connections from your IP address, and an administrator login. You are now ready to connect to the database programmatically.

Connect to SQL Database

This section shows how to connect to SQL Database instance using different .NET Framework data providers.

If you choose to use Visual Studio and your configuration doesn't include an Azure web application as a front-end, there are no additional tools or SDKs needed to be installed on the development computer. You can just start developing your application.

You can use all of the same designer tools in Visual Studio to work with SQL Database as you can to work with SQL Server. The Server Explorer allows you to view (but not edit) database objects. The Visual Studio Entity Data Model Designer is fully functional and you can use it to create models against SQL Database for working with Entity Framework.

If the elements of a connection string are known ahead of time, they can be stored in a configuration file and retrieved at run time to construct a connection string. Here is a sample connection string in configuration file:

To retrieve the connection string in a configuration file, you use the ConfigurationManager class:

SqlConnectionStringBuilder csBuilder;
csBuilder = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
After you have built your connection string, you can use the SQLConnection class to connect the SQL Database server:
SqlConnection conn = new SqlConnection(csBuilder.ToString());
conn.Open();

Using .NET Framework Data Provider for ODBC

The System.Data.Odbc namespace is the.NET Framework Data Provider for ODBC. The following is a sample ODBC connection string:

If you want to build the connection string on the runtime, you can use the OdbcConnectionStringBuilder class.

Using EntityClient Provider

The System.Data.EntityClient namespace is the .NET Framework Data Provider for the Entity Framework.

The Entity Framework enables developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The Entity Framework builds on top of storage-specific ADO.NET data providers by providing an EntityConnection to an underlying data provider and relational database.

To construct an EntityConnection object, you have to reference a set of metadata that contains the necessary models and mapping, and also a storage-specific data provider name and connection string. After the EntityConnection is in place, entities can be accessed through the classes generated from the conceptual model.