1

I have a database that is used for reporting services. This database was created by a contractor who is no longer around. Buried all throughout the database are references to a single table on a linked server. The content on that linked server is being moved, and the server being retired.

Rather than hunting around for all of the linked server references and updating them to point at the new linked server, or changing them to use a synonym name, would it be possible (or acceptable) to create the new linked server, then add a synonym that uses the same name as the current fully qualified linked server reference, but points to the new linked server? This would allow me to leave all database code intact and minimize disruption to business users.

Here's what I'm thinking. Current linked server is SRAPP. All queries using the linked server point to OPub.dbo.ADE. 

 exec sp_addlinkedserver @server=SQLProd1

Then

CREATE SYNONYM [SRAPP.OPub.dbo.ADE]
FOR SQLProd1.OPub.dbo.ADE
GO

Finally,

exec sp_dropserver 'SRAPP'

Will SQL Server get confused by a synonym name that looks identical to a linked server reference?

Improve this question

4 Answers 4

Reset to default
4

Did you try your idea? What were your results? Yes, you can create a synonym as:

CREATE SYNONYM [SRAPP.OPub.dbo.ADE]
FOR SQLProd1.OPub.dbo.ADE
GO

But you cannot use a synonym for a linked server. Also, as of SQL Server 2016 the following limitation exists:

Four-part names for function base objects are not supported.

Recommendation: Drop and Recreate the Linked Server 'SRAPP'. You can help yourself by scripting out the existing definition. Drop the existing linked server and recreate it using the new server name.

As of SQL Server 2016 synonyms can only be defined for:

  • Assembly (CLR) Stored Procedure
  • Assembly (CLR) Table-valued Function
  • Assembly (CLR) Scalar Function
  • Assembly Aggregate (CLR) Aggregate
  • Functions Replication-filter-procedure
  • Extended Stored Procedure SQL
  • Scalar Function
  • SQL Table-valued Function
  • SQL Inline-table-valued Function
  • SQL Stored Procedure
  • View
  • Table (User-defined)

Yes, there are many things that we can conceive about synonyms, but they do not yet exist.

Improve this answer
0
1

A synonym is a database object owned by a schema. To select using your schema would look like:

select * from [MyDataBase].[dbo].[SRAPP.OPub.dbo.ADE]
or
select * from [SRAPP.OPub.dbo.ADE]

No chance of confusion there.

Improve this answer
1

If the old server is being retired (take offline) have your IT folks set the DNS entry of the retired server's name to point to the new server that will be taking over. It's not the cleanest solution but you don't have to update anything in the database and the linked server connection stays in place.

Improve this answer
0

The first problem here is that your synonym DOESN"T look identical to a linked server reference. By using square brackets [] you are telling it to treat the periods just like any other character and not separate out the SRAPP to treat it like a server, database, schema etc...

That said, if you remove the square brackets you will at the very least get this issue

'CREATE SYNONYM' does not allow specifying the database name as a prefix to the object name.

I don't know of a way to use a synonym for a linked server which would be the ideal answer here. As far as I can tell, I think you will have to update those references. For the least amount of work, you could set up the synonym as you have here which should work, and then find the references and just add the square brackets to make it match the synonym.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.