SQL Mirroring – Online SQL Database Data Transfer

I explain the SQL Mirroring method, which is one of the preferred methods for transferring database tables and the data they contain, both as a backup task between Microsoft SQL Servers and when necessary, with step-by-step screenshots.

We will explain what you want to do and how to do it through this picture that we use to understand the subject.

The Wrong Scenario for SQL Mirroring

An employee in Ankara branch of a company headquartered in Istanbul will transfer the MS-SQL database of the application he developed to the Cluster SQL server in Istanbul.

The first thing that comes to mind for this is to create a backup file of the new database created on the server in Ankara and send it to the System Administrator in Istanbul via email or FTP. Although this is also a method, it is not preferred due to reasons such as version difference or security vulnerability.

The Right Scenario for Database Backup

Instead, we will demonstrate the much safer and easier SQL Server Export Database method in detail. Before starting this process, let’s start by assuming that the -IP, -authorization, -username and -password parameters have been created and delivered to you by your System administrator on Cluster Servers.

The narration will be completely supported by screenshots. The narration is in the form of a picture at the top and a description at the bottom. You can therefore click on the images to see them in more detail.

SQL Management

1. Open SQL Management with“sa” i.e. Super Admin authorization and right click on the database to be mirrored to Cluster. Select Tasks>Export Data option.

SQL Server Import and Export Wizard

2. In the SQL Server Import and Export Wizard screen that will open, let’s proceed to the next step with Next.

Choose a Data Source

3. In the Choose a Data Source section, you will be asked for Source, that is, information about your server. In the Database Source section, select SQL Server Native Client 11.0 . Since it is your own server, in the Server Name section, select your server’s IP address, server name, localhost or “.” dot sign will be enough.

Then you need to check Use SQL Server Authentication. Enter your valid SQL information in the user name and password boxes that will appear just below. After this stage, when you press the Refresh button, the database names on your server will be added to the check box on the left. Select the database you want to copy from here and proceed to the next step with the Next button.

Choose a Destination

4. In the Choose a Destination screen, you will be asked to enter the information of the Cluster SQL server, which is the other party, similar to step 3. However, an issue that may be a problem here is the Server Name section.

Entering the server name for server name here may cause connection errors in some cases(when using firewall, Active Directory etc.). For this reason, it is best to enter the IP address. After typing the username and password, the list of databases opened and authorized for you on the opposite Cluster server will appear. The names of the databases you will copy must be the same on both servers. Select the database name and proceed to the next step with the Next button.

Specify Table Copy or Query

5. In the Specify Table Copy or Query screen, it is asked by which method the tables and data, if any, will be transferred to the other party with sql mirroring. Let’s go to the next step by selecting“Copy data from one or more tables or views“.

Select Source Tables and Views

6. On the Select Source Tables and Views screen, the tables on the source server and the tables to be created on the destination server are listed. You are asked which of these you will transfer. Select all with the“select all” option at the top and proceed to the next step.

Save and Run Package

7. In the Save and Run Package screen, the Run immediately option, which is predefined for sql mirroring, is selected. If you have an SSL certificate, the transfer process is securely encrypted with SSL Security Certificate.

If you do not have an SSL certificate, you can also perform the transfer with the simple encryption procedure of mutual transfer. If the network has VPN communication with mutual firewall devices, none of this will be necessary. The Run Immediately option will work on its own. Proceed to the next step with the Next button.

The Last Screen – Summary

8. On the Complate the Wizard page, a summary of the settings you have made for sql mirroring is written. After checking, proceed to the next step with the Finish button.

Performing Operation

9. The Performing Operation screen shows the completion percentage of the new tables created. If you can see the text“The execution was successful” on the screen when the operation ends here, the operation is complete. If there are errors, troubleshoot these errors by looking at the details of these errors.

After these operations are completed, ask your System Administrator in Istanbul to check the database. You have sent the data of your own database to the remote server with SQL Export Data method. Please contact us for questions and problems.