0

I'm attempting to make my existing SQL Server 2008 database compatible with the Windows Azure platform by using SSDT, however I am getting a whole bunch of errors when I build the project due to TVFs and views looking for an external database that sits in the same instance in SSMS.

I've added the database that its looking for into Azure, which wasn't a problem.

I've found that if I load the offending piece of code I can add the Azure server address to the FROM statement which resolves the error (shown below), however I have a huge number that rely on the external db and hoped there may be a quicker way?

FROM [myAzureserver.database.windows.net.ExternalDBName.dbo.TableName] as ALIAS

I understand that this issue would not exist if I merged the databases, however this isn't possible at present.

Thanks a lot for your help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ImaginationBarrier
  • 13
  • 3

1 Answers1

1

Why are you trying to make your local SQL Server Azure compliant? Are you planning to move it at some point in the cloud? If so, you won't be able to use linked servers. Your FROM clause will work as long as the database remains on an on-premise SQL Server instance.

Assuming that's what you want to do, you are asking if there is quicker way to change your references to point to the cloud database, right? I am not sure if this will work for you but I had a similar issue on another project and ended up using synonyms. Check our synonyms here: http://msdn.microsoft.com/en-us/library/ms177544.aspx. Although you can't create a synonym for a server, you can create synonyms for tables/views/procs.

Again, this may not work for you, but let's try this...

Assuming you have your primary database called DB1, the secondary database called DB2, and the cloud database of DB2 called AzureDB2, you could create synonyms in DB2 to point to the cloud database without changing any SQL statement from DB1.

So assume you have this statement today in DB1:

SELECT * FROM DB2.MyTable

You could create a synonym in DB2 called MyTable:

CREATE SYNONYM MyTable FOR [myAzureserver.database.windows.net.ExternalDBName.dbo.TableName]

DB2 becomes a bridge basically. You don't need to change any statement in DB1; just create synonyms in DB2 that point to the cloud database.

Hopefully this works for you. :)

Herve Roggero
  • 5,149
  • 1
  • 17
  • 11