CN106302473B - A kind of the duplication maintaining method and system of SQL Server database - Google Patents

Abstract

The present invention is suitable for SQL Server database field, provides the duplication maintaining method and system of a kind of SQL Server database, which comprises receives server log information and verifies to the server log information；If the server log Information Authentication passes through, it is connected to pre-set publisher server, the distribution server and Subscriber；One-key duplication instruction set is received, the configuration information is called according to the one-key duplication instruction set, and publication is created according to the configuration information automatically and is subscribed to.The present invention is by carrying out security verification to the server log information, guarantee the legitimacy of login user, when receiving one-key duplication instruction set, the configuration information of pre-set publisher server, the distribution server and Subscriber is called, to create publication automatically and subscribe to the function, it can be achieved that one-key duplication database, simplify database duplication operation, database duplicating efficiency is improved, the workload of operator is reduced, reduces maintenance cost.

Description

A method and system for duplicating and maintaining a SQL Server database

technical field

The invention belongs to the field of SQL Server databases, in particular to a method and system for duplicating and maintaining SQL Server databases.

Background technique

SQL Server database is a relational database management system launched by Microsoft Corporation. It is mainly used to solve the problem of database real-time synchronization. It copies and distributes data and database objects from one database to another or several databases, then synchronizes between databases, and compares database snapshots to ensure data integrity.

However, the replication operation of the SQL Server database requires the operator to have professional database replication knowledge. Before upgrading the system including the SQL Server database, the replication must be deleted first, and the replication must be rebuilt after the upgrade. The operation is more complicated. Systemically speaking, the traditional SQL Server database replication operation method is inefficient and increases the workload of operators.

Contents of the invention

The object of the present invention is to provide a kind of copy maintenance method and system of SQL Server database, aim at solving the copy operation of SQL Server database and require the operator to have professional knowledge of database copy, before upgrading the system that includes SQLServer database, the copy must be deleted earlier, The operation of rebuilding the replication after the upgrade is more complicated. For the system that needs to be upgraded and updated frequently, the traditional SQL Server database replication operation method is inefficient and increases the work intensity of the operators.

The present invention is achieved like this, a kind of replication maintenance method of SQL Server database, described method comprises:

receiving server login information and verifying the server login information;

If the verification of the server login information is passed, then connect to the pre-set publishing server, distribution server and subscription server;

Receive a one-key copy instruction set, invoke the preset configuration information of the publisher, distributor and subscriber according to the one-key copy instruction set, and automatically create a new publication and subscription according to the configuration information.

The present invention also provides a replication maintenance system of a SQL Server database, said system comprising:

A verification module, configured to receive server login information and verify the server login information;

A server connection module, configured to connect to a pre-set publishing server, distribution server and subscription server if the server login information is verified;

A one-key copy module, configured to receive a one-key copy instruction set, call the preset configuration information of the publishing server, distribution server, and subscriber server according to the one-key copy instruction set, and automatically create a new publishing and subscription server according to the configuration information. subscription.

Compared with the prior art, the present invention has the beneficial effects of:

By performing security verification on the login information of the server, it is only connected to the pre-set publishing server, distribution server and subscription server when the verification is passed to ensure the legitimacy of the logged-in user. When receiving the one-key copy instruction set, call The pre-set configuration information of the publisher, distributor and subscriber can automatically create a new publication and subscription, which can realize the function of one-click copying the database, simplify the database copying operation, improve the efficiency of database copying, reduce the workload of the operator, and reduce maintenance cost.

Description of drawings

In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following will briefly introduce the drawings that need to be used in the description of the embodiments. Obviously, the drawings in the following description are some embodiments of the present invention. For Those of ordinary skill in the art can also obtain other drawings based on these drawings without making creative efforts.

Fig. 1 is the block flow diagram of the duplication maintenance method of SQL Server database that the embodiment of the present invention provides;

Fig. 2 is the block flow diagram of the duplication maintenance method of SQL Server database provided by the second embodiment of the present invention;

Fig. 3 is the structural block diagram of the replication maintenance system of the SQL Server database that the embodiment of the present invention three provides;

FIG. 4 is a structural block diagram of a SQL Server database replication maintenance system provided by Embodiment 4 of the present invention.

Detailed ways

