A method, device, electronic device and storage medium for building high-availability technology for SQL Server database

Abstract

The present application relates to a method, device, electronic device and storage medium for building a SQL Server database with high availability technology, which belongs to the technical field of databases, wherein the method comprises the following steps: receiving a building request sent by a first user, wherein the building request carries a first node address; determining a primary node associated with the first node address; creating mirror information corresponding to the primary node based on a preset script file; building an auxiliary node according to the mirror information and the first node address and a preset AlwaysOn configuration function, and generating building information; and feeding back the building information to the first user. The SQL server automatically creates auxiliary nodes for the database, reducing the work that users need to do to create them manually. The present application has the effect of improving user work efficiency.

Description

Technical Field

[0001] The present application relates to the technical field of databases, and in particular to a method, device, electronic device and storage medium for building high-availability technology for a SQL Server database. Background Art

[0002] Currently, with the introduction of SQL Server database, users can query any data in the SQL Server database through search formulas to improve the efficiency of users in obtaining data.

[0003] In the related art, when a user creates a new node (i.e., a secondary node) in a SQL Server AlwaysOn availability group, the user obtains a file directory of a primary node through the SQL Server operation interface, and then selects a node (i.e., the first node) and copies the obtained file directory to the first node. The user associates the primary node with the node address of the first node (i.e., the first node address) so that data can be exchanged between the first node and the primary node.

[0004] In the process of implementing this application, the inventor found that there are at least the following problems in this technology:

[0005] When each auxiliary node is associated with the primary node, manual operation is required by the user, and the operation process is cumbersome, resulting in reduced user work efficiency. Summary of the invention

[0006] In order to improve the problem that it is cumbersome for users to manually build a SQL Server database, the present application provides a method, device, electronic device and storage medium for building a SQL Server database with high availability technology.

[0007] In the first aspect, the present application provides a method for building a SQL Server database high availability technology, using the following technical solution:

[0008] A method for building a SQL Server database high availability technology, the method comprising the following steps:

[0009] Receiving a setup request sent by a first user, wherein the setup request carries a first node address;

[0010] Determining a master node associated with the first node address;

[0011] Based on a preset script file, create mirror information corresponding to the master node;

[0012] According to the mirror information and the first node address, according to the preset AlwaysOn configuration function, build the auxiliary node and generate the building information;

[0013] Feedback the construction information to the first user.

[0014] By adopting the above technical solution, the SQL Server server receives a setup request sent by a certain user (i.e., the first user) and determines the primary node associated with the first node address. The script file of the SQL Server server can create mirror information based on the primary node, and build the auxiliary node according to the preset AlwaysOn configuration function, and feed back the setup information generated by building the auxiliary node to the first user. When the SQL Server server receives the request, it automatically creates the mirror information of the primary node and transmits it to the auxiliary node corresponding to the first node address, so that a new auxiliary node can be automatically created for a certain primary node, effectively reducing the workload of manual creation by users and improving the work efficiency of users.

[0015] Optionally, determining the master node associated with the first node address comprises the following steps:

[0016] Selecting a pre-stored database address corresponding to the first node address;

[0017] According to the database address, a database and an associated master node are determined.

[0018] By adopting the above technical solution, the primary node of the SQL Server database will change because the primary node cannot meet the storage requirements, and the newly created secondary node will be associated with the primary node before the change, resulting in errors in the creation of the new secondary node. The SQL Server server pre-stores the addresses of all databases (i.e., database addresses), and the first node address of the new secondary node can be associated with the database address. The SQL Server server determines the current primary node based on the database address, thereby effectively improving the accuracy of creating secondary nodes.

[0019] Optionally, the method further comprises the following steps:

[0020] receiving update information uploaded by a second user, wherein the update information carries the address of the first database;

[0021] Determining a first master node corresponding to the first database address;

[0022] According to the update information, the first master node is updated;

[0023] According to the preset delayed update model, all auxiliary nodes associated with the first primary node are updated.

[0024] By adopting the above technical solution, when the SQL Server server updates the first primary node, the SQL Server server updates all secondary nodes synchronously or asynchronously according to the preset delayed update model, thereby reducing the need for users to manually update the secondary nodes and improving user work efficiency.

[0025] Optionally, the creating the mirror information corresponding to the master node based on a preset script file comprises the following steps:

[0026] Based on the preset Powershell module, call the pre-stored script file;

[0027] The node characteristics of the master node are input into the script file, and the mirror information corresponding to the master node is obtained.

[0028] By adopting the above technical solution, the SQL Server server stores a large number of script files. Retrieving the corresponding script files through the Powershell module helps to improve the efficiency of obtaining mirror information, thereby further improving the efficiency of creating new auxiliary nodes.

[0029] Optionally, after generating the building information, the following steps are included:

[0030] Add the setup information to the preset SQL Server verification page to get the verification result;

[0031] The verification result is added to the build information.

[0032] By adopting the above technical solution, the SQL Server server is pre-set with a verification page, which can verify whether the data of the build information is accurate and generate corresponding verification results. The verification results are added to the build information so that users can judge whether there are errors in the newly created auxiliary node through the verification results.

[0033] Optionally, the method further comprises the following steps:

[0034] Acquire a change request sent by a third user, where the change request carries a second database address of the second database;

[0035] Determine a second primary node and each auxiliary node to be selected corresponding to the second database address;

[0036] Selecting a preferred auxiliary node from among the auxiliary nodes to be selected according to a preset priority order;

[0037] Obtaining association information between the second database and the second master node;

[0038] Based on the preferred auxiliary node, the association information is updated to make the preferred auxiliary node a new primary node.

[0039] By adopting the above technical solution, when a user sends a change request, the SQL Server server selects a preferred secondary node according to the preferred order and uses the preferred secondary node as the new primary node to meet the user's storage requirements for the primary node.

[0040] Optionally, after determining the second primary node and each auxiliary node to be selected corresponding to the second database address, the following steps are further included:

[0041] Updating the second node address of the second master node into a preset idle node comparison table;

[0042] When the number of nodes in the idle node comparison table exceeds a preset node remaining threshold, a prompt message indicating that the idle node remaining amount is excessive is sent to the fourth user.

[0043] By adopting the above technical solution, the SQL Server server records the disassociated master nodes in the idle node comparison table. The SQL Server server also pre-sets a node remaining storage threshold, so that when the number of disassociated master nodes exceeds the node remaining storage threshold, the SQL Server server gives feedback to the user, which helps the user to process the disassociated master nodes.

[0044] In the second aspect, the present application provides a SQL Server database high availability technology construction device, which adopts the following technical solution:

[0045] A SQL Server database high availability technology construction device, comprising:

[0046] A first receiving module, configured to receive a setup request sent by a first user, wherein the setup request carries a first node address;

[0047] A first determining module, used to determine a master node associated with the first node address;

[0048] A creation module, used to create mirror information corresponding to the master node based on a preset script file;

[0049] A building module, used to build an auxiliary node according to the mirror information and the first node address and a preset AlwaysOn configuration function, and generate configuration information;

[0050] A feedback module is used to feed back the configuration information to the first user.

[0051] By adopting the above technical solution, when the SQL Server server receives a request, it automatically creates the mirror information of the primary node and transmits it to the secondary node corresponding to the first node address, so that a new secondary node can be automatically created for a certain primary node, effectively reducing the workload of manual creation by users and improving user work efficiency.

[0052] In a third aspect, the present application provides an electronic device, which adopts the following technical solution:

[0053] Optionally, the electronic device includes a processor and a memory, the memory storing at least one instruction, at least one program, a code set or an instruction set, and the at least one instruction, the at least one program, the code set or the instruction set are loaded and executed by the processor to implement a SQL Server database high availability technology construction method as described in the first aspect.

[0054] By adopting the above technical solution, an electronic device can implement the above-mentioned SQL Server database high-availability technology construction method according to the relevant computer program stored in the memory, thereby improving the collaboration between information from different sources when building auxiliary nodes, thereby improving the accuracy of building auxiliary nodes.

