We have a large table (containing a large number of records and occupying a large amount of data) that stores transactions.
CREATE TABLE [Transactions] (
[TransactionID] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
[TimeStamp] DATETIME2 NOT NULL,
[ClientID] INT NOT NULL,
[ClientTransactionID] NVARCHAR (20),
[Sum] MONEY,
[SomeOtherData] NVARCHAR (MAX)
);
It is necessary to break down this table into partitions by the [TimeStamp] field, broken down by months. The [TransactionID] field should remain as Primary Key.
Data older than 1.5 years can be stored in one partition (access to them will be very rare).
The operation should go "seamlessly" - with minimal database downtime.
Direct rebuild of the table will take more than a day and not acceptable.
The result of the task should be a plan for the transition to partitioned storage, a list of the necessary technologies / services, as well as the necessary code to complete this plan.
Чтобы я сделал ... арендовал у кого-нить ссд полку или просто рейд на 4 ссдшника
Загнал бы ссд в 10-тку, далее bcp туда сюда
Если скуль позволяет включать отключать логгиррование на уровне файлгруп, воспользовался бы этим
