Shulou(Shulou.com)06/01 Report--

Background of Sqlserver remote backup practice project: the customer's offline IDC has a set of business systems running in the physical environment, and Tomcat8+Sqlserver2014 's internal process system runs on winodws 2012. For various reasons, it is necessary to transplant a copy of the offline environment to a private cloud internal environment as backup (if the offline environment encounters some uncontrollable human factors that can not be restored within a short period of time. The system in the private cloud environment can quickly pull up the business and resume the business operation in the shortest possible time. Project difficulties: 1) in the private cloud environment of the destination, there are many restrictions and security considerations. By default, all uplink and downlink ports are not open and cannot be imported by remote mirror, which directly limits the construction of the software environment. 2) the real-time synchronization process of Sqlserver2014 also has certain requirements for the IO of network and disk, the reliability of real-time synchronization has not been tested in the actual environment, and the 100% stability and reliability of the synchronization link can not be guaranteed. The software installation packages needed for the basic running environment are adapted and confirmed in other environments, and then uploaded using the cloud disk tools deployed inside the private cloud, combined with the specified trusted mirror source (https://opsx.alibaba.com/mirror/), and passed the on-network test. After the construction of the basic environment, compress and copy the source code package running by Tomcat to the target server, decompress it, and pay attention to the permissions of the Tomcat environment. C.Sqlserver database publish and subscribe configuration (PS: it is best to set the user name and password of the source environment and the target environment the same, to avoid mistakes in the later configuration; D. File-level data timing synchronization plan, through rsync+openssh encrypted transmission to cooperate with the scheduled task to synchronize file data to the target server; E. For the reliability of the database synchronization link, it is finally decided to start the maintenance plan on the source side of the database, generate a full backup .bak file of the database at a regular time every day and store it in the directory specified by rsync synchronization, and copy a copy of the bak file to the target server to ensure data security. POC testing phase: after the scheme is confirmed, you can carry out the POC test according to the established plan. The installation process of the basic environment can be omitted. (note that which external URL needs to be used during the installation process and which ports need to work together, which need to be confirmed in advance. Later, you need to apply for opening through the firewall policy. ) Database subscription publication configuration process is as follows: the target environment installs the corresponding version of the database (here sqlserver2014 EE ed2k:// | file | cn_sql_server_2014_enterprise_edition_x64_dvd_3932882.iso | 2898847744 | A33CE10CD989083D1AD882DF0D56CFBE | /) before configuring the publish subscription, you need to ensure that the source and target servers can find each other through the corresponding servername. The most direct way is to add the corresponding x.x.x.x sqlservername records in the hosts files of the source and destination servers, and test that the database can be connected successfully through sqlservername. The error data will be reported through the ip address direct connection to ensure that the configuration can be started after the sqlservername connection, and the source side publishes replication.

New release

Follow the wizard to configure

Snapshot storage location (the default location is in the directory corresponding to the database instance, which can be modified or not modified according to the actual situation)

Select the corresponding database

Select publication type

Publication type description

Snapshot publication:

The publisher sends a snapshot of the published data to the subscriber at scheduled intervals.

Transaction publication:

After the subscriber receives the initial snapshot of the published data, the publisher streams the transaction to the subscriber.

Peer-to-peer publishing:

Peer-to-peer publishing supports multi-master replication. The publisher streams the transaction to all peers in the topology. All peer nodes can read and write changes, and all changes are propagated to all nodes in the topology.

Merge publication:

After the subscriber receives the initial snapshot of the published data, the publisher and subscriber can update the published data independently. Changes are merged periodically. Microsoft SQL Server Compact Edition can only subscribe to merge publications.

Select the appropriate publication type and select the objects to be published

Filter to remove unwanted data (no need to add)

Generate and run the Snapshot Agent

Security settings (configure proxy username and password)

-it is recommended to configure it in the following way (currently testing the configuration that there is no problem)-

Create a publication

Specify the release name (it is more reliable to choose according to the project name, of course, which name does not affect the operation)

Waiting for publication to be created

View created publications

Create a subscription on the target side

Select a publisher (select a published project after connecting to the publisher by looking for sql)

Select the location of the distribution agent (it is recommended to choose the push method on the publisher, which is currently tested to have the highest success rate)

Select subscription database

Security settings (similar to those in a publication, pay attention to the distinction between the publisher side and the subscriber side)

-it is recommended to configure it in the following way.

Confirm the operation mode according to the actual situation (default continuous operation)

Set initialization time (default immediately)

Create a subscription

Wait for the subscription to take effect

View subscription synchronization links

At this point, the publish and subscribe process for sqlserver is complete.

It is recommended to configure regular maintenance tasks to generate .bak backups of the database periodically and synchronize them to the target side regularly. The windows encryption synchronization process refers to the next document.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.