The database providers for the Web Deployment Tool
(WebDeploy) give application developers a great deal of flexibility in setting
up databases as part of their application installs. Outside of the Gallery,
applications have a variety of methods for setting up databases, tables, users,
and starting data:
- Some applications can do everything necessary, as long as the
user can provide administrative credentials during the application setup.
These applications usually have the flexibility to work with any database
components that have already been configured, and can work without
administrative credentials in those cases.
- Some applications expect the database and the database user to be
created before running the application for the first time.
- Some applications expect all of these steps to be done before
running the application for the first time.
- Some applications will use any combination of the above.
The WebDeploy database providers give the application
developer the ability to perform whatever database functions are required prior
to the application’s first run. There are three WebDeploy components involved
in specifying database installation directives.
- The provider directive in the manifest.xml file. This directive
tells WebDeploy which database provider to use, and what file to use
for the script.
- The connection string in the parameters.xml file. This tells Web
Deploy what credentials to use, and what database and server to
connect to.
- The SQL Script. This is a standalone file that can contain any
valid database commands for the specific database engine you are
using.
The minimal provider directive identifies the provider to
use and the SQL file to use it on:
</msdeploy.iisapp>
<dbfullsql path="install.sql" />
</msdeploy.iisapp>
This example tells WebDeploy to use the MS SQL (dbfullsql)
provider and to run a file called "install.sql".
The connection string is usually put together from other
parameters in the parameters.xml file. The App
Packaging Guide contains a description of the requirements for these
parameters, and examples of how to use them and put them together.
When using one of the database providers, the user will need
to provide credentials for WebDeploy to use to communicate with the database.
Even though these credentials are used somewhat differently for the different
databases, they are all present in some form for each. These credentials
consist of:
- Database Server - This can be the hostname or IP address
of a server, or the service name for the database server.
- Database Name - The name of the database on the server
that the application will use
- Database User - This is the user that is used specifically
for WebDeploy to connect to the database and, if necessary, perform database
operations.
- Database Password - The password for the Database User.
In addition to these credentials, applications will usually
need an ID and password specific to the application. Application developers
can use the WebDeploy tool to prompt the user for these credentials as well.
Then the credentials can be placed in configuration files or SQL scripts to be
used by the application.
The SQL Script can be any valid SQL or commands that the
database engine will accept. The contents of this file can be parameterized
the same way as any other file in the distribution. The SQL file can also be
empty if you just want to use the provider to ensure that the database already
exists. Most of our current applications use this SQL to create a database
user for the application to use. Examples of the syntax for each engine are
covered in their respective sections.
The providers will follow these steps in communicating with
a database:
- Connect to the database server.
- Authenticate as the database user.
- Check to see if the database exists, and if the user has access to it.
- If the database does not exist, WebDeploy will attempt to create
it using the provided credentials.
- If the database creation fails, then WebDeploy will exit with a
message telling the user that the user does not have sufficient
privileges for the required tasks.
- Execute the contents of the database script.
Common Scenarios
There are a few common scenarios that users of applications
in the Gallery will face. The WebDeploy database providers have been designed
with the flexibility to allow an application developer to create one set of
configuration entries and scripts that will work for all of these scenarios.
Shared hosting
In a shared hosting scenario, the customer rarely has direct
administrative access to the database. While in many cases, the user can
create databases, the function is usually performed through a control panel
where the user never sees the administrative credentials. The user that gets
created as part of this process usually has full administrative privileges for
the specific database that was created. Packages need to be able to execute
the required SQL without needing full database administrative privileges. The
database is usually remote in these scenarios.
Dedicated Hosting
Dedicated hosting users will usually have full privileges
for their database engine. Many of these users may not have the skills,
though, to be a full DBA. These users require the flexibility to be able to
use either the administrative user for WebDeploy connections, or an application
database user. The database may be local or remote.
Dev environment
In a dev environment, the user can be expected to have full
control over the database and operating system. Any actions required by the
application can be performed from outside the setup, if necessary. However,
most devs still prefer an application installation that takes care of setting
up the database.
Installation Methods
WebDeploy packages which are part of the Application Gallery
can be installed by one of the methods below, each of which will provide a
slightly different experience for users. As you design your package, you
should keep each of these methods in mind such that your package will work with
any of them.
- Using the WebPI. When a package is installed with the WebPI, the
WebPI’s user interface adapts to the database requirements of the
package. The WebPI will look at the tags on database oriented
parameters, and the providers specified in the manifest.xml file. If
more than one database engine is available for the application, the
WebPI will ask the user to specify which database to use. The
parameter setting screen will then only show the parameters that are
relevant for that database. The WebPI will ask the user if the user
wants to use an existing database, or create one. The user will only
be asked for database administrative credentials if they are needed to
create a new database.
- Using WebDeploy locally. You may choose to make your WebDeploy
package available to users outside of the WebPI. Users could then
download the package and use the IIS Manager to install the
application. The WebDeploy UI will ask the user to select from
multiple databases if relevant. The user will be asked to provide all
parameters specified for that database, including the administrative
credentials. When designing your script, you should think about how
the script would run if run by a regular user instead of an
administrative user.
- Using a third-party control panel. We have provided documentation
on the way we use the parameter tags to all of our partners who are
working on control panels to integrate with the Gallery. How they
choose to implement their user interfaces is up to their discretion,
and is dependent on their existing models. In some cases, the UI will
behave like either of the examples above. Control panel vendors may
choose to expose or hide any portion of the parameterization they
want. It is important to make sure you use the specified tags, as
these control panel vendors will be looking for them to help them
build their UIs.
SQL Specifics
Applications that require access to MS SQL databases can
work with either SQL Server or SQL Express. When the Web Platform Installer
installs one of these applications, it will check to see if any SQL is
installed on the local machine. If there is none, the WebPI will select SQL
Express as one of the components to be installed. Users that want to use a
remote database have the option of unselecting the SQL Express option.
SQL Express
The Web Platform Installer will install SQL Express 2008
when the user requests a SQL Installation, or when an application needs SQL.
The WebPI will offer the user a choice of authentication modes for SQL - either
Windows Native or database based. The WebPI recommends that application users
choose database based authentication, as most applications prefer that method.
When the user selects database authentication, the user will need to enter a
password for the ‘sa’ or administrative user. This password needs to meet the
requirements set out in http://msdn.microsoft.com/en-us/library/ms143705.aspx.
If you use the SQL script to set a password for a database user, the same
password requirements will obtain. The default installation sets up the SQL
Express server with the service name of .\SQLEXPRESS.
The Install.SQL script below works with the parameters
example in the packaging guide to create a user and set a password for the
application’s database. The script is designed to set all of the required
privileges for most application needs.
/**********************************************************************/
/* Install.SQL */
/* Creates a login and makes the user a member of db_owner */
/* */
/**********************************************************************/
-- Declare variables for database name, username and password
DECLARE @dbName sysname,
@dbUser sysname,
@dbPwd nvarchar(max);
-- Set variables for database name, username and password
SET @dbName = 'PlaceHolderForDb';
SET @dbUser = 'PlaceHolderForUser';
SET @dbPwd = 'PlaceHolderForPassword';
DECLARE @cmd nvarchar(max)
-- Create login
IF( SUSER_SID(@dbUser) is null )
BEGIN
print '-- Creating login '
SET @cmd = N'CREATE LOGIN ' + quotename(@dbUser) + N' WITH PASSWORD ='''+ replace(@dbPwd, '''', '''''') + N''''
EXEC(@cmd)
END
-- Create database user and map to login
-- and add user to the datareader, datawriter, ddladmin and securityadmin roles
--
SET @cmd = N'USE ' + quotename(@DBName) + N';
IF( NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + replace(@dbUser, '''', '''''') + N'''))
BEGIN
print ''-- Creating user'';
CREATE USER ' + quotename(@dbUser) + N' FOR LOGIN ' + quotename(@dbUser) + N';
print ''-- Adding user'';
EXEC sp_addrolemember ''db_owner'', ''' + replace(@dbUser, '''', '''''') + N''';
END'
EXEC(@cmd)
GO
MySQL Specifics
The Web PI will not install MySQL on your users’ systems.
If your application requires MySQL, the WebPI will check for the existence of
MySQL on the local system. If MySQL is not found, the WebPI will warn the user
that MySQL is required, and provide a link to get MySQL.
WebDeploy can work with MySQL databases in much the same way
as it does with SQL. SQL scripts can contain any valid MySQL syntax. The
Install.sql script below works with the parameters example in the packaging
guide to create a user and set a password for the application’s database. The
script is designed to set all of the required privileges for most application
needs.
In a shared hosting environment, the end user will probably
not have the administrative privileges required for executing the GRANT
statement in the script. If the user does not have permission to execute the
GRANT statement, the MySQL provider will throw an error, and the installation
process will fail. We have developed the add_user script to ignore errors
caused by the end user having insufficient privileges to execute the GRANT.
This way, the installation can complete successfully in an environment where
both the user and the database are created before installing the application.
In MySQL syntax, the normal command delimiter is a
semi-colon (;). MySQL procedures require a different delimiter to be used for separating
statements. This is explained in the MySQL
documentation. To specify the delimiter for WebDeploy, you should use
attributes of the dbMySQL provider directive in the manifest.xml file:
<dbmysql path="install.sql"
commandDelimiter="//"
removeCommandDelimiter="true" />
The create user script we use can be parameterized as well.
Note that we use ‘localhost´ for the source for the GRANT statement. As
most of our users will either be installing locally, or will have the database
and user created for them with appropriate GRANT statements, we decided to
assume ‘localhost’ for the server. If you think your application is
more likely to be running against a remote database where the user does have
full administrative privileges, you may want to replace ‘localhost’ in
the example with a parameter.
USE PlaceHolderForDb;
DROP PROCEDURE IF EXISTS add_user ;
CREATE PROCEDURE add_user()
BEGIN
DECLARE EXIT HANDLER FOR 1044 BEGIN END;
DECLARE EXIT HANDLER FOR 1410 BEGIN END;
GRANT ALL PRIVILEGES ON PlaceHolderForDb.* to 'PlaceHolderForUser'@'localhost' IDENTIFIED BY 'PlaceHolderForPassword';
FLUSH PRIVILEGES;
END
//
CALL add_user() //
DROP PROCEDURE IF EXISTS add_user //