SQL Server 数据库分表分库操作

时间:2022-11-21 16:19:20

1.简介

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

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 FileGroup5.83E
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 数据库分表分库操作

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 数据库分表分库操作

查看关联结果

SQL Server 数据库分表分库操作