Home » Azure

Upgrade SQL Azure database schema while in production

The question that I currently have: Is it possible to upgrade schemas [ Add tables/fields/indexes…] in SQLAzure while the database is in use and what are the risks in doing this?

Some of our datatables have quite a lot of records (100million and up)


1 Answer Found


Answer 1

Hi Rudi,

I think SQL Azure and SQL Server should have the same behavior. For SQL Server, when modifying a table use the ALTER TABLE statement, a schema  modify lock is acquired on the table to block other connections.

For more information, please see the following document (the Locks and ALTER TABLE section):



I'm currently in the middle of developing an application loosely similar to this forum (question / answer based)

Initially, I was using AWS (simple db and s3) for storage, however that posed several problems, and i've opted to go with SQL - With a view to eventually hosting on SQL Azure.

I was just wondering, if i opted for the 1gb database initially, could I increase the size to the 5gb / 10gb databases later on?
Or would I have to create a separate new database of this size, and port my data over?

Can I upgrade SQL express database to SQL server Standard database? Please give information to me! Thank you!

I hope this is the correct forum....

This is related to SQL 2005 (using Pro edition)

I have two database schema's....verson 1.0 (older sql file) and 2.0 (updated sql file with some new tables, stored procedures, etc).

Besides doing a line-by-line compare & merge (which I have been doing, very slowly and will just take a very long, tedious time, using just the SQL file alone), how can upgrade the DB schema - maybe even while the version 1.0 is in production and has data populated in the tables?  If that's even possible.  My goal is to upgrade the 1.0 to the 2.0 without destroying the data that exists already.

Any help is greatly appreciated.

Thank you.


Hey everyone,

I am running an upgrade from SQL7 to SQL2005.  Have a couple questions related to user login name and Schema.

When I do a restore, the database user login name is empty and I do not know how to fill this in.  It's grayed out in the studio manager.  I can delete this user and recreate it and enter in the login name then I have access to my database.  Without this login name, I get a an error of failed login.

The problem is, the schema is deleted with the user and I am not sure if I can just recreate the schema after the migrate or any problems we may run into in the future.

We are very new to SQL and wondering what the schema is used for and what data is stored in it.  The database seems to run fine with dbo as the default schema, but again not sure if we may run into problems in the future.

Any advice or assurances are welcome!


I have two databses on a SQL Server -- one for development (call it "TestData"), and one for production (call it "LiveData"). I make changes to TestData -- typically adding tables or adding new fields to existing tables (rarely dropping anything) and creating or modifying Stored Procedures. At some point, I would like to update the LiveData tables, stored procedures, etc. with the changes made to TestData. I only want this to affect the schema, not the actual data. What is the best way to do this? I am new to SQL Server, so the more detailed the explanation, the better.

edit: I know there are third-party programs out there, but I'm looking into ways to do this without a separate software, just using scripts, etc.


This works with sql server:


With sql azure I get this error:

Msg 15247, Level 16, State 1, Line 1
User does not have permission to perform this action.
Msg 2759, Level 16, State 0, Line 1
CREATE SCHEMA failed due to previous errors.


Can anyone point me to a utility/tool that will facilitate the migration of a database from Sybase to Microsoft. 


Thanks in advance for your help.

How to migrate SQL Azure database to local database?

Hi all,

I need to migrate my data from sql server to sql azure and from a sql azure to another sql azure. in both cases, data must be migrated directly without being exported to files.

bcp or bulkcopy can migrate data but data will be exported to files before copied to sql azure database. 

Are there any ways to do those tasks?

Can SSIS do so? How does SSIS actually do? Queries all data from source database to memory then copy it to destination database?


Hi folks,

I am new to SQL Azure.

We would like to deploy our database with data (now in SQL Server 2005) to SQL Azure.

As I understand from the link below, I can use the tool SQL Azure Migration Wizard.


But it also mentioned about "run the SQL Server 2008 Upgrade Advisor". Does this mean I can only import the dabase if my SQL Server 2005 DB passed the 2008 Upgrade Advisor check?


