Sql-server – What steps should I perform to prepare a MDF for distribution as a (mostly) read-only database

sql servervendor-support

I am on a team that develops a product that uses several SQL Server databases. Some of them are mostly read-only. Tables do not get changed, but some views and stored procedures get changed as a way of configuring the app. These views lack indexes. They are not materialized views. Changes to the tables only happen during upgrades and involve shipping the clients new MDFs that are attached. What steps should I perform to put these databases in a "ship ready" state? UPDATE Note that there is a LOT of free space in these MDFs, therefore a shrink followed by re-indexing.

We are doing the following currently:

  • Databases are in simple recovery mode (and we generally recommend keeping the original copy of the MDFs as the backups anyway)
  • We make the databases owned by sa and remove all logins (the deployment process adds appropriate logins)
  • We run DBCC SHRINKDATABASE on the databases
  • We run Ola Hallengren's Integrity and Maintenance scripts:

    EXECUTE dbo.DatabaseIntegrityCheck
        @Databases = 'DB_IN_QUESTION',
        @CheckCommands = 'CHECKDB',
        @ExtendedLogicalChecks = 'Y',
        @TabLock = 'Y'
    GO
    
    EXECUTE dbo.IndexOptimize
        @Databases = 'DATABASE_IN_QUESTION',
        @FragmentationLow = NULL,
        @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
        @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
        @FragmentationLevel1 = 5,
        @FragmentationLevel2 = 30,
        @SortInTempdb = 'Y',
        @MaxDOP = 0
    GO
    

Should we use other parameters for Ola's scripts? Should we take any other steps? Making the database read-only would be a pain because the views and stored procedures are often modified by the end user.

Best Answer

If you want to distribute a copy of your database, why don't you use LocalDB?

Shrinking the data file causes very high fragmentation. Running index maintenance grows the file back out. Why are you shrinking it?

Furthermore, if you're only going to ship the mdf file, why are you using DBCC SHRINKDATABASE? SHRINKDATABASE calls DBCC SHRINKFILE for every file in the database. If you're not shipping the log file, why are you shrinking it at all? Seems like a waste of time.

Delivering just an mdf file is not a supported method for sharing a database. There's always a risk that the file cannot be reattached. Shipping a backup or at the very least the full set of files is the best way to ship a database.