In order to enable those skilled in the art to better understand the solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly described below in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are the embodiment of the present invention. Some examples, but not all examples. Based on the embodiments of the present invention, all other embodiments obtained by persons of ordinary skill in the art without making creative efforts shall fall within the protection scope of the present invention.

The terms "comprising" and any variations thereof in the description and claims of the present invention and the above drawings are intended to cover non-exclusive inclusion. For example, a process, method or system, product or device comprising a series of steps or modules is not limited to the listed steps or modules, but optionally also includes steps or modules that are not listed, or optionally also includes Other steps or modules inherent in these processes, methods, products or devices.

Embodiment one:

As shown in Figure 1, the replication maintenance method of the SQL Server database provided by the present embodiment includes:

Step S101: Receive server login information and verify the server login information.

In this embodiment, QL Server database replication refers to publishing the data to be synchronized on the data source database server, and then subscribing to the publication on the target database server, so as to realize real-time synchronization of database data. The data source database server is the publisher, and the target database server is the subscriber server. The distributor is used to transfer the database data published by the publisher to send to one or more subscribers respectively.

In a specific application, the server login information includes database user login information and computer user login information of the publishing server, distribution server and subscription server, and the user login information specifically includes server name, login name and password. By verifying the server login information, the security of copying and maintaining the SQL Server database can be increased, preventing criminals or unauthorized users from publishing and subscribing data in the SQL Server database at will.

Step S102: If the server login information is verified, connect to the pre-set publishing server, distribution server and subscription server.

In a specific application, the number of each of the preset publishers, distributors, and subscribers includes at least one server, that is, one or more publishers, one or more distributors, and one or more subscribers can be preset. Subscriber.

In one embodiment, before step S101, it also includes:

maintaining the preset configuration information of the publisher, distributor and subscriber.

In a specific application, the configuration information specifically includes:

The name of the publishing server set in advance, the computer user of the publishing server running the snapshot proxy process and the way the snapshot proxy connects to the publishing server, the name, user name and password of the publishing database, the preset;

The name of the pre-set distribution server, the name of the distribution database, user name, password, file name and snapshot;

The name of the preset subscriber server, the computer user running the process when the distribution agent synchronizes the subscription, and the way the distribution agent connects to the subscriber server, the name, user name, and password of the subscription database, and the preset subscriber server.

In a specific application, the preset configuration information of one or more publishers, one or more distributors, and one or more subscribers can be maintained, and the maintenance operation specifically includes:

Adding, modifying or deleting the preset configuration information of publishers, distributors and subscribers can also increase or delete the number of publishers, distributors and subscribers.

Step S103: Receive a one-key copy instruction set, invoke the preset configuration information of the publishing server, distribution server, and subscriber server according to the one-key copy instruction set, and automatically create a new publication and subscription according to the configuration information.

In this embodiment, the one-key copy instruction set specifically refers to: a set consisting of one or more one-key copy instructions. In a specific application, when the copy operation of the SQL Server database includes executing multiple publish and subscribe operations on multiple publishers and corresponding multiple distributors and multiple subscribers simultaneously, the one-key copy instruction set includes : Multiple one-click copy commands that execute multiple publish and subscribe operations at the same time. All publishing and subscribing operations can be performed with one click according to the configuration information, or only part of the publishing and subscribing operations can be performed.

In a specific application, data published by a publisher can be distributed by a distributor and subscribed by at least one (that is, one, two or more) subscribers, and multiple publishers can correspond to a distributor.

In one embodiment, the method also includes:

Receive a one-key deletion instruction set, and delete the created publication and subscription according to the one-key deletion instruction set.

In a specific application, the one-key deletion instruction set specifically refers to a set consisting of one or more one-key deletion instructions. When multiple publish and subscribe operations have been created, the one-key delete instruction set is executed to delete the corresponding publish and subscribe operations according to the one-key delete instructions in the one-key delete instruction set. You can delete all the publications and subscriptions that have been created with one click, or you can delete only some publications and subscriptions.

In this embodiment, by performing security verification on the server login information, it is only connected to the pre-set publishing server, distribution server and subscription server when the verification is passed, so as to ensure the legitimacy of the logged-in user. When calling the pre-set configuration information of the publisher, distributor and subscriber to automatically create a new publication and subscription, it can realize the function of one-click copying the database, simplify the database copying operation, improve the efficiency of the database copying, and reduce the workload of the operator , Reduce maintenance costs.

