Best Way to Get Data from Linked Server

Other SQL Server Topics
1

I am trying to determine the best way to import data from a 2008R2 linked Server database containing only 400+views into a SQL 2012 database. I want to be able to place the data into staging tables and also check to see if any of the views changed since the last import.

2

one-time or ongoing?

3

If it's on-going, I'd setup replication.

4

Depends on the purpose. If the 2008R2 server is OLTP and the 2012 server is a DW for OLAP, replication is not the way to go. You need a properly-built ETL process

5

Sorry I haven't responded in a while, I have been pulled into other areas.

  1. this would be on-going, the linked server is updated on predetermined intervals, Weekly I believe is what we are going with,

  2. I am curious to know if I should anticipate any issues since the data is coming from Views and not tables

  3. The SQL 2008 db is based on a Transactional DB2 database, but the originating company has determined that we can only see the data contained in the supplied views.

  4. we have also noticed that the views can change slightly from time to time.

6

What is the purpose of the target db?

If you use views that "can change slightly from time to time" you will have constant, ongoing, catch-up maintenance. Not pretty.

7

Hey Swoozie.. have been trying to get a reply to you on the old forum for "Policy Based Management - Encryption".

You do not have to write a ExecuteSQL to determine the Force Encryption setting. Create a new condition, use the Server Protocol Settings facet and you will now see a Field for @ForceEncryption.

Hope that helps!

8

Thank you, I actually ended up writing some wonderful SQL for that project. I am so mad at myself that I couldn't take it with me when the project ended.