SqlServer自動化分區方案

來源：懂視網責編：小采時間：2020-11-09 07:41:22

SqlServer自動化分區方案

SqlServer自動化分區方案:本文是我關于數據庫分區的方案的一些想法，或許有些問題。僅供大家討論。SqlServer (SqlServer 2005\SqlServer 2008)實現分區需要在企業版下進行. SqlServer的分區分為大致有以下個過程：1、創建文件組用以存放數據文件 2、創建文件組用戶數據文件 3、創

推薦度：

點擊下載本文 文檔為doc格式

導讀SqlServer自動化分區方案:本文是我關于數據庫分區的方案的一些想法，或許有些問題。僅供大家討論。SqlServer (SqlServer 2005\SqlServer 2008)實現分區需要在企業版下進行. SqlServer的分區分為大致有以下個過程：1、創建文件組用以存放數據文件 2、創建文件組用戶數據文件 3、創

本文是我關于數據庫分區的方案的一些想法，或許有些問題。僅供大家討論。SqlServer (SqlServer 2005\SqlServer 2008)實現分區需要在企業版下進行. SqlServer的分區分為大致有以下個過程：1、創建文件組用以存放數據文件 2、創建文件組用戶數據文件 3、創建分

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

　　SqlServer的分區分為大致有以下個過程：1、創建文件組用以存放數據文件 2、創建文件組用戶數據文件 3、創建分區函數 4、創建分區方案 5、在分區方案下創建表

　　本文是在SqlServer2012 下完成的。

　　過程：

1、新建數據庫，在屬性中創建文件以及文件組。如下圖：

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

　　2、創建分區函數

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

3、創建分區方案

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

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

partitionById 創建的。那就是說以上Id的存儲區間分別被放在[FileGroup1], [FileGroup2], [FileGroup3]，[FileGroup4]文件組的文件中。4、依據分區方案創建表

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

　　注意：創建表的腳本中需要指明分區方案和分區依據列

　　查看某分區的數據：

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

　　查詢結果如下圖：

至此，分區似乎已經結束了。但是看看后一個分區里的數據：Id>=400的全部放在了一個數據文件中。這樣在有可能瓶頸就發生在了這個分區中。

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

這里我采用了Sql Job的方式來自動產生分區：

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

--修改分區方案，用一個新的文件組用于存放下一新增的數據
SET @sql='ALTER PARTITION SCHEME [partitionSchemeById] NEXT USED'+' '+@fileGroupName
EXEC(@sql)
 --分區架構
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() --分區函數
SPLIT RANGE (@maxValue+@differ)

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