I don't have an Azure account for test now. So I can only count on your answers for this question for now.

Thank you,




I have a database in SQL Server 2005 with Personalization tables. I am using personalization features from .NET Framework 2.0 to save the tabs of "Menu" control in aspnet_Profile table, which stored the values in image format in PropertyValuesBinary column.

Now, when i upgraded my SQL Server 2005 to SQL Server 2008 and restored the database with personalization tables, which was backed up in SQL Server 2005. I am not getting all the tabs of my Menu control.

When I compared the value in "aspnet_Profile.PropertyValuesBinary" column, I found it is different than which was in SQL Server 2005.

Can anyone guide me how can I upgrade my SQL server 2005 to SQL Server 2008 without loosing values of Personalization tables?




This morning I initiated an upgrade of my Windows Azure production application (directly, not by swapping with staging).  The portal is showing "Production: Automatic Upgrade in Progress" "Upgrading Domain 1 of 1" for around three hours so far.  The progress bar is at the half-way mark.  The ProjectId (from the querystring) is cbc118a9-e969-4502-9917-2080176046e4.  It doesn't make any difference to close the browser and re-login, the status is the same.  I have the Affinity Group Geographic Location set to 'Anywhere Asia'.  An attempt to Delete the service produces:

The service is not ready for deletion in its current state: The service still has deployed tenants. Please delete them first.

We need the ability to force a reset of deployments that go wrong!



here is the error that I got. can someone please help me


The 'System.Web.Security.SqlRoleProvider' requires a database schema compatible with schema version '1'.  However, the current database schema is not compatible with this version.  You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Configuration.Provider.ProviderException: The 'System.Web.Security.SqlRoleProvider' requires a database schema compatible with schema version '1'.  However, the current database schema is not compatible with this version.  You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[ProviderException: The 'System.Web.Security.SqlRoleProvider' requires a database schema compatible with schema version '1'.  However, the current database schema is not compatible with this version.  You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.]
System.Web.Util.SecUtility.CheckSchemaVersion(ProviderBase provider, SqlConnection connection, String[] features, String version, Int32& schemaVersionCheck) +758235
System.Web.Security.SqlRoleProvider.CheckSchemaVersion(SqlConnection connection) +65
System.Web.Security.SqlRoleProvider.GetRolesForUser(String username) +771
System.Web.Security.RolePrincipal.IsInRole(String role) +272
System.Web.Configuration.AuthorizationRule.IsTheUserInAnyRole(StringCollection roles, IPrincipal principal) +120
System.Web.Configuration.AuthorizationRule.IsUserAllowed(IPrincipal user, String verb) +300
System.Web.Configuration.AuthorizationRuleCollection.IsUserAllowed(IPrincipal user, String verb) +191
System.Web.Security.UrlAuthorizationModule.IsUserAllowedToPath(HttpContext context, VirtualPath virtualPath) +219
System.Web.UI.Util.IsUserAllowedToPath(HttpContext context, VirtualPath virtualPath) +101
System.Web.SiteMapProvider.IsAccessibleToUser(HttpContext context, SiteMapNode node) +367
System.Web.SiteMapNode.IsAccessibleToUser(HttpContext context) +14
System.Web.StaticSiteMapProvider.GetChildNodes(SiteMapNode node) +348
System.Web.SiteMapNode.get_ChildNodes() +23
System.Web.SiteMapNode.get_HasChildNodes() +4
System.Web.SiteMapNode.System.Web.UI.IHierarchyData.get_HasChildren() +4
System.Web.UI.WebControls.Menu.DataBindRecursive(MenuItem node, IHierarchicalEnumerable enumerable) +4225
System.Web.UI.WebControls.Menu.DataBindItem(MenuItem item) +277
System.Web.UI.WebControls.Menu.PerformDataBinding() +117
System.Web.UI.WebControls.HierarchicalDataBoundControl.PerformSelect() +82
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.Menu.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.Menu.EnsureDataBound() +29
System.Web.UI.WebControls.Menu.OnPreRender(EventArgs e, Boolean registerScript) +21
System.Web.UI.WebControls.Menu.OnPreRender(EventArgs e) +22
System.Web.UI.Control.PreRenderRecursiveInternal() +77
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360


