I'm going to assume you actually have the following goals:
- Provide maximum performance at the clients post ALTER DATABASE X SET READ_ONLY
- Send the minimum amount of data to the clients consistent with maximum performance
I'm also going to assume you do NOT have any of the following goals:
- Reduce excessive amounts of work your server may have to do
- Complete the deployment work in a short amount of time
- Provide security for data transmission
Thus, I would recommend a few alterations. Order is absolutely critical, since we're going to first cause fragmentation, and then reduce it.
Therefore, in the following order:
- Run whatever Agent Job you normally use to do Ola's backup routine, either Full or Diff, and note that date/time of this "Ready to Deploy" backup
- You may continue with your normal work in your normal database once this backup is complete, while the rest of this is running.
- Restore the backup you just took to a new database which I will call PreparingToDeploy in this answer
- Change PreparingToDeploy to make the databases owned by sa and remove all logins
- Ensure PreparingToDeploy is in the SIMPLE recovery model
- Ensure PreparingToDeploy is using CHECKSUM for PAGE_VERIFY
- Drop every table and column and view and stored procedure and ... ... ... not explicitly required by the clients in PreparingToDeploy
- Verify every single table in PreparingToDeploy has a Clustered index. If not, stop here, delete PreparingToDeploy, fix that, retest, and start deploying over again.
- You really should do this check regularly, but your clients won't care about tables they won't get.
- Set AUTOGROWTH on PreparingToDeploy MDF files to a stupidly small fixed amount - perhaps as little as 8 to 32 MB at a time.
- this will cause filesystem level fragmentation, but that won't matter post-deployment, so in this rare edge case it's all right.
- Run Ola's maintenance script (modified below) on PreparingToDeploy to reduce the total number of pages in use
- CHECKPOINT PreparingToDeploy just in case
- SHRINKFILE only your PreparingToDeploy MDFs to 0% free space.
- You have just caused index fragmentation, which is horrific for performance, but we're about to fix that. The extra effort is ok in this rare edge case.
- Run Ola's maintenance script (modified below) on PreparingToDeploy to get rid of the index fragmentation we just caused
- Now SHRINKFILE TRUNCATEONLY the MDF files to remove any trailing free space without causing any more fragmentation
- CHECKPOINT just in case
- SHRINKFILE your LDF files does to the minimum size they can be for your clients to actually run with the largest amount of data they expect to have in the next N years, where N is large.
- You have just caused index fragmentation, which is horrific for performance, but we're about to fix that. The extra effort is ok in this rare edge case.
- take a FULL backup of PreparingToDeploy
- Ideally with SQL Server backup compression turned on to save space, if it's SQL 2008 Enterprise or SQL 2008 R2 Standard or above.
- DROP DATABASE PreparingToDeploy
- Ship the FULL backup of PreparingToDeploy to your clients
You will, however, need to change your deployment methodology to restore the backup rather than copy MDF's around - use sqlcmd if you need to. As a bonus, your clients won't have a problem even if they're on a later version of SQL Server than you are :).
Alter the call to Ola's maintenance script for deployment purposes only to the following, since the SOLE purpose is to provide the maximum benefit to the client - it can take as long as it takes, you really don't care, and INDEX REBUILD provides a better end result than INDEX REORGANIZE does. Every option that might perhaps provide some benefit to the READ_ONLY client copies has been selected.
EXECUTE dbo.IndexOptimize
@Databases = 'DATABASE_IN_QUESTION',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 1,
@FragmentationLevel2 = 2,
@SortInTempdb = 'Y',
@MaxDOP = 0,
@PageCountLevel = 9,
@Fillfactor = 100,
@PadIndex = 100,
@LOBCompaction = 'Y',
@UpdateStatistics = 'ALL',
@StatisticsSample = 100,
@MSShippedObjects = 'Y',
@Execute = 'Y';
GO
You may be able to get rid of the second fragmentation parameter entirely, of course. Test first.