This embodiment can also realize the creation and deletion operations of multiple publishes and subscriptions at the same time, so as to realize the batch copy and delete of the SQLServer database. When the system needs to be upgraded frequently, the one-key batch copy and delete function can simplify the upgrade operation. Steps to improve the upgrade efficiency, reduce the workload of operators, and facilitate the management of operation and maintenance personnel.

Embodiment two:

As shown in Figure 2, before step S101 in the first embodiment, this embodiment also includes:

Step 201: pre-setting the publisher, distributor and subscriber.

In a specific application, the preset publishing server, distribution server, and subscription server specifically refer to pre-selecting and configuring configuration information of the publishing server, distribution server, and subscription server.

Step S202: Select a release database, select a distribution database, and create a new release.

In a specific application, selecting a publishing database specifically refers to selecting a publishing server for performing the new publishing operation, and then selecting a database or all or part of data in the database to be published in the selected publishing server. Correspondingly, selecting a distribution database refers to selecting a distribution server for performing the new publishing operation, and then selecting a default distribution database in the selected distribution server, and automatically creating a default distribution database if there is no default distribution database .

In a specific application, the number of new releases is used for custom settings, and the user can create one or more releases.

Step S203: Select the newly created publication, select a subscription database, and create a new subscription.

In a specific application, step S203 specifically refers to selecting to subscribe to one or more publications among the newly created one or more publications. After selecting the publication that needs to be subscribed to, select a subscription database for synchronizing the data published by the publisher, and create a subscription to the subscription database in the corresponding subscriber server.

Step S204: saving the configuration information of the publication and the subscription.

In a specific application, saving the configuration information of the publication and the subscription is for the convenience of invoking these configuration information when the one-key copy instruction set is executed later, so as to realize the one-key copy function.

Step S205: Establish a one-key copy instruction set associated with the configuration information.

In a specific application, the association relationship between the configuration information and the one-key copy instruction set may be a mapping relationship.

For example, the configuration information includes:

The publishing database A' corresponding to the publishing server A, the distribution server a, the distribution database a', the subscription server A" for subscribing the data in the publishing database A', and the subscription database a" for synchronously publishing the data in the database A';

The publishing database B', distribution server b, and distribution database b' corresponding to the publishing server B, the subscription server B" that subscribes to the data in the publishing database B', and the subscription database b" that synchronously publishes the data in the database B';

One-click copy instruction set includes:

A one-key copy command A"' used for one-key copying of data in the publishing database A' corresponding to the publishing server A;

A one-key copy instruction B"' for one-key copying of data in the publishing database B' corresponding to the publishing server B;

Then, the association relationship between the configuration information and the one-key copy instruction set is that A"' is associated with A, A', a, a', A", and a"; B"' is associated with B , B', b, b', B", and b' are associated.

Executing a set of one-click copy instructions including one-click copy instructions A"' and B"' means:

Control the publisher A and the distributor a to publish the data in the publishing database A’, and control the subscriber A” to synchronize the data in the publishing database A’ to the subscription database a”;

Control publisher B and distributor b to publish the data in the publishing database B', and control the subscriber server B" to synchronize the data in the publishing database B' to the subscription database b".

In one embodiment, after step S205 includes:

Add new publications and new subscriptions;

updating the configuration information according to the new publication and new subscription;

Modifying the saved configuration information of the publication and the subscription;

Or, delete the saved configuration information of the publication and the subscription.

By adding, modifying or deleting the pre-set configuration information, it is possible to change the published and subscribed data, so that users can create new published and subscribed, or modify and delete already created published and subscribed.

Embodiment three:

As shown in Figure 3, the replication maintenance system of the SQL Server database provided by this embodiment includes:

A verification module 101, configured to receive server login information and verify the server login information;

The server connection module 102 is used to connect to the pre-set publishing server, distribution server and subscription server if the server login information is verified;

The one-key copy module 103 is configured to receive a one-key copy instruction set, call the preset configuration information of the publishing server, distribution server, and subscriber server according to the one-key copy instruction set, and automatically create a new publication according to the configuration information and subscribe.

