SQL Server Schema Migration

Original: SQL Server schema Migration

The recent server architecture migration, the original server schema to the new server, the new server in the hardware better than before! The original server uses bidirectional synchronization and is divided horizontally into multiple database servers. During the migration process, the database involves data consistency, account numbers, re-zoning, canonical indexing, distributed queries, deletion of invalid triggers, and so on. Program aspects of re-modifying configuration files, SQL and so on.

Original schema

Now that you have the same server, all the sub-Libraries are migrated to the new server, and one server is added as a standalone distributor. However, the General library has no redundant servers, how to do? The workaround is to install one more DB instance in the General Library (DBA)! This way, each server has a corresponding one. Why does the general library also need to correspond to a server? because the total library and the sub-Library have data differences, users are using the sub-Library, so the original master library data can not be! But the database name is still the same, so there is only one additional instance! The total library cannot be, how to summarize the library data into a complete total library? used in the past, now the full backup of the sub-Library to restore to the total library server and use a different name, downtime maintenance, the sub-Library log backup and restore to the main library server in the various sub-libraries, then use the merge with the original master library, the original master library will be three full database. However, the contrast time is too long! The current scenario is to create a release of things in each sub-library and roll it into a new instance! (Reference: SQL Server Hub Subscription model (multi-release single subscription))

Then, the entire migration scenario can be divided into 5 phases:

The first stage: The Prophase stage, the plan scheme, the determination step, the pre-execution part time-consuming operation;

Second stage: One or two hours before the shutdown, pre-prepared, the steps to see one side, pre-implementation of the operation;

Phase III: Downtime maintenance, change distributed scripts, switch user access to the database, access test;

Stage four: Downtime, open access, important part of the work to complete first;

Phase Fifth: Complete all follow-up work for the next two days and observe the performance of the database;

Switching schemas

, the upper part is the original schema, and the lower half is the new schema. More questions to consider!! There are also several stages of processing:

First stage:

New server and database installation upgrade configuration needless to say;

Restore the total library backup to the new instance, set the simple mode, and empty the data inside (truncate) and shrink the database;

Delete all indexes, bulk create primary key;

Add files and filegroups to set the file growth rate;

Re-partition more partitions, set lock_escalation;

Change the default value constraint, remove the useless trigger;

migrating login account;

Back up the current empty database and save it to a new sub-library;

Set up the distribution database;

The original sub-Library (DB1~DBN) creates a thing to publish to the new General library and publishes only to the table; (Reference: SQL Server Hub Subscription model (multi-release single subscription))

Grant permission;

(The new library restores the empty library above and refers to some of the above steps)

The new General Library is created and published to the new sub-Library;

The new sub-Library sets the full mode and sets the backup schedule;

Phase II:

Changes to the linked server;

Stop the previous copy;

Prohibit backup jobs and other related jobs;

Check the synchronization situation;

Comparison of some parameter tables not summarized;

Phase III:

Shutdown maintenance Start-up;

Check for synchronization and whether the old and new databases are different (sysindexes)

change SQL scripts;

Upgrading the Web configuration file

Stage four:

Grant developer permissions;

Partial permission modification;

Observing the operation of the system;

Fifth stage:

The original master database is deleted, the new general library is migrated to SSD disk;

Hard backup plan creation;

Job transfer or reconstruction;

Delete the original sub-Library to the new General library copy;

Observing the operation of the system;

The whole process, handy, is almost always executed with a script, because scripts can be written in advance and can be planned in a good order. In fact, it can take less than half an hour to maintain downtime without problems. In short, can do other things first, can do after-do, reduce downtime maintenance time.

SQL Server Schema Migration