SQL Server, SANs and Virtualisation

By Perry Whittle, 2009/01/08

 Rate this |   Join the discussion |  Briefcase |   Print

SAN's and virtual platforms are fast becoming the norm for most companies\authorities now. This is all well and good for those that have interaction with these technologies and understand them, but what about the DBA. After all, if his\her SQL server will be connecting to backend SAN storage, they still need to understand a little about what it does. This is even more so if it's also a virtual machine. This article will give you some basics on how these things affect your database instance.

Let's start by breaking down the SAN,

What is a SAN?

Storage Area Networks basically consist of racks of disks connected by switches. The disks are "sliced" up into arrays called "LUN's". The switches used are not ordinary switches, they are fibre channel units supporting specialized protocols (lately FC-IP).

But why does the admin always refer to it as the fabric?

The "Fabric" is the term used to describe the high speed fibre channel network which comprises the SAN. This network consists of fibre channel switches, fibre optic cables and host bus adaptors (HBA's). Some older SAN technologies use hubs instead of switches, but these are now rare. The network configuration is usually automatic in that each switch registers a devices WWN when plugged into one of its ports. Each device has its own WWN which is a 64 bit unique ID (sort of like a MAC address but longer). This WWN is registered on the internal routing table and essentially builds a map. LUN's can be zoned to provide secure access between given ports. At the server end sits the HBA, an HBA is a plug in card much like a NIC and most have dual port capability, the fibre optic cable plugs into these ports. All these components link together and essentially present the storage as though it were locally attached.

How does it provide failover?

Multiple fabrics are usually employed to multi home devices to the disk storage. The diagram below shows a basic fabric configuration.

Although a little crude you should get the basic idea. Note the multi homing between the HBA's and the switches to provide a fail over path at any point. I have depicted SQL Server1 and SQL Server2 in the diagram above but these could quite easily be ESX hosts with virtual machines running inside them. Obviously the more devices you add (HBA's and switches), the more links you create to provide multi homed redundancy, and the larger they get the more complex they usually become.

In our scenario each HBA and switch are linked to multiple partners, Perimeter Switch1 and Core Switch1 make up Fabric1. Perimeter Switch2 and Core Switch2 make up Fabric2, so even with a core switch and a server HBA failure your server still has a path through to the storage. Another reason for this is, it allows a whole fabric to be taken offline and repaired\administered\etc without affecting storage access. Currently speeds of 2-4 Gbps are supported, but these speeds are ever increasing. For all their complexity SAN's are essentially, no different to ethernet networks (well maybe just a little).

So what is virtualization?

Modern virtualization technologies (HyperVisors) are merely a software layer which shares common hardware components among software based machines. They all work very differently, so it's best to clear this up from the start.

During a some of my previous scoping projects, a vendor has indicated to me that they didn't support their software on VMWare virtual machines as some of their customers had tried this and encountered performance problems. I became suspicious and asked the vendor which platform the customers had used, it turned out (much as i expected) they were using VMWare Server.

Now, don't get me wrong this product and Microsoft Virtual Server 2005 Enterprise are both ideal entry level virtualization platforms but should not be used in anger for a product such as a full blown GIS system or SQL\Exchange server. Having said that, I did have a 2 node SQL2005 cluster running on a laptop under MS VS2005 Enterprise. The laptop only died when I booted up the 3rd VM which was a Windows 2008 server (Beta) :-) , funny that!

The premium product currently on the market is VMWare's VI3 which incorporates ESX server 3.x, this host platform is extremely powerful and works quite differently to the previously mentioned products. The VI uses a management server which uses either a SQL or Oracle back end RDBMS. This is used to track\configure the entities in the virtual infrastructure.

The heart of the ESX server is the VMKernel (yes its a UNIX machine at heart), which is originally based on the Redhat 2.4.x kernel. It is totally stripped down and then rebuilt by the VMWare engineers. The VMWare kernel directly executes CPU code where ever possible except for special\privileged instructions, these use a process called binary translation, which interrupts the binary machine instructions and then re compiles them before sending them on to the virtual machine. The direct instruction execution affords the hypervisor superior performance as it does not emulate any instruction set, but can cause problems when moving VM's between hosts with disparate hardware (different CPUs specifically). When designing your virtual infrastructure it is best to decide on uniform hardware for the hosts from the outset to try and alleviate any problems that may arise as a result of different hardware (in other words don't buy 3 hosts with AMD Opteron and one with Intel Xeon).

