Software Installation Considerations for SQL Server Applications

When I think back to my days as a developer, I remember lots of time invested in each phase of the SDLC. One area that always seemed to be neglected was application installation. That's unfortunate. An installation package is a complex program in and of itself and building one is not easy, especially if an RDBMS like SQL Server is involved. As a former InstallShield developer and current database guy, I have some suggestions. Whether you use InstallShield, Wise, Orca, or something else, most everything here should apply. SQL Server will be emphasized, but the concepts may align with other database vendors too.

Modularity

In addition to the installation/setup of the database environment, you'll have other installation needs such as services, workstation components, web server components, etc. Go with a modular approach and create a separate installation for each of those parts. The installation/setup of the database environment should be able to run independently of the others. If you absolutely have to have everything included in one installation, consider a top-level "master" installation, with sub-installations for different parts of your app.

The SQL Scripts

Every tsql command in your SQL script(s) has the potential to fail. It's important to catch and handle tsql errors so that they don't cause the entire installation to fail. This will require a lot of defensive, resilient, fault-tolerant coding on your part. Here's an example for creating the database. Note the emphasis on permissions, which I touched on in another post.

IF EXISTS (
    SELECT *
    FROM master.sys.databases d
    WHERE d.name = '$DatabaseName'
)
BEGIN
    --The database already exists.
    RETURN;
END
ELSE
BEGIN
    DECLARE @CanCreateDB BIT = 0;

    --Check if current server login has appropriate 
    --permissions to CREATE DATABASE.
    IF IS_SRVROLEMEMBER(N'sysadmin', SUSER_NAME()) = 1 
        SET @CanCreateDB = 1;
    ELSE IF IS_SRVROLEMEMBER(N'dbcreator', SUSER_NAME()) = 1 
        SET @CanCreateDB = 1;
    ELSE IF EXISTS (
        SELECT * 
        FROM sys.server_permissions p 
        JOIN sys.server_principals pr 
            ON p.grantee_principal_id = pr.principal_id 
        WHERE p.permission_name IN ('CREATE DATABASE', 'CREATE ANY DATABASE', 'ALTER ANY DATABASE')
        AND p.state_desc = 'GRANT'
        AND pr.name = SUSER_NAME()
    )
    SET @CanCreateDB = 1

    IF @CanCreateDB = 1
    BEGIN
        BEGIN TRY
                CREATE DATABASE [$DatabaseName]
                ...
                ...
                ...
        END TRY
        BEGIN CATCH
            --Log an error with the error msg.
            PRINT ERROR_MESSAGE();
        END CATCH
    END
    ELSE
    BEGIN
        --Current login doesn't have sufficient permissions.  Log an error.
        PRINT 'Database [$DatabaseName] does not exist and current login has insufficient permissions.';
    END
END

To Fail Or Not To Fail

If there's one thing I could tell every software vendor, it is this: Don't assume your application's database will have free reign on its own SQL Server instance. Changing instance-level server properties may be beneficial to your application. But it can have detrimental effects on the other applications that are sharing the instance, other instances installed on the SQL host, or even the SQL host itself.

Now, back to those potential SQL script errors. For each one, you'll need to decide if it should fail the installation or not. Changing the maximum degree of parallelism, default index fill factor, or maximum server memory (to name just a few) is not vital to your application. Any script action that attempts to change an instance-level server property should not fail the installation. Log these as warnings instead.

Some script actions will be vital to the application. This includes the CREATE DATABASE example and script actions for anything within the context of the database (create/drop/alter objects, data population, execution of stored procs, etc.) Any of these types of script actions that are unsuccessful should fail the installation.

Some of the script actions will fall into a gray area, and classifying them as vital or not can be difficult. ALTER DATABASE statements are a good example. Personally, I'd be wary of anything that might affect HA/DR. Changes to the recovery model, ownership of the database, database mirroring, HADR options, any backup that might break the log chain or differential backup chain, etc. are good "not vital" candidates. For others, use your best judgement.

With some careful planning, your SQL scripts will finish completely with all "errors" logged to file. Afterwards, you can fail the installation package as needed and provide a SQL script to the customer's DBA. She can run it herself or elevate the permissions of the installation SQL login. Then you'd try the installation again. Repeat until there are no more script errors vital to the application.

While all of this may sound like a burden, it's what allows you to successfully deploy your installation to anyone. From the agreeable company with no SQL Server expertise to the difficult client with a grumpy DBA.