Issue with migration of Azure sql and sql server

1
votes

Fist of all sorry for my bad English. I am new for azure.We are planning to move some selected tables from our SQL database to azure SQL database because of it getting to much load.But existing stored procedure have joined with these tables in SQL server. So what is the best solution to get a result from both databases.

For example booking table right now in Azure database. But customer details, office details, courier details are in our existing SQL database.

Updated

Initially, we have only one database in sql server which contains all tables booking, customer details, office details, courier details etc. Due to heavy load, the client has decided to move some of the tables from sql server to Azure. So we have moved booking related tables into Azure. The issue is the database contains many stored procedures joined between all these tables. If I move some tables to Azure this won't work. I know there are methods to link multiple sql server to write stored procedures by adding those databases as 'Linked Servers' and access through [Server Name].[Database Name].[Table Name]. I think the same is possible between two Azure Sql databases.

My question is this cross-database querying is possible between two databases one is situated in SQL server and other is in Azure.

Thank you.

1
sql-serverazure-sql-database
what do you mean by this ? .But our lot existing store procedure have joined with these tables - TheGameiswar
It meant, existing stored procedure have joined with these tables in SQL server. - satheesh kumar
did you meant you have cross database queries and you are moving one of those databases to azure ? - TheGameiswar
I have updated my question.Please have a look. - satheesh kumar

1 Answers

0
votes

Azure supports cross database queries if both databases are in Azure ..In your case,it seems some of will be in OnPremises..

So the only option,which i can think of is to use is linked servers to azure..these queries can perform worse,depending on the data you want from them..

In General,you have to follow below steps to create Linked server to AZure..

1.Run odbcad32.exe to setup a system DSN using SQL Server Native Client.
2.Now create a linked server..

EXEC master.dbo.sp_addlinkedserver
@server = N’Can be any name′,
@srvproduct=N’Any’, 
@provider=N’MSDASQL’, 
@datasrc=N’name of DSN you created′

Now you can query azure from your local server like below

select * from [@datasrc name(dsn name)],db.schema.table

this blog explains step by step and goes into some details on what are the pitfalls

https://blogs.msdn.microsoft.com/sqlcat/2011/03/07/linked-servers-to-sql-azure/