2

SQL Server 数据库分表分库操作

 1 year ago
source link: https://blog.51cto.com/waringid/5873870
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

SQL Server 数据库分表分库操作

精选 原创

数据库高可用环境的搭建可以参考以下文章

SQL Server 高可用（always on）配置指南之域(AD)环境搭建       ​ ​https://blog.51cto.com/waringid/5851856​​

SQL Server 高可用（always on）配置指南之节点配置      ​ ​https://blog.51cto.com/waringid/5851970​​

SQL Server 高可用（always on）配置指南之搭建故障转移群集     ​ ​https://blog.51cto.com/waringid/5854694​​

SQL Server 高可用（always on）配置指南之数据库安装    ​ ​https://blog.51cto.com/waringid/5854730​​

SQL Server 高可用（always on）配置指南之数据库侦听器及高可用   ​ ​https://blog.51cto.com/waringid/5859821​​

alwayson故障模拟及处理方案  ​ ​https://blog.51cto.com/waringid/5868824​​

2.操作步骤

2.1.创建文件组

echo 按任意键开始创建数据库FileGroup文件夹（请根据实际情况修改路径，5.83应为E盘）
pause
echo --创建基本资料表FileGroup文件夹
md D:\SQLServerData\MStore\FG_BaseTable
echo --创建促销数据FileGroup文件夹
md D:\SQLServerData\MStore\FG_Promotion
echo --创建商品单据FileGroup文件夹
md D:\SQLServerData\MStore\FG_StoreBill
echo --创建促销数据商品资料文件夹
md D:\SQLServerData\MStore\FG_StoreGoods
echo --创建销售单据FileGroup文件夹
md D:\SQLServerData\MStore\FG_VendorBill
pause
echo 创建完成。
pause



USE MStore
ALTER DATABASE MStore ADD FILEGROUP FG_BaseTable 
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_BaseTable_01', FILENAME = N'D:\SQLServerData\FG_BaseTable\FG_BaseTable_01.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_BaseTable
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_BaseTable_02', FILENAME = N'D:\SQLServerData\FG_BaseTable\FG_BaseTable_02.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_BaseTable
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_BaseTable_03', FILENAME = N'D:\SQLServerData\FG_BaseTable\FG_BaseTable_03.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_BaseTable

ALTER DATABASE MStore ADD FILEGROUP FG_Promotion 
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_Promotion_01', FILENAME = N'D:\SQLServerData\FG_Promotion\FG_Promotion_01.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_Promotion
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_Promotion_02', FILENAME = N'D:\SQLServerData\FG_Promotion\FG_Promotion_02.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_Promotion
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_Promotion_03', FILENAME = N'D:\SQLServerData\FG_Promotion\FG_Promotion_03.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_Promotion

ALTER DATABASE MStore ADD FILEGROUP FG_StoreBill 
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_StoreBill_01', FILENAME = N'D:\SQLServerData\FG_StoreBill\FG_StoreBill_01.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_StoreBill
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_StoreBill_02', FILENAME = N'D:\SQLServerData\FG_StoreBill\FG_StoreBill_02.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_StoreBill
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_StoreBill_03', FILENAME = N'D:\SQLServerData\FG_StoreBill\FG_StoreBill_03.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_StoreBill

ALTER DATABASE MStore ADD FILEGROUP FG_StoreGoods 
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_StoreGoods_01', FILENAME = N'D:\SQLServerData\FG_StoreGoods\FG_StoreGoods_01.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_StoreGoods
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_StoreGoods_02', FILENAME = N'D:\SQLServerData\FG_StoreGoods\FG_StoreGoods_02.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_StoreGoods
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_StoreGoods_03', FILENAME = N'D:\SQLServerData\FG_StoreGoods\FG_StoreGoods_03.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_StoreGoods

ALTER DATABASE MStore ADD FILEGROUP FG_VendorBill 
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_VendorBill_01', FILENAME = N'D:\SQLServerData\FG_VendorBill\FG_VendorBill_01.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_VendorBill
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_VendorBill_02', FILENAME = N'D:\SQLServerData\FG_VendorBill\FG_VendorBill_02.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_VendorBill
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_VendorBill_03', FILENAME = N'D:\SQLServerData\FG_VendorBill\FG_VendorBill_03.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_VendorBill



SQL Server 数据库分表分库操作_数据库分表分库



2.2.创建分区方案



1、创建名称为“pf_dc_createtime”分区函数和名称为“ps_fg_baseTable_dc_createtime”分区方案