Each virtual machine runs in its own memory\process space, so there is no danger of machines crossing address spaces. The infrastructure uses a .NET client which allows full management of the system including configuring storage, virtual machines, networking, etc. Shown below is a screenshot of the VMWare Virtual Infrastructure Client for a configured VI3 system. Note the storage LUN's under datastores.

 

Zoom in  |  Open in new window

The HBA's are installed to each ESXHOST and the disk stores; storage LUN's are configured using the SAN management software and presented to the virtual infrastructure via the HBAs. VI3 has 3 unique options "HA", "DRS" and "VMotion". "VMotion" is the proprietary technology VMWare use to migrate a VM from one host to another while running live. "VMotion" uses a special private network (at least 1 Gbps configured on each host) which passes the VM configuration details and memory footprint (unencrypted, hence the high speed private network) between hosts. "HA" is the high availability option which allows VM's to be automatically resumed on another partner host should the original host fail for some reason. HA uses a heart beat technology (again over a private network) and detection of a failure initiates restart on another host. "DRS" is the process of distributing VM's across hosts to relieve memory and CPU pressures, this uses resource pools and works in conjunction with VMotion. With resource pools it is possible to group VM's and throttle them accordingly.

With virtualization and SAN's fast becoming cheaper and popular there is more of a requirement for the DBA to understand these technologies. Many organizations world wide are seeing a lot of success vitalizing SQL servers, exchange servers, file servers, domain controllers and even software firewalls. Reducing datacentre sizes and power consumption are primary goals and also contribute heavily to streamlined corporate DR processes, after all if all your mission critical servers are VM's on one host then DR becomes easier and more manageable. There are many documents available on the Internet detailing Virtualization platforms and SAN topologies. For VMWare links go to

sqlcmd 
[{ { -U login_id [ -P password ] } | –E trusted connection }] 
[ -z new password ] [ -Z new password and exit]
[ -S server_name [ \ instance_name ] ] [ -H wksta_name ] [ -d db_name ]
[ -l login time_out ] [ -A dedicated admin connection ] 
[ -i input_file ] [ -o output_file ]
[ -f < codepage > | i: < codepage > [ < , o: < codepage > ] ]
[ -u unicode output ] [ -r [ 0 | 1 ] msgs to stderr ] 
[ -R use client regional settings ]
[ -q "cmdline query" ] [ -Q "cmdline query" and exit ] 
[ -e echo input ] [ -t query time_out ] 
[ -I enable Quoted Identifiers ] 
[ -v var = "value"...] [ -x disable variable substitution ]
[ -h headers ][ -s col_separator ] [ -w column_width ] 
[ -W remove trailing spaces ]
[ -k [ 1 | 2 ] remove[replace] control characters ] 
[ -y display_width ] [-Y display_width ]
[ -b on error batch abort ] [ -V severitylevel ] [ -m error_level ] 
[ -a packet_size ][ -c cmd_end ] 
[ -L [ c ] list servers[clean output] ] 
[ -p [ 1 ] print statistics[colon format]] 
[ -X [ 1 ] ] disable commands, startup script, enviroment variables [and exit] 
[ -? show syntax summary ]

SELECT LastName

FROM Sales.vSalesPerson

ORDER BY LastName

GO

Running sqlcmd commands
In addition to running Transact-SQL statements in interactive mode, you can also run sqlcmd commands. The commands let you perform actions that cannot be performed by using Transact-SQL. For example, you can use sqlcmd commands to connect to a specific instance of SQL Server or to send query results to a text file. The following code demonstrates how you can use sqlcmd commands along with Transact-SQL in interactive mode:

:Connect server01\SqlSrv2008
:Out C:\Data\SqlCmdOutput.txt
USE AdventureWorks2008
GO
SELECT LastName
FROM Sales.vSalesPerson
ORDER BY LastName
GO
:EXIT