In one embodiment, the system further includes a maintenance module, configured to maintain the preset configuration information of the publisher, distributor and subscriber;

In a specific application, the maintenance module can maintain the preset configuration information of one or more publishers, one or more distributors and one or more subscribers, and the maintenance operation specifically includes:

Adding, modifying or deleting the preset configuration information of publishers, distributors and subscribers can also increase or delete the number of publishers, distributors and subscribers.

In a specific application, the one-key copy module 103 can simultaneously perform multiple publishing and subscribing operations according to the preset configuration information of multiple publishing servers and corresponding multiple distribution servers and multiple subscriber servers, so as to realize batch replication of databases function.

In a specific application, the data published by a publisher can be distributed by a distributor and can be subscribed by at least one subscriber, and multiple publishers can correspond to a distributor.

In one embodiment, the system also includes:

The one-key deletion module is used to receive the one-key deletion instruction set, delete the created publications and subscriptions according to the one-key deletion instruction set, and delete all the created publications and subscriptions with one key, or delete only part of the publications and subscriptions subscription.

Embodiment four:

As shown in Figure 4, the replication maintenance system of the SQL Server database provided by this embodiment also includes on the basis of Embodiment 4:

The server setting module 201 is used to pre-set the publishing server, the distribution server and the subscription server;

Release creating module 202, used for selecting a release database, selecting a distribution database, and creating a new release;

A subscription creation module 203, configured to select the newly created publication, select a subscription database, and create a new subscription;

A storage module 204, configured to store configuration information of the publication and the subscription;

A one-key copy instruction set establishing module 205, configured to establish a one-key copy instruction set associated with the configuration information.

In a specific application, the server setting module 201 is specifically used to pre-select and configure the configuration information of the publishing server, distribution server and subscription server.

In a specific application, the publishing creation module 202 is specifically used to select a publishing server for performing the new publishing operation, and then select the database to be published or all or part of the data in the database in the selected publishing server; The distribution server of the publishing operation, and then select the default distribution database in the selected distribution server. If there is no default distribution database, the default distribution database will be created automatically.

In a specific application, the number of new releases is used for custom settings, and the user can create one or more releases.

In a specific application, the subscription creation module 203 is specifically used to select to subscribe to one or more publications among the newly created one or more publications, and after selecting the publications that need to be subscribed, select the subscription to be used to synchronize the publications of the publishing server. A subscription database for the published data, and a subscription is created for the subscription database in the corresponding subscription server.

In a specific application, the storage module 204 can be a high-speed RAM memory, or a non-volatile memory (non-volatile memory), such as a disk memory.

In a specific application, the one-key copy instruction set establishing module 205 is further configured to establish a mapping relationship between the configuration information and the one-key copy instruction set.

In one embodiment, the system also includes:

Add modules for adding new publications and new subscriptions;

An update module, configured to update the configuration information according to the new publication and new subscription;

A modification module, configured to modify the saved configuration information of the publication and the subscription;

A deletion module, configured to delete the saved configuration information of the publication and the subscription.

By adding, modifying or deleting the pre-set configuration information, it is possible to change the published and subscribed data, so that users can create new published and subscribed, or modify and delete already created published and subscribed.

The modules in all the embodiments of the present invention can be realized by a general-purpose integrated circuit, such as a CPU (Central Processing Unit, central processing unit), or an ASIC (Application Specific Integrated Circuit, application-specific integrated circuit).

The steps in the methods of the embodiments of the present invention can be adjusted, combined and deleted according to actual needs.

The modules in the system of the embodiment of the present invention can be merged, divided and deleted according to actual needs.

The functions of the modules in the system of the embodiment of the present invention correspond to the steps in the method one by one.

Those of ordinary skill in the art can understand that all or part of the processes in the methods of the above embodiments can be implemented through computer programs to instruct related hardware, and the programs can be stored in a computer-readable storage medium. During execution, it may include the processes of the embodiments of the above-mentioned methods. Wherein, the storage medium may be a magnetic disk, an optical disk, a read-only memory (Read-Only Memory, ROM) or a random access memory (Random Access Memory, RAM) and the like.

The above descriptions are only preferred embodiments of the present invention, and are not intended to limit the present invention. Any modifications, equivalent replacements and improvements made within the spirit and principles of the present invention should be included in the protection of the present invention. within range.

