Recovery :: Using Synonyms On AG

I currently have a 2 node Availability Group that houses some big warehouse type of DB’s. It’s not uncommon to see DB1 populate DB2:

Use db1
Go
Insert into db2.dbo.table_1 values ‘ssfsfsfsf’

This all works as expected. However, we want to split these DB’s away from each other to reduce CPU impact on any one instance. Of course though we don’t want to have to go and change all the connection strings in our SSIS Packages.  By default this would now require going to a 4 part naming convention like so: Insert into newServerName.db2.dbo.table_1 values ‘ssfsfsfsf’Again though, we are trying to avoid this. That said I was thinking to use synonyms. This works as expected, until a failover occurs, and the synonym is redirected to the local Read Only version of DB2.So in other words, SQL Server is behaving exactly as it should, but I need a way to work around it.

Example below:

USE [master]
GO
/****** Object:  LinkedServer [DB2]    Script Date: 10/28/2015 11:43:46 AM ******/
--note this is pointed to the AG Listener name, for failover purposes.
EXEC
master.dbo.sp_addlinkedserver
@server = N'DB2', @srvproduct=N'sqlserver', @provider=N'SQLNCLI',
@datasrc=N'myAGListenerName', @catalog=N'DB2'

[code]....

Again, SQL Server is behaving exactly as it should. However, this synonym is now fairly worthless, as it can't handle failing from one node to the other in the AG