sqlcmd -d AdventureWorks -Q "SELECT LastName FROM Sales.vSalesPerson"

-- 실행 결과 OUTPUT
sqlcmd -i C:\Data\SqlCmdInput.sql -o C:\Data\SqlCmdOutput.txt –u


!!del C:\Data\SqlCmdOutput.txt
SELECT LastName, SalesYTD
FROM Sales.vSalesPerson
ORDER BY LastName
:Out C:\Data\SqlCmdOutput.txt


Editing SQLCMD Scripts with Query Editor : http://msdn.microsoft.com/en-us/library/ms174187.aspx

http://cafe.naver.com/sqlmvp/371


-- test 목적
-- 5일간 데이터만 보관한다.
-- delete dml 의 비효율을 보완하기 위한 방법

use master
go

if exists (select * from master.dbo.sysdatabases where name ='split')
begin
 alter database split set single_user with rollback immediate
 drop database split
end
go

create database split
go

use split
go

-- 이하 실행 및 테스트
-- manage table
if object_id ('dbo.d_manage') is not null
drop table dbo.d_manage
go

create table dbo.d_manage(type varchar(100), i_dt smalldatetime)
go

create unique clustered index cl_d_manage on d_manage(i_dt, type)
go

insert into dbo.d_manage values('day', '20081201')
insert into dbo.d_manage values('day', '20081202')
insert into dbo.d_manage values('day', '20081203')
insert into dbo.d_manage values('day', '20081204')
insert into dbo.d_manage values('day', '20081205')
go

if object_id ('dbo.d_20081201') is not null
begin
 drop table dbo.d_20081201
 drop table dbo.d_20081202
 drop table dbo.d_20081203
 drop table dbo.d_20081204
 drop table dbo.d_20081205
end
 
-- manage target table
create table dbo.d_20081201(clubid varchar(100) not null, i_dt smalldatetime  not null check (i_dt = '20081201'), type varchar(100) not null, value varchar(100) not null)
create table dbo.d_20081202(clubid varchar(100) not null, i_dt smalldatetime  not null check (i_dt = '20081202'), type varchar(100) not null, value varchar(100) not null)
create table dbo.d_20081203(clubid varchar(100) not null, i_dt smalldatetime  not null check (i_dt = '20081203'), type varchar(100) not null, value varchar(100) not null)
create table dbo.d_20081204(clubid varchar(100) not null, i_dt smalldatetime  not null check (i_dt = '20081204'), type varchar(100) not null, value varchar(100) not null)
create table dbo.d_20081205(clubid varchar(100) not null, i_dt smalldatetime  not null check (i_dt = '20081205'), type varchar(100) not null, value varchar(100) not null)
go

-- test data 입력
set nocount on
insert into dbo.d_20081201 values ('1', '20081201', 'a', rand() * 10)
insert into dbo.d_20081201 values ('2', '20081201', 'a', rand() * 10)
insert into dbo.d_20081201 values ('1', '20081201', 'b', rand() * 10)
insert into dbo.d_20081201 values ('2', '20081201', 'b', rand() * 10)

insert into dbo.d_20081202 values ('1', '20081202', 'a', rand() * 10)
insert into dbo.d_20081202 values ('2', '20081202', 'a', rand() * 10)
insert into dbo.d_20081202 values ('1', '20081202', 'b', rand() * 10)
insert into dbo.d_20081202 values ('2', '20081202', 'b', rand() * 10)

insert into dbo.d_20081203 values ('1', '20081203', 'a', rand() * 10)
insert into dbo.d_20081203 values ('2', '20081203', 'a', rand() * 10)
insert into dbo.d_20081203 values ('1', '20081203', 'b', rand() * 10)
insert into dbo.d_20081203 values ('2', '20081203', 'b', rand() * 10)

insert into dbo.d_20081204 values ('1', '20081204', 'a', rand() * 10)
insert into dbo.d_20081204 values ('1', '20081205', 'a', rand() * 10)
insert into dbo.d_20081204 values ('2', '20081204', 'a', rand() * 10)
insert into dbo.d_20081204 values ('1', '20081204', 'b', rand() * 10)
insert into dbo.d_20081204 values ('2', '20081204', 'b', rand() * 10)