Database Mail relies on SQL Server Agent so is not supported in SQL Azure.
If you are using Database Mail on an existing application and you planned to move it on Azure, you need to change it or make the Database Mail system stored procedures work on SQL Azure.

I wrote a blog post on this so, if you hare looking for a solution, I hope it can be helpful to move your solution to the cloud without changing it.




Hi All,

I had a Typed polling port checking rows in SQL Server, was working fine in test environment, I deployed to production by importing MSI and bindings. Now when I enable the port I immediatly get this error


There was a failure executing the receive pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "XML disassembler" Receive Port: "WcfReceivePort_Sql_TypedPolling" URI: "mssql://SERVERNAME//DATABASENAME?InboundId=BILLS" Reason: Finding the document specification by message type "http://schemas.microsoft.com/Sql/2008/05/TypedPolling/BILLS#TypedPolling" failed. Verify the schema deployed properly.


where did I go wrong? Thanks!


Hello Every body

Please advise me the steps i am following are correct I need to improve some more...

I want upgrade from sql server 2000 to SQL Server 2008,

Plese confirm the steps

1.Setup Test SQL Server 2000 similar to Production SQLServer 2000.

2.Setup other server with SQL Server 2008.

3.Install the Upgrade advisor in SQL Server 2008 system

4.Select the  Play back(under "sql server 2000 server") in Upgrade advisor tool  and fill the SQL Server2000 Server name and authontication.

5.Click on Capture play back(take the all the backups run some top 10 queries or application on top of SQL server 2000)

6.Please in shared drive(whcih can access by the SQL Server 2008 server)

7.Now click the "setup the test server"

8.Now click the upgrade test server 2008..

9.camapre the trace files

10.View the Trace files differance.

some body help me and plese confirm those steps are correct...I am in right direction..


I get the following rule check error when attempting to run the SQL Server Upgrade process on my Vista Ultimate machine:

Code Snippet

Rule Check Result
Rule "SQL Server 2008 Feature Upgrade" failed.

The specified edition upgrade is not supported. For information about supported upgrade paths, see the SQL Server 2008 version and edition upgrade matrix in Books Online.

However, the version that I'm attempting to upgrade from is -  Standard Edition - Version 9.00.1399.06

And according to the Version and Editions Upgrades matrix, this should be possible?



I am new in SQl Azure.

I have created SQl Azure Database in Windows Azure Control Panel and the Test connection was Sucess.

And i tried connect it from Sqlserver 2008 Enterprise Edition Client (sp1) Management studio. I coud see the Master and [Test Database](My database) .

If i select [Test Database] i am getting the "Database is not Accessible" error.

Can any one help me



Liyasker Samraj K
I read in the FAQ that any number of databases can be created in SQL Azure since PDC 09. So, I log into SQL Azure and create 5 new databases with max size of 1 GB each and then I read about the pricing of SQL Azure, it says 1GB = 9.99 per month.
Here are two ways that I interpreted that statement, please tell me which (or neither) is correct.
Case One: I would end up paying  5 * 9.99 for my 5 databases + 9.99 (1GB for master)
Case Two: I pay 9.99 per month as long as all the Databases that I create are using less than 1GB collectively.

Case Two makes more sense but I need a confirmation and was unable to get answers by searching.

Thanks for the info.


It is a delight to announce the availability of the AdventureWorks Community Sample Databases for SQL Azure.  You can download the scripts and data files from CodePlexTo install the databases on your SQL Azure server just follow the directions on the release page at CodePlex or in the ReadMe.htm file which is included in the zip file.

The scripts and data files in the zip file can be used to create the data warehouse (DW) and light (LT) sample databases in a SQL Azure server without SQL Server or Visual Studio being installed on the machine performing the installation.  Currently the AdventureWorks OLTP database is not supported for SQL Azure and is not included in the zip file.


<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure