A couple of tips and tricks in getting SQL Azure and Windows Azure to work well together
The SQL Azure Firewall needs tweaking to work between these two stacks – 90% of your teething problems will be here.
Step 1) create your SQL Azure project – from the Azure Portal
Add a DB
test connectivity – it will fail – you need to add a Firewall rule – you may need to wait a few minutes for this to work
Add a DB and test connectivity with userid & password – connectivity will now succeed:
Get your connection string from the portal.
Next, you need to manage your DB – you must have SQL 2008 R2 Management Studio – note the R2
Make sure you open port 1433 outgoing on your local firewall
Specify the server name from your DB – it should be something like XXX.database.windows.net.
Use SQL Server Authentication to connect
Supprisingly, connection fails!
Its that pesky SQL Azure firewall again – you are going out on 1433 and your ISP NAT probably has a different mapping – jot down this IP and add another firewall rule to the SQL Azure portal:
Now you can connect
But note: its not your regular SQL Management Studio interface – try right click on Tables > New Table: you wont get a designer, its back to TSQL (reduced) for you!
-- =========================================
-- Create table template SQL Azure Database
-- =========================================
IF OBJECT_ID('<schema_name, sysname, dbo>.<table_name, sysname, sample_table>', 'U') IS NOT NULL
DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
GO
CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
(
<columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>,
<column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>,
<column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>,
CONSTRAINT <contraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>)
)
GO
Step 2) Create a Web Role to access the SQL Azure DB
You need a service Façade over your data – in Visual Studio, you need to create a Cloud project that contains a WCF Service Web Role
alternatively, you could also expose your SQL Azure DB via OData: https://www.sqlazurelabs.com/ConfigOData.aspx
The concept is to first test the Web Role from your local dev fabric against your cloud storage model, then publish and test from there.
Add an ADO.NET Entity Data Model – just get your connection string from SQL Azure and point to your SQL Azure DB
Add a WCF Data Service
public class WcfDataService1 : DataService< /* TODO: put your data source class name here */ >
{
// This method is called only once to initialize service-wide policies.
public static void InitializeService(DataServiceConfiguration config)
{
// TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
// Examples:
// config.SetEntitySetAccessRule("MyEntityset", EntitySetRights.AllRead);
// config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);
config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
}
}
Test locally – create a test client
var x = new ServiceReference1.CloudValueEntities(new Uri(uriProd));
try
{
var r = new Registrations()
{
CompanyName = "xxx",
EmailAddress = "XXX",
FullName = "XXX",
HowDidYou = "XXX",
PhoneNumber = "XXX"
};
x.AddToRegistrations(r);
x.SaveChanges();
Console.WriteLine("New product added with ID {0}.", r.ID);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadLine();
Or use Fiddler Request Builder to test non-GET HTTP verbs:
Step 3) Publish
From visual studio:
Specify that you want to deploy directly:
You will see this screen: you need to setup credentials
To do this, there are some prerequisites!
- You must have an Azure account
- You must have an Azure storage account and hosted service setup – if not, after you setup your credentials, then the 2nd and 3rd dropdowns above will not find a deployment target!!!
select the 1st dropdown to create your credential
Then click copy the full path link and go to the developer portal accounts to upload your certificate:
Now deployment can proceed
Note: although it is stated that this should take approx. 60 seconds, its more like 20 minutes! Bah!
Step 4) Troubleshooting
But it works on my machine!
Because you cant debug directly into a deployed web role, it's a good idea to expose a service operation that tries to do a basic version of your operation in a try catch, and returns an exception message on failure. Like so:
public string GetDataCount()
{
string retval;
try
{
using (var context = new CloudValueEntities())
{
retval = context.Registrations.Count().ToString();
}
}
catch (Exception ex)
{
retval = ex.Message;
}
return retval;
}
Check your web.config file – publishing may have mangled it. If you are exposing WCF over EF, make sure
aspNetCompatibilityEnabled="false"
and
MultipleActiveResultSets=False
Open up your SQL Azure firewall to check that this is not causing the problem – just because your local machine IP is allowed through, doesn't mean that your published web role Windows Azure host can get through!
You should see your service exposed as such:
And Bobs your uncle!