insert into dbo.d_20081205 values ('1', '20081205', 'a', rand() * 10)
insert into dbo.d_20081205 values ('2', '20081205', 'a', rand() * 10)
insert into dbo.d_20081205 values ('1', '20081205', 'b', rand() * 10)
insert into dbo.d_20081205 values ('2', '20081205', 'b', rand() * 10)
go

create unique clustered index cl_d_20081201 on d_20081201 (clubid, type, i_dt)
create unique clustered index cl_d_20081202 on d_20081202 (clubid, type, i_dt)
create unique clustered index cl_d_20081203 on d_20081203 (clubid, type, i_dt)
create unique clustered index cl_d_20081204 on d_20081204 (clubid, type, i_dt)
create unique clustered index cl_d_20081205 on d_20081205 (clubid, type, i_dt)
go

create view d
as
  select * from dbo.d_20081201
union all select * from dbo.d_20081202
union all select * from dbo.d_20081203
union all select * from dbo.d_20081204
union all select * from dbo.d_20081205
go

-- 유저 데이터

set statistics io on
set statistics profile on

set statistics io off
set statistics profile off

-- #################################################################################
-- 원하는 쿼리 (원하는 성능이 나오는가?  : OK 해당 테이블만 정확히 억세스 한다.)
-- #################################################################################
select * from dbo.d where i_dt = cast('20081204' as smalldatetime) and type ='a' and clubid = '1'

sp_helptext 'dbo.d '

-- 결과
-- 1 2008-12-04 00:00:00 a 8.14244

-- 실행계획
-- rows executes stmttext
1 1 select * from dbo.d where i_dt = cast('20081204' as smalldatetime) and type ='a' and clubid = '1'    -- 결과   -- 1 2008-12-04 00:00:00 a 8.14244    -- 실행계획   -- rows executes stmttext  -- 1 1 select * from d where i_dt = cast('20081204' as smalldatetime) an
1 1   |--Compute Scalar(DEFINE:([d_20081204].[clubid]=[d_20081204].[clubid], [d_20081204].[i_dt]=[d_20081204].[i_dt], [d_20081204].[type]=[d_20081204].[type], [d_20081204].[value]=[d_20081204].[value]))
1 1        |--Clustered Index Seek(OBJECT:([split].[dbo].[d_20081204].[cl_d_20081204]), SEEK:([d_20081204].[clubid]='1' AND [d_20081204].[type]='a') ORDERED FORWARD)
-- #################################################################################
-- #################################################################################

-- 아래 프로시저를 이용해 동적으로 view 를 만들어 줘야 한다.
--1 batch 작업으로 일간 batch 작업을 시작 한다.
-- 오늘은 20081206 이라고 가정한다.

-- 호출시 테이블을 만들어 주는 프로시저
go

if object_id('dbo.usp_d_maketable') is not null
drop proc dbo.usp_d_maketable
go

create proc dbo.usp_d_maketable
(@adddate smalldatetime)
as

declare    @sql varchar(8000)
  , @adddatechar char(8)

set @adddatechar = convert(char(8), @adddate, 112)
select @adddatechar

-- 이중 실행에 따른 오류 처리
if exists( SELECT *
   FROM sysobjects
   where name = 'd_'+ @adddatechar )
begin
 set @sql = 'drop table dbo.d_'+ @adddatechar
 exec (@sql)
end

