SnapManager for SQL Sizing

SnapManager for SQL Server gives extended capabilities for SQL backups which include backing up and restoring SQL Server databases with minimum disruption to the database and minimising backup and restore times. These capabilities are provided by using the NetApp underlying snapshot technology which will quiecse the database and snapshot the data at a volume level so no data is actually copied the process is completed in a timely.

SnapManager for SQL Server includes a client-based graphical user interface (GUI) program to perform various activities on SQL Server. These activities include:

Setting options for SnapManager for SQL Server operations.

Backing up, restoring and cloning databases and database components.

Monitoring and Reporting SnapManager for SQL Server operations.

Limitations & Known issues

All SQL Server 2000, 2005, & 2008 Editions are supported, except Microsoft Desktop Edition (MDSE) 2000. And all versions of MYSQL

Pre-Requisites

• System database files cannot reside on the same LUN as user databases
• Each user database log files and data files need to be on separate LUNs
• Tempdb must have its own LUN or stay on Local disk
• A separate LUN needs to be created to store the Snapshots
• SQL must be installed on the server before installing Snapdrive and SMSQL
• ONLY database files can exist on the LUN. You’ll need to put all application binaries and non-database files on different LUNs.
• The client machine must be in DNS (whichever is appropriate), and be part of a Domain.
• A reboot of the client will be required, so a downtime slot must be arranged. This does not have to be at the same time as the installation, if necessary.
• Verify that the follow requirements are met for the database software.
• SQL Server is installed on Windows 2000 or later.
• Snapdrive must be installed prior to the install of Snap Manager for SQL

For further requirements please refer to the new SnapManager for SQL server 5.2 best practices - http://media.netapp.com/documents/tr-4003.pdf

Drive layouts

Mount points will be used instead of drive letters as using drive letters gives you a limit of 26 LUNs per server.

Each SQL server will be provisioned with the following LUN and volume layout:

System Databases will be migrated onto a separate LUN contained in a single volume

LUN Name –  systemdb_”server name”.lun

Volume Name – v_systemdb_”server name”

Qtree Name – q_systemdb_”server name”

Tempdb will be migrated onto a separate LUN contained in a single volume

LUN Name – tempdb_”server name”.lun

Volume Name – v_tempdb_”server name”

Qtree name – q_tempdb_”server name”

Each Database that is in need of a granular restore or is an intensively used database will need to placed on its own Data LUN along with its own Log LUN both of these LUNs will be contained in separate volumes

Database

LUN Name – “database name”_servername”.lun

Volume Name – v_”database name”_”server name”

Qtree Name – q_”databse name”_”server name”

Logs

LUN Name – “log name”_”server name”.lun

Volume Name – v_”log name”_”server name”

Qtree Name – q_”log name”_”server name”

For small databases that are not heavily utilised and are not in need of a granular restore, these can be grouped together and will be backed up together. NOTE – When a restore is performed on a single database in this configuration all databases will be restored, a restore should only be performed in a DR scenario.

Database

LUN Name – “db1”_servername”.lun

Volume Name – v_”db1”_”server name”

Qtree Name – q_”db1”_”server name”

Logs

LUN Name – “log1”_”server name”.lun

Volume Name – v_”log1”_”server name”

Qtree Name – q_”log1”_”server name”

A snapinfo LUN will also need to be created as this will hold a copy of the trans logs that have been backed up sizing for this LUN is shown in section 6.5.

LUN Name – snapinfo_”servername”.lun

Volume Name – v_snapinfo_”server name”

Qtree Name – q_snapinfo_”server name”

Database Volume Sizing

To find out what the size of the Database volume should be follow the procedure below

Database LUN size = Check with application owner or look on server

Database LUN change rate = 5%

Number of online snaps = 3

Database LUN Size x 5% = N

LUN Size + (N x 3) = volume size

For example - Note this is an example and will not ascertain the size of the volume you are creating

Database LUN size = 100Gb

100 x 5% = 5Gb = N

5Gb x 3 = 15Gb

Volume Size = 100Gb + (5gb x 3) = 115Gb

Log Volume Sizing

To find out what the size of the Log volume should be follow the procedure below

Log LUN size = Check with application owner or look on server

Log LUN change rate = 5%

Number of online snaps = 3

Log LUN Size x 5% = N

LUN Size + (N x 3) = volume size

For example - Note this is an example and will not ascertain the size of the volume you are creating

Log LUN size = 100Gb

100 x 5% = 5Gb = N

5Gb x 3 = 15Gb

Volume Size = 100Gb + (5gb x 3) = 115Gb

Snapinfo LUN Sizing

To make sure that we have adequate space available for our SLQ backups we must size the snapinfo volume and LUN. To do this run through the following steps for each SQL server:

Step 1

Run the below script on the database that is being moved

-- Create the table to accept the results

create table #output (dbname char(30),log_size real, usage real, status int)

-- execute the command, putting the results in the table

insert into #output

exec ('dbcc sqlperf(logspace)')

-- display the results

select *

from #output

go

--open cursor

declare output_cur cursor read_only for

select log_size

from #output

--make space computations

declare @v1 as real

declare @base_val as real

set @base_val = 0

open output_cur

FETCH NEXT FROM output_cur

INTO @v1

WHILE @@FETCH_STATUS = 0

BEGIN

set @base_val = @base_val + @v1

FETCH NEXT FROM output_cur

INTO @v1

END

set @base_val = @base_val + 15

PRINT 'BASE_VAL = ' + cast(@base_val as nvarchar) + ' MB'

--clean-up

close output_cur

drop table #output

BASE_VAL =  21.46906 MB

Step 2

Total number of transaction log files to be kept in the snapinfo LUN = 3

LUN Size = 21.4906 * 3 = 64MB

Snap info Volume Sizing

To calculate the size of the snapinfo volume follow the procedure below

Volume size = LUN Size + (Largest Trans log * 3)

64MB + (21.46906 * 3) = 128MB