January 26, 2017 at 3:14 pm
I've been searching around for a while, and I could not find a way that a database name can be aliased. I was hoping that someone here might be able to point me toward a solution.
Here is the scenario: We've been using transaction replication for our large OLTP database. We're hosting the replicated copy on a different server and using a different database name for it. My_OLTP replicates to My_RPT
So naturally, the RPT one has a lot of stored procedures coded for accessing data there as My_RPT.dbo.table1 as an example.
We are planning on switching from transactional replication to AlwaysOn-Availability Groups setup and this will mean that the secondary copies have a database name called My_OLTP too and My_RPT will become invalid. Is there a way that I can create an alias/synonym for My_RPT which will cause a stored procedure to run against the name My_OLTP instead?
Synonyms only work for tables/stored procs/views/functions but not (it appears) for a database.
Any ideas anyone? Thanks.
January 26, 2017 at 3:45 pm
Larry Kruse - Thursday, January 26, 2017 3:14 PMI've been searching around for a while, and I could not find a way that a database name can be aliased. I was hoping that someone here might be able to point me toward a solution.Here is the scenario: We've been using transaction replication for our large OLTP database. We're hosting the replicated copy on a different server and using a different database name for it. My_OLTP replicates to My_RPT
So naturally, the RPT one has a lot of stored procedures coded for accessing data there as My_RPT.dbo.table1 as an example.
We are planning on switching from transactional replication to AlwaysOn-Availability Groups setup and this will mean that the secondary copies have a database name called My_OLTP too and My_RPT will become invalid. Is there a way that I can create an alias/synonym for My_RPT which will cause a stored procedure to run against the name My_OLTP instead?
Synonyms only work for tables/stored procs/views/functions but not (it appears) for a database.
Any ideas anyone? Thanks.
Why not fix your procs instead? Don't include database name when referencing objects within the 'current' database context, it's not good practice, as you've found.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 30, 2017 at 2:04 am
This was removed by the editor as SPAM
January 31, 2017 at 7:30 am
Phil Parkin - Thursday, January 26, 2017 3:45 PMLarry Kruse - Thursday, January 26, 2017 3:14 PMI've been searching around for a while, and I could not find a way that a database name can be aliased. I was hoping that someone here might be able to point me toward a solution.Here is the scenario: We've been using transaction replication for our large OLTP database. We're hosting the replicated copy on a different server and using a different database name for it. My_OLTP replicates to My_RPT
So naturally, the RPT one has a lot of stored procedures coded for accessing data there as My_RPT.dbo.table1 as an example.
We are planning on switching from transactional replication to AlwaysOn-Availability Groups setup and this will mean that the secondary copies have a database name called My_OLTP too and My_RPT will become invalid. Is there a way that I can create an alias/synonym for My_RPT which will cause a stored procedure to run against the name My_OLTP instead?
Synonyms only work for tables/stored procs/views/functions but not (it appears) for a database.
Any ideas anyone? Thanks.
Why not fix your procs instead? Don't include database name when referencing objects within the 'current' database context, it's not good practice, as you've found.
Stored procedures here are pretty complex and lengthy. Most contain lots of JOINs and multiple references to the database name. I think at last count, there were just over 13,000 stored procs - a lot of modifications would need to be made and of course, then they'd need to be tested/validated and there are a host of jobs running that reference the old DB name. Transactional replication has been entrenched here for the last 6-7 years. 🙂
January 31, 2017 at 8:05 am
Larry Kruse - Tuesday, January 31, 2017 7:30 AMPhil Parkin - Thursday, January 26, 2017 3:45 PMLarry Kruse - Thursday, January 26, 2017 3:14 PMI've been searching around for a while, and I could not find a way that a database name can be aliased. I was hoping that someone here might be able to point me toward a solution.Here is the scenario: We've been using transaction replication for our large OLTP database. We're hosting the replicated copy on a different server and using a different database name for it. My_OLTP replicates to My_RPT
So naturally, the RPT one has a lot of stored procedures coded for accessing data there as My_RPT.dbo.table1 as an example.
We are planning on switching from transactional replication to AlwaysOn-Availability Groups setup and this will mean that the secondary copies have a database name called My_OLTP too and My_RPT will become invalid. Is there a way that I can create an alias/synonym for My_RPT which will cause a stored procedure to run against the name My_OLTP instead?
Synonyms only work for tables/stored procs/views/functions but not (it appears) for a database.
Any ideas anyone? Thanks.
Why not fix your procs instead? Don't include database name when referencing objects within the 'current' database context, it's not good practice, as you've found.
Stored procedures here are pretty complex and lengthy. Most contain lots of JOINs and multiple references to the database name. I think at last count, there were just over 13,000 stored procs - a lot of modifications would need to be made and of course, then they'd need to be tested/validated and there are a host of jobs running that reference the old DB name. Transactional replication has been entrenched here for the last 6-7 years. 🙂
Understood. Should you decide to 'bite the bullet' and fix this up, there is a semi-automated way of doing it which does not require any manual editing of procs ...
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 31, 2017 at 1:30 pm
I'm not sure if this would apply to your specific scenario, but the application can switch out the database name in the connection string.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply