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.
All SQL Server 2000,
2005, & 2008 Editions are supported, except Microsoft Desktop Edition
(MDSE) 2000. And all versions of MYSQL
- 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”
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
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
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
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
