August 12, 2015 at 9:31 am
Hello,
I need help / advise on how to copy a content of a table from one remote server to another, please.
Although sounds very simple, server A does not see server B (B doesn't see A) - I cannot even ping to one from another.
I do have SQL Studio installed on server C, which IT team configured to allow access to both A and B.
So what I did so far is to periodically:
1. connect from the studio on server C to server A
2. run the following script on server A: SELECT * FROM A.myTable FOR XML PATH('ROOT')
3. copy the result
4. connect from the studio on server C to server B
5. to write something like
DECLARE @xmlData XML;
SET @xmlData = pasting here my result from item 3 above
6. INSERT INTO
SELECT
ref.value .....
FROM @xmlData.nodes('/myElemnet/ROOT')
xmlData( ref );
so it works. now there is a requirement to schedule this update to run periodically and I need one's advise how to implement it, please?
Much obliged
August 12, 2015 at 9:50 am
Well, if server C can see both A and B, then you can do this a number of ways.
1. Linked Servers to pull data from A to C, and then Push from C to B ( I would stage it in C and wait for the pull to complete before trying to push the data to B so 2 links are not involved in the query at the same time)
2. SSIS package - create a connection object to both A and B and then run the package on C to push the data from A to B through C.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2015 at 9:59 am
Thank you, Jason
Sadly I come from an Oracle background. Can you point me to some documentation I can read how to implement your suggestions, please?
Many thanks
August 12, 2015 at 10:02 am
SQLRNNR (8/12/2015)
Well, if server C can see both A and B, then you can do this a number of ways.1. Linked Servers to pull data from A to C, and then Push from C to B ( I would stage it in C and wait for the pull to complete before trying to push the data to B so 2 links are not involved in the query at the same time)
2. SSIS package - create a connection object to both A and B and then run the package on C to push the data from A to B through C.
While good, both solutions need SQL DB Engine & Agent installed on server C, but it's not clear from the description if it is or not. 2 will also need SSIS installed.
If no SQL on C, Powershell & Task Scheduler could be useful, depending on the amount of data. I'm assuming from the method you're currently using it's not a lot.
August 12, 2015 at 10:10 am
While good, both solutions need SQL DB Engine & Agent installed on server C, but it's not clear from the description if it is or not. 2 will also need SSIS installed.
If no SQL on C, Powershell & Task Scheduler could be useful, depending on the amount of data. I'm assuming from the method you're currently using it's not a lot.
Hello,
Apologies I didn't provide the full info, server C has SQL Studio installed. I can also see there SQL Server installation (on C), but it is disabled in the services, so no SQL on server C can be utilised.
Regards
August 12, 2015 at 10:19 am
First, understand the SQL Management Studio is a client application. It doesn't do anything on a server, even if it's running on the host that the SQL Server instance is running on. What you're doing is returning information from one server to a client and sending it to another server from a client.
If you need to do this regularly, here's what I'd do.
- Setup a linked server from Server B to Server A. In other words, connect to ServerB and create a linked server that points to server A. Use an account that is set up with rights on Server B for the security.
- Create a stored procedure that can query the data from Server A through the linked server and then insert/update data on server B. This could be with MERGE if it's a small set of data. If it's a lot, potentially, or performance concerns exist, you could insert the data into a temp table and then do an update from the temp table to the main table.
- If necessary, create a job on Server B that runs the procedure on B periodically and moves data to the table.
SSIS can do this easily, but it might be overkill and too much complexity for your requirements.
August 12, 2015 at 10:26 am
With no SQL engine on C, you have your hands tied a bit more.
Powershell is an option as mentioned by gazareth.
What is the business purpose for not allowing communications directly from A to B?
If there is not a strong business purpose to disallow the communications, why not create a linked server direct from A to B or vise versa?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2015 at 10:36 am
My apologies, I didn't see the B can't see A part.
I'll agree with Jason on finding out why B can't see A. Can A see B? You could reverse my solution in that case and "push" data from A.
August 12, 2015 at 10:43 am
Steve Jones - SSC Editor (8/12/2015)
First, understand the SQL Management Studio is a client application. It doesn't do anything on a server, even if it's running on the host that the SQL Server instance is running on. What you're doing is returning information from one server to a client and sending it to another server from a client.
Absolutely - SQL Studio is client only. no attempt to treat it otherwise
If you need to do this regularly, here's what I'd do.
- Setup a linked server from Server B to Server A. In other words, connect to ServerB and create a linked server that points to server A. Use an account that is set up with rights on Server B for the security.
- Create a stored procedure that can query the data from Server A through the linked server and then insert/update data on server B. This could be with MERGE if it's a small set of data. If it's a lot, potentially, or performance concerns exist, you could insert the data into a temp table and then do an update from the temp table to the main table.
- If necessary, create a job on Server B that runs the procedure on B periodically and moves data to the table.
SSIS can do this easily, but it might be overkill and too much complexity for your requirements.
Here lies the issue server A is in Europe, server B is in Australia. different people, different IT, different security policies. All have agreed to allow the access from C to both of the servers.
I thought to use some Windows schedule to pull the data (my XML with couple of thousands of records), but then I have realised I will first need to copy it onto server B before actually loading it.
I am happy to go back to the IT, explaining that an only route is to use linked server option - the process that will take pretty long time within the organisation, unless there is anything else could be done?
Thank you
August 12, 2015 at 11:05 am
Here's an example of a Powershell script that will do this. You should probably still plan on exporting to a file first and then import from that file for the same reasons that Jason originally listed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 12, 2015 at 11:31 am
Grant Fritchey (8/12/2015)
Here's an example of a Powershell script that will do this.
Thanks for finding that. I didn't have one handy.:crazy:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2015 at 11:38 am
If it's difficult to get connectivity, then Grant's solution can likely be scheduled in Windows. No need for SSMS.
However I'd also be sure there's some process to check that it's working and isn't failing. Simple transfers like this can easily fail when something changes and no one will notice.
August 12, 2015 at 12:19 pm
Thank you all for the suggestions and especially for the script!
Much appreciated!
August 12, 2015 at 12:22 pm
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply