Visual Studio Database Project and SQL Azure

Question

I'm really struggling with the best approach for managing a SQL Azure database which is based on a Visual Studio 2010 Database Project. I figured it would be easy enough to use VSDBCMD to create diff scripts for upgrades and then simply run against SQL Azure in SSMS. However, I get the dreaded "The target database schema provider could not be determined. Deployment cannot continue." error.

At this point I'm just assuming either SQL Azure doesn't support something in VSDBCMD or vice versa and I'm looking at other approaches. Here's the approach I'm currently considering:

Script my SQL Azure database from SSMS using the Azure database engine setting.
Create a tempory local database from script in step 1.
Use VSDBCMD to create my delta script against the local database from step 2.
Review/modify the script from step 3.
Run script from step 3/4 against SQL Azure in SSMS.

Good, bad? Any other ideas?

EDIT: I updated to Visual Studio 2010 SP1 today and found that there is a new database schema provider in Microsoft.Data.Schema.Sql.dll: SqlAzureDatabaseSchemaProvider. However, I cannot determine how to actually use this bad boy. Now when I try the same VSDBCMD script against an Azure database, I get:

The source database schema provider Sql100DatabaseSchemaProvider could not be translated to provider SqlAzureDatabaseSchemaProvider. Deployment cannot continue.

I also tried using the Sql90 provider with the same outcome. I even manually edited the dbproj file and changed the DSP property to SqlAzureDatabaseSchemaProvider. When I reload the project I get:

The database schema provider must provide an implementation of DataGenerationServices.

Has anyone tried this with VS 2010 SP1?

score 3 · Answer 1 · answered Mar 24 '11 at 09:21

3

You can use the SQL Azure Migration Wizard and play with it. Maybe you can get the scripts and edit them as you want. Just an idea.

answered Mar 24 '11 at 09:21

vtortola

34,709
29
161
263

1

+1 for this suggestion - it's a great tool and can be run as a UI or from the command line. – Stuart Mar 24 '11 at 20:05
1

the source code is also available - so you should probably be able to adapt it to your deltas if you want to. – Stuart Mar 24 '11 at 20:05

score 1 · Answer 2 · answered Mar 24 '11 at 15:35

1

Check out the properties page of the database project. It should show a dropdown containing the various available providers for the project. If you select the Azure provider you should be able to deploy the project as part of your Azure application.

answered Mar 24 '11 at 15:35

Willem Meints

1,152
14
33

I do not see Azure as an option for my database project, I only see 2005, 2008, and DAC for project type options. To clarify, do you have a DAC project or database project? I have a database project. – toddkitta Mar 24 '11 at 15:49
I asked someone on my team and he told me the setting was there before VS2010 SP1, but it dissapeared afterwards. I think the underlying engine automatically determines what to do with your database project when you build an Azure application package. – Willem Meints Apr 23 '11 at 17:12

Pete Maroun · Answer 3 · 2011-05-05T12:29:51.480

A simple command line tool that replaces invalid SQL Azure statements is all that is needed:

    private const string STARTPLACEHOLDER = "AZURESCRIPTSTARTPLACEHOLDER";

    public static void Do(string fileName)
    {
        // Read the original file
        StringBuilder script = new StringBuilder();
        using (StreamReader reader = new StreamReader(fileName))
        {
            script.Append(reader.ReadToEnd());
        }

        // Remove everything before the start placeholder
        int startPlaceHolder = script.ToString().IndexOf(STARTPLACEHOLDER, 0);
        if (startPlaceHolder > 0)
        {
            script.Remove(0, startPlaceHolder + STARTPLACEHOLDER.Length);
        }

        // Remove WITH clause
        script.Replace("WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF)", string.Empty);

        // Create azure compatible output file
        using (StreamWriter writer = new StreamWriter(Path.Combine(Path.GetDirectoryName(fileName), Path.GetFileNameWithoutExtension(fileName) + "_Azure" + Path.GetExtension(fileName))))
        {
            writer.Write(script.ToString());
        }
    }

Visual Studio Database Project and SQL Azure

3 Answers3

Linked