Sunday, January 10, 2016

Moving Data with Linked Databases

SSIS isn't the only game in town to move data between SQL Server databases. There are other options like BCP.exe, programs and scripts, and linked databases. I've created a an example solution to demonstrate how to use SQL server agent, linked database,s and some T-SQL to move data between databases.

To get the most out this post I assume that you have the following
  1. A good knowledge of basic T-SQL
  2. Familiar with SQL Server Agent
  3. Familiar with linked servers
  4. Know how to configure SQL mail for the SQL Server Agent
Some of my links point to, which requires registration. Register at the site, its worth it.

Note: This example was created with the AdventureWorks2014 demo database on a SQL Server Enterprise Installation, the AdventureWorksLT demo database installed on an SQL Azure database instance.  See my post here about linking to SQL Azure

'Fictional' Scenario

You company has a SQL Azure based customer database table. The business wants to send mailing to the customers. Since they will do multiple mailings you will need to insert new customers in this table as well as update existing customers with changes from the Azure and an other (not specified here) database. It is important that you are able to reference the customer number from both source databases. You only need to update the business address not the shipping addresses. 

Prep Work

In order to hold your address you need a mailing list table. Its based on the Customer and Address source table on the AzureLT I removed some unnecessary tables and added an identity as a record id, files from customer ID's from the Azure table and the 'other' database and some date fields.

Design Considerations

  1. Easy to debug.
  2. Notifications of failures.
  3. Minimize load on SQL Azure Server.
  4. Run daily at a scheduled time.
  5. All the processing is contained on the SQL server for easy management.

What I did

While the title of this post reference Linked Servers, the real core of this process is the SQL Server Agent Job Functionality.  With it I'm easily able to schedule the process, segregate logical parts of the process and, and manage notifications and logging.

Job Properties Setup

Things to note:
  1. Each part is a separate setup. If there is failure it should be pretty easy to figure out where the failure happened. If you look at the code, nothing is cleaned up on failure so its easier to debug.(Remember to clean up after you fix the problem.)
  2. Each Step is T-SQL, all you need to know to manage the process is SQL Server and T-SQL (No Powershell, SSIS, etc)
  3. Note that the 'On Success' action is to go to the next step, except for the last item.
  4. It goes without saying (so I'll say it anyway) that the order of the steps are important. 

General Setup for Each Step

Things to note:
  1. The type is always 'Transact-SQL Script (SQL)'
  2. I Just pasted the code from SSMS into the window. 
  3. I left the Database as 'master' because I was managing the the database from within the scrips with the USE command. 

Advanced Settings for each step except the last step

Things to Note:
  1. On success 'Go to the the next step'.
  2. On Failure 'Quit and report a failure'.
  3. Log to table, 'Append output to existing view', and 'Include step history in output'

Advanced Settings for the last step

Things to note:

The only difference from the others steps is the 'On Success Action' that should be 'Quit the job reporting success'


Things to note:

There's nothing special about the schedule setup.


Things to Note:
  1. I setup an operator in SQL Mail for the failure
  2. I log every completion in the event log,


Things to note:

In addition to seeing what step failed, you can press the 'View' button to see the log output for for a step for better debugging.

It this case I messed up the location of my USE Statement and the Database I needed wasn't in scope.

Code Samples

Here are links to the code that I used in each step of the job.

Code for the 'MailingList' table

Code for each of the job steps


With a little planning you can use linked servers, the SQL Server Agent, and some SQL move data between databases. This can make the process more manageable and easier for to maintain.


Please let me know via the comments if you have any questions or issues with the information in this post. Please also let me know if the format makes sense, and ways that It can be improved.

Thank you.

1 comment: