Transferring Data from Local to a Remote Database

This article is for all the DBA’s out there who, like me, have to transfer data from a local database to a remote one quite often.

I know that there are many ways of doing this but my most recent experience with trying to find one has made me make this post.

Please note that I will assume you already know how to create your scripts, prepare your tables and import your data using SSIS, etc.  

As an example; let’s say we have created some user accounts on our local machine (Server1) then want to transfer them to our central server (Server2).  

So here they are:

Server1:

Server2:

In this article, we will work with the following SSIS packages which you can download from here.  For those of you who do not want to go through all that trouble or would rather go with a better solution, I suggest reading about SQL Server “Transfer” feature that was introduced in 2008 R2 as it is much easier and more efficient. Transferring Data between Servers by Using SQL Server 2008 R2 link.  It does require two times of executions though since it fetches all data at once on both sides and then transfers only the changes (Update statement).

With our packages ready, let’s get to our next step – configuring the server connection.  This will be a simple task as you should already have your servers set up properly.

The only thing we have to do here is to prepare all the scripts that will create those users on Server2.  In this example, I am going to use 4 scripts:

1- Create a user called “sheriff” with the password “sheriff12” and connect it to a database named “Test”.

2- Create a user called “bob” with the password “bob12” and connect it to a database named “Test”.

3- Create a role called “deputy_role” with the creation option set to true.

4- Assign our privilege above to this role.  (This will be created on Server1)

So we have all we need in order to transfer these users from one side to another but before we can do that, we need to create our SSIS package and add in the connection manager:

Add References in the Connection Manager: 

After creating your connection manager within your SSIS package, go back and add references under properties of the Connection Manager tab.  Here you should see the following:

At this point, remember to remove the $$$$ signs within your connection manager because they are there just for our own reference.  After you have done so, let’s move on to transferring data between servers using SSIS.

Transferring User Accounts with Power Shell Scripts:

Now if this is the first time you will be doing something like this, we might want to do a quick check and see how it works out before we go forward with our goal – transferring our users from Server1 to Server2. So let’s start by running our simple Power Shell scripts that will create and connect those user accounts and roles on the new server (Server2).

We’ve looked at lots of ways to transfer data from a remote database into your local device. We used the sync framework to do it, we built our own specialized app using Kinvey Studio and we even did it manually via CSV files. But what if we could do all these tricks while still working within the comfort of our native apps? That’s where Remote Buddies come in: this tool provides us with everything needed to script and automate the whole process so that you can focus on creating great apps. In this tutorial, I will show you how to use Remote Buddies from your Android or iOS devices by creating a simple app that transfers contacts from an address book.

Prerequisites & Tools

To follow this article, you must have:

Kinvey account (sign up for free here ) Kinvey Android or iOS app installed ( download here ) A computer with Unix shell access to run the scripts (check the prerequisites section of this tutorial) Basic knowledge of Unix scripting tools, like sed and curl The application that we will create in this tutorial is inspired by the official Gmail Contacts transfer app. This example transfers contacts from your device’s address book (the system default), but you can easily modify it to automate any other data transfer operation. For the sake of simplicity, let’s start by creating a simple “one-way” script that will copy all contacts from your iPhone to Kinvey. The assumption is that all contacts are synced to iCloud, so it will be necessary to first download your iPhone contacts into an Excel file. Once you have this file, the script should upload the data into Kinvey using the iOS app. 

Conclusion:

Now you can easily transfer data from your remote server to your local device using the Kinvey technology.  We have learned how to use Remote Buddies to automate this transfer process in an efficient and simple way.