[0055] In a fourth aspect, the present application provides a computer-readable storage medium, which adopts the following technical solution:

[0056] Optionally, the storage medium stores at least one instruction, at least one program, a code set or an instruction set, and the at least one instruction, the at least one program, the code set or the instruction set are loaded and executed by the processor to implement a SQL Server database high availability technology construction method as described in the first aspect.

[0057] By adopting the above technical solution, the corresponding program can be stored, thereby improving the collaboration between information from different sources when building auxiliary nodes, thereby improving the accuracy of building auxiliary nodes.

[0058] In summary, the present application includes at least one of the following beneficial technical effects:

[0059] When receiving a request, the SQL Server server automatically creates the mirror information of the primary node and transmits it to the secondary node corresponding to the first node address, so that a new secondary node can be automatically created for a certain primary node, effectively reducing the workload of manual creation by users and improving user work efficiency;

[0060] The primary node of the SQL Server database will change because the primary node cannot meet the storage requirements. The newly created secondary node will be associated with the primary node before the change, resulting in errors in the creation of the new secondary node. The SQL Server server pre-stores the addresses of all databases (i.e., database addresses). The first node address of the new secondary node can be associated with the database address. The SQL Server server determines the current primary node based on the database address, thereby effectively improving the accuracy of creating secondary nodes;

[0061] When the SQL Server server updates the first primary node, the SQL Server server updates all secondary nodes synchronously or asynchronously according to the preset delayed update model, reducing the need for users to manually update the secondary nodes and improving user work efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0062] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0063] Figure 1 It is a structural block diagram of a SQL Server database high availability technology construction device in an embodiment of the present application.

[0064] Figure 2 It is a flow chart of a method for building a SQL Server database high availability technology according to an embodiment of the present application.

[0065] Figure 3 It is a flow chart of a device for building a SQL Server database high availability technology according to an embodiment of the present application. DETAILED DESCRIPTION

[0066] In order to make the objectives, technical solutions and advantages of the present application clearer, the implementation methods of the present application will be further described in detail below with reference to the accompanying drawings.

[0067] The embodiment of the present application provides a method for constructing a SQL Server database with high availability technology, which can be applied to a device for constructing a SQL Server database with high availability technology. The framework structure of a device for constructing a SQL Server database with high availability technology can be as follows: Figure 1As shown, it may include a SQL Server server and multiple user terminals. The SQL Server server is subsequently referred to as a server, and the user terminal may be a computer or a mobile terminal. Specifically, the execution subject of the method may be a server, and it may be implemented with the assistance of a terminal. When the server receives a build request sent by a user terminal, the server builds a new auxiliary node for the corresponding SQL Server database, and feeds back corresponding build information to the user terminal. Specifically, the service end receives a build request sent by the user terminal, and the server determines the associated SQL Server database according to the build request, and builds a new auxiliary node. The server associates the SQL Server database with the auxiliary node and generates build information, builds static parameters for characterizing the auxiliary node, and the server feeds back the build information to the user, so that the user can judge whether the auxiliary node has been built through the build information.

[0068] The following will be combined with specific implementation methods. Figure 2 The processing flow shown is described in detail, and the content can be as follows:

[0069] Step 201: receiving a setup request sent by a first user, where the setup request carries a first node address.

[0070] In an embodiment, the server receives a setup request sent by a certain user (ie, a first user), and the setup request carries an address of a certain node (ie, a first node address), and the node address may be an IP address of a certain database.

[0071] Step 202: determine the master node associated with the first node address.

[0072] In an embodiment, the server determines the master node associated with the first node address, and the master node is used to represent the sub-database with the highest weight in the SQL Server AlwaysOn availability group, and the weight can be pre-set by a technician in the SQL Server operation interface. When the server retrieves a file from the SQL Server AlwaysOn availability group, it can preferentially screen whether there is a sub-directory matching the file from the file directory of the master node.

[0073] Optionally, a pre-stored database address corresponding to the first node address is selected, and a database and an associated master node are determined according to the database address.

