Incrementally moving data across SQL Server instances without an ETL tool
I am working on a project that involves regularly copying data from identically structured database A (on a SQL Server instance) to database B (on a different instance). I do not have the option of using SSIS or another ETL tool, so I have to use already established linked servers and write a stored procedure run as an hourly job for the incremental loads.
To copy the delta records from database A to B, I have the following stored procedure to be run as a job:
CREATE PROCEDURE Incremental_Load
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO New_Table (New_Date_Column, Copied_Attribute)
SELECT Datetime_Var, Attribute1
FROM Linked_Server.Database_A.dbo.Source_Table
WHERE Datetime_Var > (SELECT MAX(New_Date_Column) FROM New_Table);
END
It seems to work; only the new records, not present in the new table are inserted, based on the last record by datetime. My question has to do with performance and potential issues. If the source table gets new records every 30 minutes, and this stored procedure is run hourly, should I expect any issues, especially with regard to the subquery? Also, is it possible I will not detect all deltas? Are there any other glaring issues with this approach?
In case it matters, I do not have access to SQL Server Profiler. I also cannot create jobs, because of my privileges/roles; Someone else must do that for me. So, I would like to collect some information in advance of testing.
Thank you.
sql-server edited Nov 19 '18 at 19:41
marc_s
574k12811091256
asked Nov 19 '18 at 18:17
pv342pv342
62
|
show 1 more comment
I am working on a project that involves regularly copying data from identically structured database A (on a SQL Server instance) to database B (on a different instance). I do not have the option of using SSIS or another ETL tool, so I have to use already established linked servers and write a stored procedure run as an hourly job for the incremental loads.
To copy the delta records from database A to B, I have the following stored procedure to be run as a job:
CREATE PROCEDURE Incremental_Load
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO New_Table (New_Date_Column, Copied_Attribute)
SELECT Datetime_Var, Attribute1
FROM Linked_Server.Database_A.dbo.Source_Table
WHERE Datetime_Var > (SELECT MAX(New_Date_Column) FROM New_Table);
END
It seems to work; only the new records, not present in the new table are inserted, based on the last record by datetime. My question has to do with performance and potential issues. If the source table gets new records every 30 minutes, and this stored procedure is run hourly, should I expect any issues, especially with regard to the subquery? Also, is it possible I will not detect all deltas? Are there any other glaring issues with this approach?
In case it matters, I do not have access to SQL Server Profiler. I also cannot create jobs, because of my privileges/roles; Someone else must do that for me. So, I would like to collect some information in advance of testing.
Thank you.
sql-server edited Nov 19 '18 at 19:41
marc_s
574k12811091256
asked Nov 19 '18 at 18:17
pv342pv342
62
I don't see any issues. As it grabs the max date value from the table which needs insertion and uses that to only query records from your linked server table, I think this will work fine.
– Ryan Wilson
Nov 19 '18 at 18:25
you can use MERGE statements. should not have any performance issues and can update records from source to destination. If you do not wish to; then you also need to take into account how you want to handle records that are modified? i.e insert them as new records, update or delete depending on source. You have clause for all 3 in MERGE statements.
– junketsu
Nov 19 '18 at 18:57
Out of curiosity, why is SSIS not an option here? Saying that you can't use it for a purpose which it is particularly good at sounds like "I'm trying to build a house but do not have the option of using a hammer".
– Ben Thul
Nov 19 '18 at 20:10
@BenThul Our IT department does not use SSIS, and our IT manager told me to use the existing linked servers. I would love to learn and use SSIS instead, as I also have to transform the data as it's copied over (not reflected in my post).
– pv342
Nov 19 '18 at 20:44
Have you considered transactional replication? In some situations linked servers can be a liability, i.e. when they are not managed, contain hard coded logins, insufficiently secured, used in badly performing queries.
– Nick.McDermaid
Nov 19 '18 at 23:06
|
show 1 more comment
I am working on a project that involves regularly copying data from identically structured database A (on a SQL Server instance) to database B (on a different instance). I do not have the option of using SSIS or another ETL tool, so I have to use already established linked servers and write a stored procedure run as an hourly job for the incremental loads.
To copy the delta records from database A to B, I have the following stored procedure to be run as a job:
CREATE PROCEDURE Incremental_Load
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO New_Table (New_Date_Column, Copied_Attribute)
SELECT Datetime_Var, Attribute1
FROM Linked_Server.Database_A.dbo.Source_Table
WHERE Datetime_Var > (SELECT MAX(New_Date_Column) FROM New_Table);
END
It seems to work; only the new records, not present in the new table are inserted, based on the last record by datetime. My question has to do with performance and potential issues. If the source table gets new records every 30 minutes, and this stored procedure is run hourly, should I expect any issues, especially with regard to the subquery? Also, is it possible I will not detect all deltas? Are there any other glaring issues with this approach?
In case it matters, I do not have access to SQL Server Profiler. I also cannot create jobs, because of my privileges/roles; Someone else must do that for me. So, I would like to collect some information in advance of testing.
Thank you.
sql-server edited Nov 19 '18 at 19:41
marc_s
574k12811091256
asked Nov 19 '18 at 18:17
pv342pv342
62
I am working on a project that involves regularly copying data from identically structured database A (on a SQL Server instance) to database B (on a different instance). I do not have the option of using SSIS or another ETL tool, so I have to use already established linked servers and write a stored procedure run as an hourly job for the incremental loads.
To copy the delta records from database A to B, I have the following stored procedure to be run as a job:
CREATE PROCEDURE Incremental_Load
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO New_Table (New_Date_Column, Copied_Attribute)
SELECT Datetime_Var, Attribute1
FROM Linked_Server.Database_A.dbo.Source_Table
WHERE Datetime_Var > (SELECT MAX(New_Date_Column) FROM New_Table);
END
It seems to work; only the new records, not present in the new table are inserted, based on the last record by datetime. My question has to do with performance and potential issues. If the source table gets new records every 30 minutes, and this stored procedure is run hourly, should I expect any issues, especially with regard to the subquery? Also, is it possible I will not detect all deltas? Are there any other glaring issues with this approach?
In case it matters, I do not have access to SQL Server Profiler. I also cannot create jobs, because of my privileges/roles; Someone else must do that for me. So, I would like to collect some information in advance of testing.
Thank you.
sql-server sql-server edited Nov 19 '18 at 19:41
marc_s
574k12811091256
asked Nov 19 '18 at 18:17
pv342pv342
62
edited Nov 19 '18 at 19:41
marc_s
574k12811091256
asked Nov 19 '18 at 18:17
pv342pv342
62
edited Nov 19 '18 at 19:41
marc_s
574k12811091256
edited Nov 19 '18 at 19:41
marc_s
574k12811091256
edited Nov 19 '18 at 19:41
marc_s
574k12811091256
574k12811091256
asked Nov 19 '18 at 18:17
pv342pv342
62
asked Nov 19 '18 at 18:17
pv342pv342
62
asked Nov 19 '18 at 18:17
pv342pv342
62
62
I don't see any issues. As it grabs the max date value from the table which needs insertion and uses that to only query records from your linked server table, I think this will work fine.
– Ryan Wilson
Nov 19 '18 at 18:25
you can use MERGE statements. should not have any performance issues and can update records from source to destination. If you do not wish to; then you also need to take into account how you want to handle records that are modified? i.e insert them as new records, update or delete depending on source. You have clause for all 3 in MERGE statements.
– junketsu
Nov 19 '18 at 18:57
Out of curiosity, why is SSIS not an option here? Saying that you can't use it for a purpose which it is particularly good at sounds like "I'm trying to build a house but do not have the option of using a hammer".
– Ben Thul
Nov 19 '18 at 20:10
@BenThul Our IT department does not use SSIS, and our IT manager told me to use the existing linked servers. I would love to learn and use SSIS instead, as I also have to transform the data as it's copied over (not reflected in my post).
– pv342
Nov 19 '18 at 20:44
Have you considered transactional replication? In some situations linked servers can be a liability, i.e. when they are not managed, contain hard coded logins, insufficiently secured, used in badly performing queries.
– Nick.McDermaid
Nov 19 '18 at 23:06
|
show 1 more comment
I don't see any issues. As it grabs the max date value from the table which needs insertion and uses that to only query records from your linked server table, I think this will work fine.
– Ryan Wilson
Nov 19 '18 at 18:25
you can use MERGE statements. should not have any performance issues and can update records from source to destination. If you do not wish to; then you also need to take into account how you want to handle records that are modified? i.e insert them as new records, update or delete depending on source. You have clause for all 3 in MERGE statements.
– junketsu
Nov 19 '18 at 18:57
Out of curiosity, why is SSIS not an option here? Saying that you can't use it for a purpose which it is particularly good at sounds like "I'm trying to build a house but do not have the option of using a hammer".
– Ben Thul
Nov 19 '18 at 20:10
@BenThul Our IT department does not use SSIS, and our IT manager told me to use the existing linked servers. I would love to learn and use SSIS instead, as I also have to transform the data as it's copied over (not reflected in my post).
– pv342
Nov 19 '18 at 20:44
Have you considered transactional replication? In some situations linked servers can be a liability, i.e. when they are not managed, contain hard coded logins, insufficiently secured, used in badly performing queries.
– Nick.McDermaid
Nov 19 '18 at 23:06
I don't see any issues. As it grabs the max date value from the table which needs insertion and uses that to only query records from your linked server table, I think this will work fine.
– Ryan Wilson
Nov 19 '18 at 18:25
I don't see any issues. As it grabs the max date value from the table which needs insertion and uses that to only query records from your linked server table, I think this will work fine.
– Ryan Wilson
Nov 19 '18 at 18:25
you can use MERGE statements. should not have any performance issues and can update records from source to destination. If you do not wish to; then you also need to take into account how you want to handle records that are modified? i.e insert them as new records, update or delete depending on source. You have clause for all 3 in MERGE statements.
– junketsu
Nov 19 '18 at 18:57
you can use MERGE statements. should not have any performance issues and can update records from source to destination. If you do not wish to; then you also need to take into account how you want to handle records that are modified? i.e insert them as new records, update or delete depending on source. You have clause for all 3 in MERGE statements.
– junketsu
Nov 19 '18 at 18:57
Out of curiosity, why is SSIS not an option here? Saying that you can't use it for a purpose which it is particularly good at sounds like "I'm trying to build a house but do not have the option of using a hammer".
– Ben Thul
Nov 19 '18 at 20:10
Out of curiosity, why is SSIS not an option here? Saying that you can't use it for a purpose which it is particularly good at sounds like "I'm trying to build a house but do not have the option of using a hammer".
– Ben Thul
Nov 19 '18 at 20:10
@BenThul Our IT department does not use SSIS, and our IT manager told me to use the existing linked servers. I would love to learn and use SSIS instead, as I also have to transform the data as it's copied over (not reflected in my post).
– pv342
Nov 19 '18 at 20:44
@BenThul Our IT department does not use SSIS, and our IT manager told me to use the existing linked servers. I would love to learn and use SSIS instead, as I also have to transform the data as it's copied over (not reflected in my post).
– pv342
Nov 19 '18 at 20:44
Have you considered transactional replication? In some situations linked servers can be a liability, i.e. when they are not managed, contain hard coded logins, insufficiently secured, used in badly performing queries.
– Nick.McDermaid
Nov 19 '18 at 23:06
Have you considered transactional replication? In some situations linked servers can be a liability, i.e. when they are not managed, contain hard coded logins, insufficiently secured, used in badly performing queries.
– Nick.McDermaid
Nov 19 '18 at 23:06
|
show 1 more comment
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53380494%2fincrementally-moving-data-across-sql-server-instances-without-an-etl-tool%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53380494%2fincrementally-moving-data-across-sql-server-instances-without-an-etl-tool%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I don't see any issues. As it grabs the max date value from the table which needs insertion and uses that to only query records from your linked server table, I think this will work fine.
– Ryan Wilson
Nov 19 '18 at 18:25
you can use MERGE statements. should not have any performance issues and can update records from source to destination. If you do not wish to; then you also need to take into account how you want to handle records that are modified? i.e insert them as new records, update or delete depending on source. You have clause for all 3 in MERGE statements.
– junketsu
Nov 19 '18 at 18:57
Out of curiosity, why is SSIS not an option here? Saying that you can't use it for a purpose which it is particularly good at sounds like "I'm trying to build a house but do not have the option of using a hammer".
– Ben Thul
Nov 19 '18 at 20:10
@BenThul Our IT department does not use SSIS, and our IT manager told me to use the existing linked servers. I would love to learn and use SSIS instead, as I also have to transform the data as it's copied over (not reflected in my post).
– pv342
Nov 19 '18 at 20:44
Have you considered transactional replication? In some situations linked servers can be a liability, i.e. when they are not managed, contain hard coded logins, insufficiently secured, used in badly performing queries.
– Nick.McDermaid
Nov 19 '18 at 23:06