SqlServer 自動化分區(qū)方案

　　本文是我關(guān)于數(shù)據(jù)庫分區(qū)的方案的一些想法，或許有些問題。僅供大家討論。SqlServer (SqlServer 2005\SqlServer 2008)實現(xiàn)分區(qū)需要在企業(yè)版下進行.

　　SqlServer的分區(qū)分為大致有以下個過程：1、創(chuàng)建文件組用以存放數(shù)據(jù)文件 2、創(chuàng)建文件組用戶數(shù)據(jù)文件 3、創(chuàng)建分區(qū)函數(shù) 4、創(chuàng)建分區(qū)方案 5、在分區(qū)方案下創(chuàng)建表

　　本文是在SqlServer2012 下完成的。

　　過程：

1、新建數(shù)據(jù)庫，在屬性中創(chuàng)建文件以及文件組。如下圖：

　　可以在下圖中選擇文件組、或者新建文件組用戶存放上圖中新建的文件：

　　2、創(chuàng)建分區(qū)函數(shù)

CREATE PARTITION FUNCTION [partitionById](int) 
AS RANGE LEFT FOR VALUES (100, 200, 300)

3、創(chuàng)建分區(qū)方案

CREATE PARTITION SCHEME [partitionSchemeById] 
AS PARTITION [partitionById] --分區(qū)函數(shù)
TO ([FileGroup1], [FileGroup2],  [FileGroup3]，[FileGroup4])

　　注意以上分區(qū)函數(shù)使用的是LEFT ,根據(jù)后面的值指明了數(shù)據(jù)庫中如何存放。以上存放方式為：-∞，100],(100,200],(200,300],(300,+∞）.此分區(qū)方案是依據(jù)分區(qū)函數(shù)

partitionById 創(chuàng)建的。那就是說以上Id的存儲區(qū)間分別被放在[FileGroup1], [FileGroup2],  [FileGroup3]，[FileGroup4]文件組的文件中。

4、依據(jù)分區(qū)方案創(chuàng)建表

CREATE TABLE [dbo].[Account](
    [Id] [int] NULL,
    [Name] [varchar](20) NULL,
    [Password] [varchar](20) NULL,
    [CreateTime] [datetime] NULL
) ON partitionSchemeById(Id)

　　注意：創(chuàng)建表的腳本中需要指明分區(qū)方案和分區(qū)依據(jù)列

　　查看某分區(qū)的數(shù)據(jù)：

SELECT * FROM 
[dbo].[Account]
WHERE $PARTITION.[partitionById](Id)=1

　　查詢結(jié)果如下圖：

至此，分區(qū)似乎已經(jīng)結(jié)束了。但是看看后一個分區(qū)里的數(shù)據(jù)：Id>=400的全部放在了一個數(shù)據(jù)文件中。這樣在有可能瓶頸就發(fā)生在了這個分區(qū)中。

如果數(shù)據(jù)不停的增長，希望分區(qū)也不斷的自動增加。如：每天生成一個新的分區(qū)來存放分區(qū)新的數(shù)據(jù)。如到第二天時，新生成一個分區(qū)來存放(400，500 ]的數(shù)據(jù)。

這里我采用了Sql Job的方式來自動產(chǎn)生分區(qū)：

DECLARE @maxValue INT,
    @secondMaxValue INT,
    @differ    INT,
    @fileGroupName VARCHAR(200),
    @fileNamePath    VARCHAR(200),
    @fileName   VARCHAR(200),
    @sql        NVARCHAR(1000)


SET @fileGroupName='FileGroup'+REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120 ),'-',''),' ',''),':','') 
PRINT @fileGroupName
SET @sql='ALTER DATABASE [Test] ADD FILEGROUP '+@fileGroupName
PRINT @sql
EXEC(@sql)

SET @fileNamePath='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLINSTANCE\MSSQL\DATA\'+REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120 ),'-',''),' ',''),':','') +'.NDF'
SET @fileName=N'File'+REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120 ),'-',''),' ',''),':','') 

SET @sql='ALTER DATABASE [Test] ADD FILE (NAME='''+@fileName+''',FILENAME=N'''+@fileNamePath+''') TO FILEGROUP'+'    '+@fileGroupName
PRINT @sql
PRINT 1
EXEC(@sql)
PRINT 2

--修改分區(qū)方案，用一個新的文件組用于存放下一新增的數(shù)據(jù)
SET @sql='ALTER PARTITION SCHEME [partitionSchemeById] NEXT USED'+'    '+@fileGroupName
EXEC(@sql)
  --分區(qū)架構(gòu)
PRINT 3 
SELECT @maxValue =CONVERT(INT,MAX(value))
FROM SYS.PARTITION_RANGE_VALUES PRV

SELECT @secondMaxValue = CONVERT(INT,MIN(value))
FROM 
(
    SELECT TOP 2 * FROM SYS.PARTITION_RANGE_VALUES ORDER BY VALUE DESC
)
 PRV 

SET @differ=@maxValue - @secondMaxValue 


ALTER PARTITION FUNCTION partitionById()  --分區(qū)函數(shù)
SPLIT RANGE (@maxValue+@differ)

這樣在計劃里指定每天什么時候運行，下圖：

參考：http://www.rzrgm.cn/lyhabc/articles/2623685.html

posted @ 2013-10-25 19:03 tyb1222 閱讀(9962) 評論(5) 收藏舉報

刷新頁面返回頂部

SqlServer 自動化分區(qū)方案

公告