0

I have a MS SQL Server database that contains data for many customers and I am working on a feature to export a single customers data.

I have basically created a script that generates inserts for all the customer records to run on another database. This is working great in my test environment but I am concerned about what impact this will have on production.

We could of used a product like RedGate but we decided to integrate this logic within our app itself.

Export Action

  • No transaction required
  • Need to be run on live database but will be off hours.
  • Right now I have a stored procedure that returns the insert statements.

Import Action

  • Transaction required.
  • Need to be run on live database but will be off hours.
  • I run the returned insert statements on the new database.

Questions (mostly regarding the production environment)

  1. The export script touches virtually every table in the system (with no lock), should I be concerned about this action? I have had suggestions to break up this action into pieces, to not create one giant execution. Would there be an advantage to this?

  2. The import script will potentially be running millions of inserts. With similar concerns to the export, should I be adding "GO"s every X inserts? Is that enough? Should this be broken into multiple executions? Is this a scenario where I need to use SQL Bulk Copy?

Thank you very much for your help.

2
  • Hey got some questions. 1. MSSQL is used on export and import? 2. I understand you have acces to them - but are they are reachable from one network? 3. At import you move insert statment script to other instance and run it?
    – VoonArt
    Commented Apr 22, 2015 at 18:54
  • 1. Yes it is MSSQL. 2. Sadly, they are not reachable via the network. 3. Yep. I create the script of insert statements and run that on the other database in the other data center.
    – Matt
    Commented Apr 22, 2015 at 20:58

1 Answer 1

0

I hope i have understood everything good.

You have MSSQL server let's name it S1 from whitch you have to export data to other MSSQL server, let's name it S2.

The best solution depends from possibilites.

  1. With Linked Server object. If you have access over network to both of them, I would suggest using linked server to communicate over network and retrive data from S1 to S2. https://www.google.pl/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=mssql%20linked%20server

Here is my script, may help.

DECLARE @OPENQUERY NVARCHAR(4000) ,
    @TSQL NVARCHAR(4000) ,
    @LinkedServer NVARCHAR(4000);

SET @LinkedServer = 'lnk_test';
SET @OPENQUERY = 'SELECT * FROM OPENQUERY(' + @LinkedServer + ',''';

----------------------------------------------------------------------------
-------------------------------- Retrieving Data ---------------------------
----------------------------------------------------------------------------
--##StatusMig - Table containg progres and times

IF OBJECT_ID('tempdb..##StatusMig') IS NOT NULL
    BEGIN
        DROP TABLE ##StatusMig;
    END;
CREATE TABLE ##StatusMig
    (
      LP INT ,
      NAME VARCHAR(MAX) ,
      DATA DATETIME
    );

INSERT  INTO ##StatusMig
        ( LP ,
          NAME ,
          DATA
        )
        SELECT  ISNULL(MAX(LP), 0) + 1 ,
                'Start' ,
                GETDATE()
        FROM    ##StatusMig;

-----------------------------------------------------------------
-------- TABLE_A
-----------------------------------------------------------------

IF OBJECT_ID('tempdb..##TABLE_A') IS NOT NULL
    BEGIN
        DROP TABLE ##TABLE_A;
    END;

CREATE TABLE ##TABLE_A ( col_1 INT, col_2 INT );


INSERT  INTO ##StatusMig
        ( LP ,
          NAME ,
          DATA
        )
        SELECT  ISNULL(MAX(LP), 0) + 1 ,
                'Start Start TABLE_A' ,
                GETDATE()
        FROM    ##StatusMig;

PRINT 'Start TABLE_A';

SET @TSQL = '
        select col_1, col2_2 from TABLE_A
          '')';

PRINT @OPENQUERY + @TSQL;

INSERT  INTO ##TABLE_A
        EXEC ( @OPENQUERY + @TSQL
            );

INSERT  INTO ##StatusMig
        ( LP ,
          NAME ,
          DATA
        )
        SELECT  ISNULL(MAX(LP), 0) + 1 ,
                'End TABLE_A' ,
                GETDATE()
        FROM    ##StatusMig;
  1. No network access, possibility to use files. Generating insert statments and execution them is last thing you want to do. Will work slow and it will consume alot of resources.

Instead of creating insert statments, you can create new database for example data_migration with customer data.

S1 - MSSQL server from you export data S2 - MSSQL server to you export data

  1. On S1 create job whitch backup data_migration table every day.
  2. Send data_migraction backup file over ftp etc.
  3. On S2 create job whitch checks ftp and if backup from today is avalaible restores it.
  4. Run T-SQL with inserts.

Remember, work with sets not single values. Sorry for spelling and grammar im after 36h work marathon.

4
  • Sadly, in this case we don't. Different organizations own each data center. Thank you very much for the information though.
    – Matt
    Commented Apr 22, 2015 at 20:57
  • So instead of insert statment script move backup and work with sets.
    – VoonArt
    Commented Apr 23, 2015 at 5:37
  • The script I have is working well in my tests. My concern here is performance and managing DB resources when I run in production. I want to make sure Im running it in the most efficient and effective way.
    – Matt
    Commented Apr 23, 2015 at 16:52
  • Matt DB's are about to working on sets... Creating, running INSERT statments its not efficient way -> Postgress dumps like that data. I would make 3 scripts 1. Export to new database 2. Backup database 3. Move it to new datacenter 3. Restore 4. Run scrips. Good luck
    – VoonArt
    Commented Apr 23, 2015 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.