Please Read First:

While Portal Architects assumes no responsibility for supporting customer SQL installations we have created a series of recommendations on how to configure a basic SQL maintenance plan.  These steps will maintain the performance of the SkySync database by reorganizing the database indexes and update statistics on the tables as a nightly job.  While this recommendation is for the SkySync database, you can configure maintenance plans to include any other databases under your administration at your own discretion.

For the purposes of this guide we are assuming that the name of your database is “SkySync’.  If have used an alternate name please insert that name in the guide as necessary

1) Verify the current database configuration.

a) On the machine which hosts your SQL node open your Microsoft SQL Server Management Studio (SSMS) and connect to the database server instance where the SkySync database is installed.
b) Right click on your database named “SkySync” (or the Name you gave it) and select "Properties". 
c) Under the Files tab there is a table which includes the path which is where your “.mdf” and “log.ldf” files are located.  You will need this path information later.

Note: If you navigate to that directory you can see the current sizes of both files.

d) Under the Options tab make sure the recovery model is set to simple. (This will keep your transaction log from growing.  If you want to do Full you will need to back your “.ldf” transaction log file.).

2) Download and apply the maintenance script to the database.

a) In a browser, open the following URL: https://ola.hallengren.com and then download the free to use SQL Server script “MaintenanceSolution.sql”.  While this is a 3^rd party script which we are not affiliated with, we believe it is an excellent solution.
b) Open the SQL script file which will load the contents into your Microsoft SQL Server Management Studio query window.
c) Execute the script which will create the stored procedures in your master database.
d) In the object explorer under "Databases" expand the following trees: System Databases, Programmability, and Stored Procedures. The following four maintenance stored procedures should have been added:

i) dbo.CommandExecute
ii) dbo.DatabaseBackup
iii) dbo.DatabaseIntegrityCheck
iv) dbo.IndexOptimize

3) Create several system tasks to back up the database and rebuild the indexes.

a) Creation of a nightly backup task.

a) Create a text file with the following name “DatabaseBackup.txt”

i) Add the following lines to the file.

@echo off

sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'SkySync', @Directory = N'C:\Backup', @BackupType = 'FULL', @LogToTable = 'Y'" -b -o C:\temp\BackupDatabase.txt

ii) Notes:

1) @Databases – This is the name of your database.

2) @Directory – This is the path on your server where you would like the backup to be deposited.

3) –o – This is the path that you would like the output file to be deposited.

b) Save the file and then rename the extension to “.bat”.

c) Open the task scheduler and select "Create a Task".

d) Name the task “Nightly Database Backup” and provide a description as needed.

i) Make sure that the task will run under the same user that the database is being run under.  If the user is not authorized then the task will not be able to run.

ii) Under the "Trigger" tab add a new entry and set it to "Daily" with a start time.

1) We recommend something during the overnight period such as 3 AM.

iii) Under the "Action" tab selection browse on the program/script line and navigate to your DatabaseBackup.bat.

iv) Accept the default conditions.

e) Create a nightly index rebuild task.

f) Create a text file with the following name “RebuildSqlIndexes.txt”.

i) Add the following lines to the file.

@echo off

sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE [dbo].[IndexOptimize] @Databases = 'SkySync', @FragmentationLow = NULL, @FragmentationMedium = INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5,  @FragmentationLevel2 = 30, @UpdateStatistics = 'ALL', @LogToTable = 'Y'" -b -o C:\temp\temp\RebuildSqlIndexes.txt

1) Verify the current database configuration.

a) On the machine which hosts your SQL node open your Microsoft SQL Server Management Studio (SSMS) and connect to the database server instance where the SkySync database is installed.

b) Right click on your database named “SkySync” (or the Name you gave it) and select properties. 

c) Under the Files tab there is a table which includes the path which is where your “.mdf” and “log.ldf” files are located.  You will need this path information later.

d) Under the options tab make sure the recovery model is set to "Simple".  (This will keep your transaction log from growing.  If you want to do Full you will need to back your “.ldf” transaction log file.).

e) Verify that the SQL Server Agent is running. If it is not running you will need to start SQL Server configuration manager and select "SQL Server Services".  From there you can select the SQL Server Agent and set the start mode to "Automatic".  Then start the Agent Service.

2) Download and apply the maintenance script to the database.

a) In a browser open the following URL https://ola.hallengren.com and then download the free to use SQL Server script “MaintenanceSolution.sql”.  While this is a 3^rd party script which we are not affiliated with but we believe it is an excellent solution.

b) Open the SQL script file which will load the contents into your Microsoft SQL Server Management Studio query window.

c) Execute the script which will create the stored procedures in your master database on your SQL Server.

d) In the object explorer under databases expand the following trees: System Databases,  "Master Database", then "Programmability", then "Stored Procedures". The following four maintenance stored procedures should have been added by the script:

i) dbo.CommandExecute

ii) dbo.DatabaseBackup

iii) dbo.DatabaseIntegrityCheck

iv) dbo.IndexOptimize

3) Create several system tasks to back up the database and rebuild the indexes.

a) Creation of a nightly backup task.

a) Under SQL Server Agent, right click on "Jobs" and click on "New Job..."

b) Name the Job something like "Nightly SkySync Maintenance".

c) Select "Steps" page on the left.

d) Click New… on the bottom.

i) Name the Step “Backup”.

ii) Make sure the Type: is Transact-SQL script (T-SQL)

iii) Database: "Master".

iv) Command: EXECUTE [dbo].[DatabaseBackup] @Databases = ‘SkySync', @Directory = N'D:\DbBackups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 48, @CheckSum = 'Y', @LogToTable = 'Y'

v) Notes:

1) @Databases – This is the name of your database.

2) @Directory – This is the path on your server where you would like the backup to be deposited.

vi) Click "OK" to save the step.

e) Click "New…" on the bottom.

i) Name the Step “Integrity Check”.

ii) Make sure the Type: is Transact-SQL script (T-SQL).

iii) Database: "Master".

iv) Command: EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'SkySync', @LogToTable = 'Y'

v) Notes:

1) @Databases – This is the name of your database.

vi) Click "OK" to save the step.

f) Click "New…" on the bottom.

i) Name the Step “Reorg Indexes and Update Stats”.

ii) Make sure the Type: is Transact-SQL script (T-SQL).

iii) Database: "Master".

iv) Command: EXECUTE [dbo].[IndexOptimize] @Databases = 'SkySync',  @FragmentationLow = NULL,  @FragmentationMedium = INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',  @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',  @FragmentationLevel1 = 5,  @FragmentationLevel2 = 30,  @UpdateStatistics = 'ALL',  @LogToTable = 'Y'

v) Notes:

1) @Databases – This is the name of your database.

vi) Click "OK" to save the step.

g) Select the "Schedules" page on the left.

h) Click "New…" on the bottom.

i) Name the Schedule "Nightly".

ii) Schedule Type: "Recurring".

iii) File in the rest to job for example: "Nightly at 3am".

iv) Click "OK" to save the Schedule.

i) You can set Alerts or Notifications on the status of the Job.

j) Click "OK" to save the job.