2

I have been working on a small project for about two weeks.

We want to consolidate a few tables from several remote server databases in order to run queries and reports on separate from the live databases....kind of like a DIY mini-data warehouse.

We have three SQL Server 2008 remote servers and I am compiling the data onto one local server running three separate SQL Server Express 2017 instances.

The SQL Server Express databases are all set up, and I have successfully tested copying the tables required using several different methods including:

  • SSMS Import/Export Wizard

  • as well as scripting using Linked Servers from the new database:

    INSERT INTO dbo.table
  SELECT *
  FROM remoteserver.remotedatabase.dbo.remotetable

Both of these successfully copied tables and data BUT not keys, indexes, and other constraints.

I am only recently working in databases, so I am at a loss myself, and Google yielded the above suggestions for copying, but I cannot find anything that includes keys, indexes and other constraints when transferring tables BETWEEN servers.

MDCCL
  • 8,530
  • 3
  • 32
  • 63
DaleP
  • 21
  • 1
  • 2

4 Answers4

3

Since you want to selectively script out certain tables, you can use dbatools to programatically script out tables, triggers, etc using Export-DbaScript.

Alternatively, you can also try Export-DbaDacPackage with -table parameter.

Since you are using linked server, my answer on Which one is more efficient: select from linked server or insert into linked server? will help as well.

Kin Shah
  • 62,545
  • 6
  • 124
  • 245
0

I had a similar need at one of my sites: I needed to create a reporting database off of production every night during a maintenance period. I did not use linked servers but the approach should work the same, assuming you have privs on the remote sever to add indexes and primary keys. Right after the data loading step, run your create index and primary key commands if the indexes don't exist.

-- do the data loading
INSERT INTO dbo.table
  SELECT *
  FROM remoteserver.remotedatabase.dbo.remotetable;

-- create the indexes if they do not exist

if NOT exists ( select * FROM [remoteserver].[remotedatabase].[dbo].[sysindexes] where name = 'pk_remotetable' )

ALTER TABLE [remoteserver].[remotedatabase].[dbo].[remotetable] ADD  CONSTRAINT [pk_remotetable] PRIMARY KEY CLUSTERED ( [remotetable_ID] ASC );

go

if NOT exists ( select * FROM [remoteserver].[remotedatabase].[dbo].[sysindexes] where name = 'nc_index1' )

    CREATE NONCLUSTERED INDEX [nc_index1] ON [remoteserver].[remotedatabase].[dbo].[remotetable] (
    [col1] ASC) INCLUDE ( [col2], [col3])

go

You might want to experiment with data load times and whether its faster to drop the indexes, do the data load, then recreate them -or- create the indexes before the data load. I believe you'll find the data load is faster without the index present but it still takes time to create the index after the fact.

For example you could try this

if exists ( select * FROM [remoteserver].[remotedatabase].[dbo].[sysindexes] where name = 'pk_remotetable' )

ALTER TABLE [remoteserver].[remotedatabase].[dbo].[remotetable] DROP CONSTRAINT [pk_remotetable]

go

if exists ( select * FROM [remoteserver].[remotedatabase].[dbo].[sysindexes] where name = 'nc_index1' )

DROP INDEX IX_index1 ON [remoteserver].[remotedatabase].[dbo].[remotetable]

go

then do your data load, then create the pk and nc index as above.

Note: my answer is somewhat theoretical because in my case my data loading was done by restoring a backup of prod (not via a linked server) but in my SQL Agent job I do a lot of dropping of indexes that are needed for the OLTP database in production but are bunk in reporting and then creating of indexes that are reporting related and help the reports run faster. In other words I do automatically recreate the same indexes in the source database but I do create new ones in the destination that are specifically designed for reporting needs not OLTP needs.

Jeff Mergler
  • 125
  • 8
0

Personally, I use SSIS to generate creation scripts (via right-click your database, select Tasks, Generate Scripts) for the items I need to transfer (especially if I have to drop and recreate, rather than create normally, since this will handle the sequence of tables related by foreign keys, disable/drop FKs on tables not being manipulated, etc, in the proper order to not generate errors), execute the create scripts on the target DB, then use an SSIS datapump operation to transfer the data, which is usually faster than other methods of mass transferring data from one system to another.

Laughing Vergil
  • 2,068
  • 11
  • 21
0

So, I had a day off in the meantime as well as was working on several different tests regarding this idea, and thus had not responded in a bit.

To recap. The goal is to synchronize select tables, including data, key, indexes, etc... from one remote database server to another to form a sort of DIY data warehouse for reporting purposes. Finally, this is to be done basically for free minus my time involved.

I settled on an initial Export of the database using SSMS for the tables required, then followed up with a script generation of the database to capture table keys, indexes, and constraints, as well as other database information not captured with the simple database data export.

This allowed a stripped-down dataset that still had keys, indexes, and constraints for the required tables while saving space on the destination server.

Unfortunately free meant using SQL Express and one of the three instances breached the 10GB database cap nullifying the project test.

Their options now are to purchase SQL server, utilize a MySQL server data migration, or simply query the live databases (not my first choice, that is for sure.)

Thank you all for the assistance folks. If nothing else, it was very good taste of SQL work that I had not previously had, as well as investigating each and everyone's solution suggestions. Thank you, all.

DaleP
  • 21
  • 1
  • 2