As I mentioned in my last post, we’re currently creating some guidance on deploying enterprise-scale applications. As we go along, I plan to blog about a few of the things that I find particularly tricky to figure out.
I don’t want to describe this process in any detail, you can find that elsewhere on the web (for example here).
Deploying databases in this way has advantages and disadvantages. On the plus side:
- It’s easy.
- It’s UI-driven.
- It figures out most of the settings for you.
- There’s no support for differential updates. In other words, the destination database is destroyed and recreated every time you deploy, so you’ll lose any data.
- Some of the default database deployment settings are unsuitable for many real-world scenarios – this is the issue I want to focus on here.
Suppose you’re using the Visual Studio 2010/Web Deploy approach to deploy an ASP.NET membership database from a local development machine to a destination server environment. (NB you’d typically only deploy a membership database if you’ve modified the schema, otherwise it’s easier just to run ASPNET_REGSQL.exe and create the database from scratch on the destination server). You opt for a full deployment, including schema and data from the source database. On the source database, you can see that various database roles are granted execute permissions on stored procedures:
However, when the database is recreated on the destination database server, these permissions are missing:
This can be mystifying at first—essentially, you add users to the built-in database roles such as aspnet_Membership_BasicAccess and aspnet_Membership_FullAccess, but membership of these roles has no effect. They’re basically just empty roles that aren’t mapped to any permissions.
The problem is that stored procedures are “objects” in database terms (don’t ask me, I’m not a DBA), and by default Web Deploy does not include object-level permissions when it scripts the database. To change this behaviour, you need to modify the project file for your web application project.
1. In the Solution Explorer window, right-click your web application project node, and then click Unload Project.
2. Right-click the project node again, and click Edit [project file].
3. Locate the PropertyGroup element that corresponds to your build configuration (for example Release|AnyCPU).
3. Within this element, locate the PreSource element and add a Permissions=”True” attribute, as shown below. The Permissions attribute indicates that the database script should include all permissions, including object-level permissions, which are defined in the source database.
- Web Deploy uses the dbFullSql provider to deploy databases (the link includes some properties you can use as PreSource attributes).
- Under the covers, the dbFullSql provider uses SQL Server Management Objects (SMO) to generate database scripts. The ScriptingOptions Properties page describes some SMO properties you can specify as PreSource attributes.
Alternatively, to get a full list of properties, you can run the following Web Deploy command:
There’s much more to database deployment than I can cover in a quick blog post, and we’ll cover these kinds of issues in much more detail when we publish to MSDN. For now though I hope this helps to shed some light on the intricacies of database deployment.