--创建分区函数
CREATE PARTITION FUNCTION [pf_dc_createtime](datetime) AS RANGE LEFT FOR VALUES (N'2016-01-01T00:00:00.000', N'2019-01-01T00:00:00.000', N'2019-02-01T00:00:00.000', 
N'2019-03-01T00:00:00.000', N'2019-04-01T00:00:00.000', N'2019-05-01T00:00:00.000', N'2019-06-01T00:00:00.000', 
N'2019-07-01T00:00:00.000', N'2019-08-01T00:00:00.000', N'2019-09-01T00:00:00.000', N'2019-10-01T00:00:00.000')
--创建分区方案
CREATE PARTITION SCHEME [ps_fg_baseTable_dc_createtime] AS PARTITION [pf_dc_createtime] ALL TO (FG_BaseTable)



查看分区函数和分区方案



SQL Server 数据库分表分库操作_数据库分表分库_02



2.3.关联分区方案



创建表并关联分区方案



CREATE TABLE [dbo].[goods](
[Pid] UNIQUEIDENTIFIER,
[CompanyCode] VARCHAR(50),
[GID] BIGINT,
[Code] VARCHAR(20),
[Name] VARCHAR(50),
[SPEC] VARCHAR(50),
[Sort] VARCHAR(50),
[Brand] VARCHAR(50),
[Code2] VARCHAR(50),
[GoodsStatus] VARCHAR(50),
[GoodsStatusName] VARCHAR(50),
[QPC] NUMERIC(18,2),
[QPCStr] NVARCHAR(100),
[Munit] VARCHAR(50),
[SALE] VARCHAR(50),
[SALETAX] NUMERIC(18,4),
[TaxRate] NUMERIC(18,4),
[Alc] VARCHAR(50),
[AlcQty] INT,
[SALCQSTART] INT,
[BCKSTORE] VARCHAR(100),
[BCKVEN] VARCHAR(100),
[INPRC] NUMERIC(18,4),
[DXPRC] NUMERIC(18,4),
[PAYRATE] MONEY,
[CNTINPRC] NUMERIC(18,4),
[LSTINPRC] NUMERIC(18,4),
[INVPRC] NUMERIC(18,4),
[distributionprc] NUMERIC(18,4),
[WHSPrc] NUMERIC(18,4),
[OLDINVPRC] NUMERIC(18,4),
[RTLPRC] NUMERIC(18,4),
[LWTRTLPRC] NUMERIC(18,4),
[TOPRTLPRC] NUMERIC(18,4),
[CHKVD] SMALLINT,
[MBRPRC] NUMERIC(18,4),
[BillTo] INT,
[isltd] INT,
[isBind] INT,
[ISPKG] INT,
[ISUnit] INT,
[BaseGoodsType] INT,
[ORDERQTY] INT,
[wrh] INT,
[Status] INT,
[Memo] VARCHAR(100),
[PROMOTE] SMALLINT,
[GFT] SMALLINT,
[GoodsAccountType] INT,
[salcqty] INT,
[AddTime] DATETIME,
[AddUser] VARCHAR(50),
[AddUserName] VARCHAR(50),
[AddIP] VARCHAR(50),
[UpdateTime] DATETIME,
[UpdateUser] VARCHAR(50),
[UpdateUserName] VARCHAR(50),
[UpdateIP] VARCHAR(50),
[DataStatus] SMALLINT,
[SSStart] DATETIME,
[SSEnd] DATETIME,
[FCATEGORY] VARCHAR(20),
[wd1] NVARCHAR(510),
[wd2] NVARCHAR(510),
[wdname1] NVARCHAR(510),
[wdname2] NVARCHAR(510),
[GRADE] NVARCHAR(510),
[VERSION] INT,
[dc_createtime] [datetime] NOT NULL default getdate()
CONSTRAINT [PK_goods] PRIMARY KEY NONCLUSTERED
(
[Pid] ASC,
[dc_createtime] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [ps_fg_Basetable_dc_createtime]([dc_createtime])
) ON [ps_fg_Basetable_dc_createtime]([dc_createtime])
GO

CREATE INDEX index_goods ON [goods]([dc_createtime])



SQL Server 数据库分表分库操作_生产环境_03



查看关联结果



SQL Server 数据库分表分库操作_SQL Server 分表分库_04

        

        

            

                report
            

        

    







        
Recommend

        



                

    
About Joyk

    
 





Aggregate valuable and interesting links.

Joyk means Joy of geeK