1. Preface

During development, we have our database on the test server and the line network server. When we modify or add some fields on the line network, the line network database also needs to be updated. , at this time, based on the modification records of the table, and then adding modifications one by one online is very wasteful and error-prone. Is there a better way to solve this problem? have! That is the architecture comparison function that comes with VS.

Personally, I feel that in addition to mastering the necessary development knowledge in the early stage of development, the use and understanding of development tools are also crucial. Just like knowing the API, proficient use will make your development easier.

2. About

Starting from the Visual Studio 2005 version, VS has begun to support "compare and synchronize database schemas". Microsoft has implemented this function so early, which shows that this is a function that we will often use. a function. We can also implement this function ourselves (implement comparison of views, stored procedures and table structures in SQL Server), or we can package it into a tool (developing MSSQL architecture and data comparison tools ourselves). It's a little more troublesome for novices, so I won't go into details here. Those who are interested can take a look.

3. Start the drill

This drill uses the SQL Server tool that comes with VS2017 to compare the schema of the two databases and synchronize the target schema and the source schema.

1. Prepare two databases to be compared

I have two databases here, one is local and the other is the test server. In this walkthrough I will connect these two databases.

2. Connect the source database and the target database

The so-called source database and target database literally means (source > target) to synchronize the new and modified data in the source data to the target database. middle.

First we open VS>Tools>SQL Server>New Schema Comparison.

Visual Studio2017 database architecture introduction

Select source

Visual Studio2017 database architecture introduction

We select the database and click to select the connection

Visual Studio2017 database architecture introduction

Connect to the source server and verify, don’t forget to select the database

Visual Studio2017 database architecture introduction

Selecting the target operation is the same as selecting the source

Visual Studio2017 database architecture introduction

Connect to the target server and verify, don’t forget to select the database

Visual Studio2017 database architecture introduction

3. Compare the source database and the target database

Select both the source database and the target database to complete Finally, click "Compare" in the upper left corner to start comparing the two databases

Visual Studio2017 database architecture introduction

The following are the differences between the source database and the target database

Visual Studio2017 database architecture introduction

4. Update the target database

After the comparison is completed, select Update in the upper left corner, and the field data we selected to update will be updated (the fields circled with red marks in the picture)

Visual Studio2017 database architecture introduction

# Data synchronization is actually to differentially generate the corresponding script and then execute it in the target database. Select View Results to see the deployment script

Visual Studio2017 database architecture introduction

We know the update is complete when we see the prompt. Then we perform "compare" to see the differences.

Visual Studio2017 database architecture introduction

At this time we will find that the synchronization just now has been completed. If you want to synchronize them all, just check them all. The architecture comparison is that simple, and this walkthrough is over here.

4. Frequently Asked Questions

1. Why is my interface different from yours?
Answer: The tools I bring with vs2017 may have some differences compared to other versions. But the general operation is the same.

2. Why some updates fail for me
Answer: I have also encountered this problem. This is because this operation may cause the target data to be lost, or due to insufficient permissions and other reasons. I did this manually. If you know a good solution, you can leave me a message.

(48,1): SQL72014: .Net SqlClient Data Provider: 消息 50000，级别 16，状态 127，第 6 行 检测到行。由于可能丢失数据，正在终止架构更新。
(43,0): SQL72045: 脚本执行错误。执行的脚本:
IF EXISTS (SELECT TOP 1 1
           FROM   [dbo].[CR_ClassRingNewsFeed])
    RAISERROR (N'检测到行。由于可能丢失数据，正在终止架构更新。', 16, 127)
        WITH NOWAIT;

执行批处理时出错。

Copy after login

The above is the detailed content of Visual Studio2017 database architecture introduction. For more information, please follow other related articles on the PHP Chinese website!