0

I was wondering if this is possible: I want to create 2 tables in an Azure SQL database which I want to use as staging tables for some data selected from a SQL Server 2005 database on an on-premise server.

Is there any good way of doing this?

Update:

Followed the Linked-Server Guide and got this error: I need to add a linked server to a MS Azure SQL Server

enter image description here

The weird thing is i just managed to login to the Azure DB on with Those exact credentials. Am i missing some security configuration somewhere?

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
H4p7ic
  • 1,669
  • 2
  • 32
  • 61
  • you can create a linked server:https://stackoverflow.com/questions/28590832/i-need-to-add-a-linked-server-to-a-ms-azure-sql-server – TheGameiswar Oct 17 '17 at 07:51
  • Forget to mention, you can select only from onpremises sql to azure sql,not the other way around – TheGameiswar Oct 17 '17 at 08:14
  • Well i only need to copy some data from the onpremises sql server to the azure server that will act as my staging server. Thank you for the answer, i will look into that one. – H4p7ic Oct 17 '17 at 08:20
  • if this is a one time activity, there are many options..like ssis – TheGameiswar Oct 17 '17 at 08:31
  • Well it needs to happen once a day. Though i am worried i wont get the permission to do this the owners of the database are very careful of modifying the on premises database. i initially wanted to do the staging tables in the same on-prem server but they insisted not to. What other was are there? Just to keep my options open. – H4p7ic Oct 17 '17 at 08:36
  • `Well it needs to happen once a day` you can use replication from onpremises to azure and you dont need to modify anything – TheGameiswar Oct 17 '17 at 08:39
  • @TheGameiswar Check the update on the post please :) – H4p7ic Oct 17 '17 at 09:21
  • i think you need to create an user in onpremises(sqlauth) and give that user basic permissions in azure,then this will work..I think you are doing the other way – TheGameiswar Oct 17 '17 at 09:28

2 Answers2

1

Support for linked servers and distributed queries against a SQL Azure Database is a feature available since September 2012 as explained here. On my tests it does not work correctly on SQL 2012 and does not work on SQL Server 2008 R2 and earlier versions. You may need to upgrade to SQL Server 2014 to create a fully functional linked server.

Hope this helps.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • Yeah upgrading the sql-server is a given. The thing is the owner totally refuses, trying my best to work around the problem. – H4p7ic Oct 17 '17 at 12:00
1

You don't necessarily need to create a linked server to move data from an on-premise SQL table to Azure SQL. Tools like SSIS come to mind, where a linked server isn't necessary. Or a tool like SSDT where you could use the Data Compare feature to move data. There is also Azure Data Factory.

Randy Minder
  • 47,200
  • 49
  • 204
  • 358
  • I see, well its not about mooving data, but more of inserting new data and update the staging table in azure db with data every night from the onpremisis SQL DB. its about 60k rows to one of the tables. Also i'm using a stored procedure to do this. – H4p7ic Oct 17 '17 at 13:52