[0074] In an embodiment, the server pre-stores the database addresses of various databases, and the database stores different types of files in the form of groups. In an embodiment of the present application, the database can be represented as a SQL Server AlwaysOn availability group. The technician can pre-set a certain node address to be associated with a certain database address. The server selects the database address corresponding to the first node address, and the server determines the database and the associated master node based on the database address.

[0075] Step 203: Create mirror information corresponding to the master node based on a preset script file.

[0076] In an embodiment, the server is pre-set with a script file. After obtaining a certain master node, the server creates mirror information corresponding to the master node. The mirror information may be information such as a user name, a SID, and a password.

[0077] Optionally, based on a preset Powershell module, a pre-stored script file is retrieved, the node characteristics of the master node are input into the script file, and the image information corresponding to the master node is obtained.

[0078] In an embodiment, the server is pre-set with a Powershell module, and the server retrieves a pre-stored script file through the Powershell module. The server inputs the node characteristics of the master node into the script file and obtains the mirror information corresponding to the master node.

[0079] Step 204: According to the mirror information and the first node address, and in accordance with the preset AlwaysOn configuration function, an auxiliary node is constructed and construction information is generated.

[0080] In an embodiment, the server is pre-set with an AlwaysOn configuration function, which specifically includes inter-node security configuration and creation of application database backup. The inter-node security configuration can be to copy the key of the primary node to the node corresponding to the first node address through the mirror information and the first node address, and the application database backup can be to back up the file stored in the primary node to the node corresponding to the first node address through the mirror information and the first node address. The server builds the auxiliary node according to the preset AlwaysOn configuration function based on the mirror information and the first node address, and generates the building information. The building information is used to characterize the attributes associated with the auxiliary node and the SQL Server database, and the associated attributes can be the SQLServer database IP address, database name, synchronization or asynchrony, and port number. When an auxiliary node is updated synchronously with the primary node, the auxiliary node is also updated synchronously when the data of the primary node is updated. When an auxiliary node is updated asynchronously with the primary node, the auxiliary node is updated with a delay when the data of the primary node is updated, and the delay can be 1ms or 2ms.

[0081] Optionally, add the build information to the preset SQL Server verification page to obtain the verification result. Add the verification result to the build information.

[0082] In an embodiment, after the server generates the setup information, the server is pre-set with a SQL Server verification page, and the SQL Server verification page is used to feedback the verification result to the server when the setup information is input. The SQL Server verification page determines whether the auxiliary node is associated with the main node based on the setup information, and obtains a corresponding judgment result (i.e., a verification result). The judgment result may be that the auxiliary node is associated with the main node or that the auxiliary node is not associated with the main node. That is, the server adds the setup information to the preset SQL Server verification page to obtain the verification result. The verification result is added to the setup information.

[0083] Step 205: Feedback the construction information to the first user.

[0084] In an embodiment, the server feeds back the construction information to a certain user (ie, the first user).

[0085] Optionally, update information uploaded by a second user is received, the update information carrying the first database address. A first primary node corresponding to the first database address is determined, and the first primary node is updated according to the update information. All secondary nodes associated with the first primary node are updated according to a preset delayed update model.

[0086] In an embodiment, the server receives update information uploaded by a certain user (second user), and the update information carries the first database address. The server determines the first master node corresponding to the first database address, and updates the first master node according to the update information. The update may be a replacement or modification of a file stored in the first master node. The server is pre-set with a delayed update model, which is used to delay the update of one or some auxiliary nodes associated with the first master node. The delay time may be 1ms, and the delayed update model may be set by a technician through the SQL Server operation interface to achieve the purpose of reducing CPU usage when updating the database.

[0087] Optionally, a change request sent by a third user is obtained, the change request carrying the second database address of the second database. A second primary node and each candidate auxiliary node corresponding to the second database address are determined. Among the candidate auxiliary nodes, a preferred auxiliary node is selected according to a preset preferred order. Association information between the second database and the second primary node is obtained, and the association information is updated based on the preferred auxiliary node so that the preferred auxiliary node serves as the new primary node.

[0088] In an embodiment, the server obtains a change request sent by a third user, and the change request carries a database address (i.e., the second database address) of a certain database (i.e., the second database). The server determines the second main node corresponding to the second database address and each auxiliary node to be selected. The server pre-sets a preferred order, and the preferred order can sort each auxiliary node according to the storage capacity of each auxiliary node or the user's frequency of use. The server selects a preferred auxiliary node from each auxiliary node to be selected according to the preset preferred order. The server obtains the association information between the second database and the second main node, and the association information can be the port and IP address used. Based on the preferred auxiliary node, the association information is updated, and the update can be to replace the original main node with the preferred auxiliary node so that the preferred auxiliary node is used as the new main node.

[0089] Optionally, the second node address of the second master node is updated to a preset idle node comparison table. When the number of nodes in the idle node comparison table exceeds a preset node remaining threshold, a prompt message indicating that the idle node remaining amount is too much is sent to the fourth user.

[0090] In an embodiment, after the server determines the second master node corresponding to the second database address and each auxiliary node to be selected, the server pre-sets an idle node comparison table, and the idle node comparison table is used to record the address of the replaced master node. The server updates the second node address of the second master node to the idle node comparison table. The server pre-sets a node remaining storage threshold, and the node remaining storage threshold corresponds to the number of nodes in the idle node comparison table. When the server recognizes that the number of nodes in the idle node comparison table exceeds the preset node remaining storage threshold, a prompt message of excessive idle node remaining is sent to a certain user (i.e., the fourth user). At the same time, when the number of nodes in the idle node comparison table exceeds the node remaining storage threshold, the server will also clear all the master node addresses in the idle node comparison table.

[0091] Based on the same technical concept, the embodiment of the present application also discloses a SQL Server database high availability technology construction device, a SQL Server database high availability technology construction device includes a SQL Server server, such as Figure 3 As shown, the SQL Server server includes:

[0092] A first receiving module is used to receive a setup request sent by a first user, where the setup request carries a first node address;

[0093] A first determination module, used to determine a master node associated with the first node address;

[0094] A creation module is used to create image information corresponding to the master node based on a preset script file;

[0095] A building module is used to build an auxiliary node and generate configuration information according to the mirror information and the first node address and the preset AlwaysOn configuration function;

[0096] The feedback module is used to feed back configuration information to the first user.

[0097] Optionally, a first selection module is used to select a pre-stored database address corresponding to the first node address;

[0098] The second determination module is used to determine the database and the associated master node according to the database address.

[0099] Optionally, a second receiving module is used to receive update information uploaded by a second user, where the update information carries the address of the first database;

[0100] A third determination module, used to determine a first master node corresponding to the first database address;

[0101] A first updating module, used for updating the first master node according to the update information;

[0102] The second updating module is used to update all auxiliary nodes associated with the first primary node according to a preset delayed updating model.

[0103] Optionally, a calling module is used to call a pre-stored script file based on a preset Powershell module;

[0104] The first obtaining module is used to input the node characteristics of the master node into the script file and obtain the mirror information corresponding to the master node.

[0105] Optionally, a second obtaining module is used to add the setup information to a preset SQL Server verification page to obtain a verification result;

[0106] The first adding module is used to add the verification result to the building information.

[0107] Optionally, the first acquisition module is used to acquire a change request sent by a third user, where the change request carries a second database address of the second database;

[0108] A fourth determination module, used to determine the second primary node and each auxiliary node to be selected corresponding to the second database address;

[0109] A second selection module is used to select a preferred auxiliary node from among the auxiliary nodes to be selected according to a preset priority order;

[0110] A second acquisition module, used to acquire association information between the second database and the second master node;

[0111] The third updating module is used to update the associated information based on the preferred auxiliary node so that the preferred auxiliary node serves as the new primary node.

[0112] Optionally, a fourth updating module is used to update the second node address of the second master node into a preset idle node comparison table;