Claims (8)

1. a copy maintenance method of SQL Server database, is characterized in that, described method comprises: receiving server login information and verifying the server login information; If the verification of the server login information is passed, then connect to the pre-set publishing server, distribution server and subscription server; Receiving a one-key copy instruction set, calling the preset configuration information of the publishing server, distribution server, and subscriber server according to the one-key copy instruction set, and automatically creating new publishing and subscription according to the configuration information; Before connecting to the preset publisher, distributor and subscriber, it includes: Publisher, Distributor and Subscriber are pre-configured; Select the release database, select the distribution database, and create a new release; among them, select the default distribution database in the selected distribution server, if there is no default distribution database, the default distribution database will be created automatically; Select one or more newly-created publications, select a subscription database, create a new subscription, and choose to subscribe to one or more publications in the newly-created one or more above-mentioned publications; saving the configuration information of the publication and the subscription; A one-key copy instruction set associated with the configuration information is established. 2. the duplication maintenance method of SQL Server database as claimed in claim 1, is characterized in that, comprises after setting up the one-key duplication instruction set associated with described configuration information: Add new publications and new subscriptions; updating the configuration information according to the new publication and new subscription; Modifying the saved configuration information of the publication and the subscription; Or, delete the saved configuration information of the publication and the subscription. 3. The replication maintenance method of the SQL Server database according to any one of claims 1 to 2, wherein the configuration information includes: The name of the publishing server set in advance, the computer user of the publishing server running the snapshot proxy process and the way the snapshot proxy connects to the publishing server, the name, user name and password of the publishing database, the preset; The name of the pre-set distribution server, the name of the distribution database, user name, password, file name and snapshot; The name of the preset subscriber server, the computer user running the process when the distribution agent synchronizes the subscription, and the way the distribution agent connects to the subscriber server, the name, user name, and password of the subscription database, and the preset subscriber server. 4. the replication maintenance method of SQL Server database as claimed in claim 1, is characterized in that, described method also comprises: Receive a one-key deletion instruction set, and delete created publications and subscriptions according to the one-key deletion instruction set. 5. a replication maintenance system of SQL Server database, is characterized in that, described system comprises: A verification module, configured to receive server login information and verify the server login information; A server connection module, configured to connect to a pre-set publishing server, distribution server and subscription server if the server login information is verified; A one-key copy module, configured to receive a one-key copy instruction set, call the preset configuration information of the publishing server, distribution server, and subscriber server according to the one-key copy instruction set, and automatically create a new publishing and subscription server according to the configuration information subscription; The server setting module is used to pre-set the publisher, distributor and subscriber; A release creation module is used to select a release database, select a distribution database, and create a new release; wherein, select a default distribution database in the selected distribution server, and automatically create a default distribution database if there is no default distribution database; The subscription creation module is used to select one or more newly-created publications, select a subscription database, create a new subscription, and choose to subscribe to one or more publications in the newly-created one or more above-mentioned publications; a storage module, configured to save configuration information of the publication and the subscription; A one-key copy instruction set establishing module, configured to establish a one-key copy instruction set associated with the configuration information. 6. the replication maintenance system of SQL Server database as claimed in claim 5, is characterized in that, described system also comprises: Add modules for adding new publications and new subscriptions; An update module, configured to update the configuration information according to the new publication and new subscription; A modification module, configured to modify the saved configuration information of the publication and the subscription; A deletion module, configured to delete the saved configuration information of the publication and the subscription. 7. The replication maintenance system of the SQL Server database according to any one of claims 5 to 6, wherein the configuration information includes: The name of the publishing server set in advance, the computer user of the publishing server running the snapshot proxy process and the way the snapshot proxy connects to the publishing server, the name, user name and password of the publishing database, the preset; The name of the pre-set distribution server, the name of the distribution database, user name, password, file name and snapshot; The name of the preset subscription server, the computer user who runs the process when the distribution agent synchronizes the subscription, and the way the distribution agent connects to the subscription server, the name, user name, and password of the subscription database, and the preset subscription server. 8. the replication maintenance system of SQL Server database as claimed in claim 5, is characterized in that, described system also comprises: The one-key deletion module is configured to receive a one-key deletion instruction set, and delete created publications and subscriptions according to the one-key deletion instruction set.