Being your own SQL Publishing Services Host

Visual Studio 2010 includes a nifty new feature where you can publish your database to a Host, directly from within the IDE. You initiate this by right-clicking on your database connection in the Server Explorer, and selecting Publish to Provider. The entire process is guided by a Wizard that is loaded with some nice options for getting your database from one place to another. But the question today is "What do I need to do in order to be my own host so I can publish to my own server?"

Note: You may want to check our our companion article that shows how to Setup a Host server for Web Deployment.  The two together make deploying web applications in Visual Studio a snap!

After some digging it became apparent that Visual Studio's database publishing wizard is written to utilize a series of web service calls. These calls define various operations that can be implemented by anyone who can write web services. After some more digging it became apparent that there is already an implementation of these services that anyone can use to provide basic database services to Visual Studio. These services are called the SQL Server Database Hosting Services, and you can download an implementation of them at the following sites:

• Microsoft Downloads: SQL Server Database Hosting Services
• CodePlex: SQL Server Hosting Toolkit

Read more after the break:

What you Need

The documentation seems out of date. In fact, the publishing date is 4/2007! But do not fear. This implementation of the publishing services is compatible with the following:

• SQL Server 2000 SP4
• SQL Server 2005 SP1
• SQL Server 2008
• IIS6 ASP.Net 2.0
• IIS7 ASP.Net 2.0
• Visual Studio 2008
• Visual Studio 2010

Installation

For most purposes you can follow the configuration guide on this page. It outlines how to install the services on various site configurations. Some important things it does not outline are:

• The code was originally intended to be for a ASP.Net 2.0 site and you should probably configure it as such (although this may not be a true requirement).
• You do not need to use HTTPS. All you need to do to support a non-HTTPS installation is to turn it off in the web.config. To turn it off, you should change the following value in the SQLPublish\web.config:

<publish timeOut="300" maxRequestLength="4096" enforceSSL="false" />

• You do not need to install this on the default website. You can install this on any site, or even host it on a site all by its own.  Keep in mind, if you change the relative paths then you will need to modify the code appropriately.
• The SQLPublish application must have Anonymous authentication Disabled.
• When invoking the services from a client, you will need to use a SQL Database account that has owner permissions. This will allow you to remotely perform the drops/add/etc.

To test your installation was successful, you should be able to hit the following URL in your browser:

https://myserver/sqlpublish/publish/service.asmx

Setting up a Sample Database

The next step is to setup a database that you plan to publish to. You can either create a new blank database or setup an existing database. In either case, all you need to to to make it available for publishing is to ensure you have a user account with owner privileges.  It is this account that you will use (or hand out) to whomever will be publishing to the database.

That's it! You are now ready to try your first publish to your very own Host.

Hitting your own Host with Visual Studio 2010 SQL Publish Wizard

Now that your SQL Server Database Hosting Services is setup and you can hit it in your browser, you are ready to try to publish a database using Visual Studio 2010. 

1. Open Visual Studio 2010
2. Define a data connection in the Server Explorer. This is the database FROM which you to copy data. So if you have a local development database that you want to publish to a shared server, this would be the local database.
3. Right-click on the connection name in Server Explorer and select Publish to Provider.
4. The Database Publishing Wizard should pop up.
5. On the first page of the wizard you will select which database you want to Publish. The one you right-clicked on to bring up the wizard should be highlighted by default, but you can change your database here if you wish. Note: Most people miss the checkbox at the bottom of this first page. If checked, you will be publishing the entire database. If you clear the checkbox, you will have options on what database objects you want to publish.
6. Click Next to save your database selection.
7. If you chose to publish only certain portions of the database (see note on step #5), then you will be presented with various pages that let you define what exactly you want to publish. We will ignore these options for now so we can concentrate on the publishing setup.
8. Click Next until you get to the Select and Output Location page.  This page has the juicy stuff.
9. On the Select an Output Location page you notice you have two options: Script to File and Publish to a shared hosting provider. We will concentrate on Publish to a shared hosting provider, so pick that radio button.
10. When you select the Publish to a shared hosting provider option, you will be able to define providers and target databases.
11. Click the More button next to Provider. This dialog allows you to define various providers. We will now setup a provider that points to your new SQL Database Publishing Services host that you setup earlier.
    • Click New to create a new provider
    • Enter a Name. This is just a descriptive name to jog your memory later. We will call ours CentralServer for simplicity.
    • Enter the web service address to your new host site. Depending where you installed the SQL Publishing Services, it should look something like:

      https:/myserver/sqlpublish/publish/service.asmx

    • The user name and password fields is a windows account that has access to the web service listed above. Remember how we removed Anonymous access to the web service? Well, this is the Windows account that will allow us to login and access the web service. Enter that information here.
      • Great! Now you need to define the database(s) that you will publish to on this server. Click New to add a database.
        • Enter the server name for the database. For most situations this will be the same server name as the webserver (myserver).
        • Enter the name of the database
        • And finally enter the user name/password of the SQL account that has privileges to modify the database. Remember how we gave owner privileges to the SQL Account in a previous step? That is the account you want to enter here.
        • Make sure you save your password, it is just so much more convenient that way.
        • Click OK to save your database information and dismiss the Add Database dialog.
      • That's it, you have defined your server and database, so click OK to dismiss the Add Provider dialog.
    • When you are brought back to the Hosting Provider selection list, select your new provider and click the Test button. This will let you know if you can hit the web service, but does not test the database portion. But that is OK, you will find out in the next step if your database is setup correctly.
    • Click OK to return to the publishing wizard.
12. When you are back at the publishing wizard, make sure your new provider and database are selected in the dropdown boxes and click Next.
13. At this point, if your database is setup incorrectly you will get an error in the wizard. If it is setup correctly you will see a Publishing Options wizard page. If you got the error, check that the SQL Account (that you defined above) has privileges to the database, as that is your most likely issue.
14. On the SQL Options page, select what you want to do. If you are pushing a new, complete database over you can take the defaults. Otherwise use whatever options make sense for your particular scenario. Click Next when you have made your selections.
15. Note: I want to take a moment here to point out that the SQL options here are nice, but in reality the operation is not very smart. If you have relational data and try to sync up data tables, you are out of luck. The Wizard is not smart enough to do "differences" and only insert the new/changed data, and you will get primary key errors. This is also true if you try to modify/drop a table that is referenced by another table. For those type of operations, you may have to ditch the Publishing Wizard altogether. Hopefully a future version will support more intelligence (or someone will make a nice plug-in to help with this!)
16. You will now be presented with a summary, and clicking Next will kick off the publishing operation.

You've done it! That is all there is to setting your own server up as a SQL Publishing Services host. Although there are some limitations in the SQL Publishing Wizard as noted above, this tutorial should get you capable of perform basic publishing to your own servers.