Visual Studio Database Project and SQL Azure

问题:

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:

1. Script my SQL Azure database from SSMS using the Azure database engine setting.
2. Create a tempory local database from script in step 1.
3. Use VSDBCMD to create my delta script against the local database from step 2.
4. Review/modify the script from step 3.
5. 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?

回答1:

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.

回答2:

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.

回答3:

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());
        }
    }