set @sql = 'create table dbo.d_'+ @adddatechar +' (clubid varchar(100) not null, i_dt smalldatetime not null check (i_dt = '''+@adddatechar+'''), type varchar(100) not null, value varchar(100) not null)'
exec (@sql)
go

-- 호출시 인덱스를 만들어 주는 프로시저
if object_id('dbo.usp_d_makeindex') is not null
drop proc dbo.usp_d_makeindex
go

create proc dbo.usp_d_makeindex
(@adddate smalldatetime)
as

declare    @sql varchar(8000)
  , @adddatechar char(8)

set @adddatechar = convert(char(8), @adddate, 112)
select @adddatechar

set @sql = 'create unique clustered index cl_d_'+ @adddatechar +' on d_'+ @adddatechar +' (clubid, i_dt, type)'
exec (@sql)
go

-- 호출시 view 를 변경시켜줄 프로시저

if object_id('dbo.usp_d_change_view') is not null
drop proc dbo.usp_d_change_view
go

create proc dbo.usp_d_change_view
(
  @addDate smalldatetime
, @type varchar(100)
, @cutOffDay int
)
as

declare    @viewheader varchar(8000)
  , @viewbody varchar(8000)
  , @errStatus int

select @viewheader ='' , @viewbody =''

if not exists (select * from dbo.d_manage where type = @type and i_dt = @addDate)
 insert into d_manage values (@type , @adddate)

set @viewheader =
'
alter view d
as
'
select @viewbody = @viewbody + 'union all select * from dbo.d_'+ convert(char(8), i_dt, 112) + ' ' + char(13) + char(10)
from dbo.d_manage
where i_dt > dateadd(dd, @cutOffDay * -1 , @addDate) and type = @type

select @viewbody = stuff(@viewbody, 1,10, '')

exec (@viewheader + @viewbody)
set @errStatus = @@error

if @errStatus <> 0
 raiserror ('aa',15,1)
else
 delete from dbo.d_manage
 where
   i_dt <= dateadd(dd, @cutOffDay * -1 , @addDate)
  and type = @type
go

set statistics profile off
set statistics io off
go

-- 실제 작업

-- 웹에서 테이블 생성 프로시저 호출
exec dbo.usp_d_maketable '20081206'
go

-- 해당 테이블을 대상으로 하둡엔 리듀스 프레임 워크에서 데이터 밀어넣기
-- 가상 데이터 삽입
insert into dbo.d_20081206 values ('1', '20081206', 'a', rand() * 10)
insert into dbo.d_20081206 values ('2', '20081206', 'a', rand() * 10)
insert into dbo.d_20081206 values ('1', '20081206', 'b', rand() * 10)
insert into dbo.d_20081206 values ('2', '20081206', 'b', rand() * 10)
go

-- 데이터 삽입과 인덱스 생성을 더 빠르게 하기위해 인덱스를 나중에 생성
exec dbo.usp_d_makeindex '20081206'
go

-- 동적 SQL 을 이용해서 view 를 고쳐줌
exec dbo.usp_d_change_view '20081206', 'day', 5
go

-- 과거 비효율 적으로 하나의 테이블에서 기간에 해당하는 테이블을 set rowcount 1000 delete d where i_dt < 날짜
-- 형식으로 지우면서 2시간 ~ 3시간을 소모함

-- 과거 테이블 drop
-- 0초
drop table dbo.d_20081201
go

exec sp_helptext d

-- 결과
-------------------------------------
-- CREATE view d 
-- as 
-- select * from dbo.d_20081202  
-- union all select * from dbo.d_20081203  
-- union all select * from dbo.d_20081204  
-- union all select * from dbo.d_20081205  
-- union all select * from dbo.d_20081206  

-- 실제 작업 test 2
exec dbo.usp_d_maketable '20081207'
go
insert into dbo.d_20081207 values ('1', '20081207', 'a', rand() * 10)
insert into dbo.d_20081207 values ('2', '20081207', 'a', rand() * 10)
insert into dbo.d_20081207 values ('1', '20081207', 'b', rand() * 10)
insert into dbo.d_20081207 values ('2', '20081207', 'b', rand() * 10)
go
exec dbo.usp_d_makeindex '20081207'
go
exec dbo.usp_d_change_view '20081207', 'day', 5
go
drop table dbo.d_20081202
go

sp_helptext d
go

select * from dbo.d_manage
go
-- type    i_dt                                                  
-- ----- -----------------
-- day 2008-12-03 00:00:00
-- day 2008-12-04 00:00:00
-- day 2008-12-05 00:00:00
-- day 2008-12-06 00:00:00
-- day 2008-12-07 00:00:00

select * from dbo.d
go
[출처] delete dml 을 ddl 로 고치기 (특수한 업무의 효율을 높이기 위해서 ) (sqlmvp) |작성자 왕눈봄맘

 http://www.codeplex.com/SqlServerSamples
 http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

SQL2005 Download sample site ....  유용할듯 하군..