[0113] The sending module is used to send a prompt message indicating that the idle node surplus is excessive to the fourth user when the number of nodes in the idle node comparison table exceeds a preset node surplus threshold.

[0114] An embodiment of the present application also discloses an electronic device, including a memory and a processor, wherein the memory stores a computer program that can be loaded by the processor and executes a method for building a SQL Server database high availability technology as described above.

[0115] An embodiment of the present application also discloses a computer-readable storage medium, which stores a computer program that can be loaded by a processor and execute a method for building a SQL Server database high-availability technology as described above. The computer-readable storage medium includes, for example: a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk, and other media that can store program codes.

[0116] The above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit the protection scope of the application. Obviously, the described embodiments are only some embodiments of the present application, rather than all embodiments. Based on these embodiments, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present application.

Claims

1. A method for building a SQL Server database high availability technology, characterized in that: The method comprises the following steps: Receiving a setup request sent by a first user, wherein the setup request carries a first node address; Determining a master node associated with the first node address; Based on a preset script file, create mirror information corresponding to the master node; According to the mirror information and the first node address, according to the preset AlwaysOn configuration function, build the auxiliary node and generate the building information; Feedback the construction information to the first user; receiving update information uploaded by a second user, wherein the update information carries the address of the first database; Determining a first master node corresponding to the first database address; According to the update information, the first master node is updated; According to the preset delayed update model, all auxiliary nodes associated with the first primary node are updated.

2. A method for building a SQL Server database high availability technology according to claim 1, characterized in that: The step of determining the master node associated with the first node address comprises the following steps: Selecting a pre-stored database address corresponding to the first node address; According to the database address, a database and an associated master node are determined.

3. A method for building a SQL Server database high availability technology according to claim 1, characterized in that: The step of creating the mirror information corresponding to the master node based on the preset script file includes the following steps: Based on the preset Powershell module, call the pre-stored script file; The node characteristics of the master node are input into the script file, and the mirror information corresponding to the master node is obtained.

4. A method for building a SQL Server database high availability technology according to claim 1, characterized in that: After the building information is generated, the following steps are included: Add the setup information to the preset SQL Server verification page to get the verification result; The verification result is added to the build information.

5. A method for building a SQL Server database high availability technology according to claim 1, characterized in that: The method further comprises the following steps: Acquire a change request sent by a third user, where the change request carries a second database address of the second database; Determine a second primary node and each auxiliary node to be selected corresponding to the second database address; Selecting a preferred auxiliary node from among the auxiliary nodes to be selected according to a preset priority order; Obtaining association information between the second database and the second master node; Based on the preferred auxiliary node, the association information is updated to make the preferred auxiliary node a new primary node.

6. A method for building a SQL Server database high availability technology according to claim 5, characterized in that: After the second primary node and each auxiliary node to be selected corresponding to the second database address are determined, the following steps are also included: Updating the second node address of the second master node into a preset idle node comparison table; When the number of nodes in the idle node comparison table exceeds a preset node remaining threshold, a prompt message indicating that the idle node remaining amount is excessive is sent to the fourth user.

7. A device for building a SQL Server database high availability technology, characterized in that: include: A first receiving module, configured to receive a setup request sent by a first user, wherein the setup request carries a first node address; A first determining module, used to determine a master node associated with the first node address; A creation module, used to create mirror information corresponding to the master node based on a preset script file; A building module, used to build an auxiliary node according to the mirror information and the first node address and a preset AlwaysOn configuration function, and generate configuration information; A feedback module, used to feed back the configuration information to the first user; A second receiving module, used to receive update information uploaded by a second user, where the update information carries the address of the first database; A third determination module, used to determine a first master node corresponding to the first database address; A first updating module, used for updating the first master node according to the update information; The second updating module is used to update all auxiliary nodes associated with the first primary node according to a preset delayed update model. new.

8. An electronic device, characterized in that: The method comprises a memory and a processor, wherein the memory stores a computer program which can be loaded by the processor and executes the method according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that: A computer program is stored which can be loaded by a processor and execute the method according to any one of claims 1 to 6.

Images