November 17, 2018 at 6:56 am
Jonathan AC Roberts - Thursday, November 15, 2018 8:23 AMJust wondering if when you use synonyms do you put them on a separate schema?
I do use a separate schema for the synonyms that reference another database, that way it's easy to keep an overview of all the referenced objects.
If the referenced database is renamed I only need to recreate all the synonyms in a single schema, this can even be done by the front-end application.
If the referenced object is on a different server, I use a Linked server object, so the synonyms use only 3 part naming.
Louis.
November 17, 2018 at 10:13 am
fahey.jonathan - Friday, November 16, 2018 3:12 PMJonathan AC Roberts - Thursday, November 15, 2018 10:18 AMYou can't create a synonym for a database, just database objects. But you can create a synonym for each object in a database.Thank you for responding to my question. I understand that synonyms cannot be created for databases, which is why I said, " I don't see a way to create a synonym for a database." I also understand that synonyms could be created for each object, which is why I said, "Those objects would need to be maintained individually each time."
I'm asking if there is some other way to create a reference to another database such that changing the reference once would affect all statements.
That is the advantage to using synonyms - which can be scripted as a drop/create for all defined synonyms. In the script you would change the destination - whether that is the database or linked server. Run the script with the new destination and all code that references the synonyms will now be redirected to the new destination.
This is much easier to manage than modifying all procedures, views, functions that reference that linked server/database when that linked server is moved/changed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 17, 2018 at 12:54 pm
Jonathan AC Roberts - Saturday, November 17, 2018 5:59 AMJeff Moden - Friday, November 16, 2018 3:38 PMfahey.jonathan - Friday, November 16, 2018 3:12 PMJonathan AC Roberts - Thursday, November 15, 2018 10:18 AMYou can't create a synonym for a database, just database objects. But you can create a synonym for each object in a database.Thank you for responding to my question. I understand that synonyms cannot be created for databases, which is why I said, " I don't see a way to create a synonym for a database." I also understand that synonyms could be created for each object, which is why I said, "Those objects would need to be maintained individually each time."
I'm asking if there is some other way to create a reference to another database such that changing the reference once would affect all statements.
You could create a specially named linked server that points to the server you're on. So long as you don't change the name of the linked server, you'd be golden no matter where you pointed the linked server.
Jeff,
How do you create a specially named linked server?
It's nothing special. It would simply be a linked server that you add to your environment that will always have the same name.
An example of this is that we have a need to read from DB2 computers from our SQL Servers. Originally, they were AS400 boxes and were later upgraded to PowerSystems boxes with computer names, IP address, logins and passwords. We didn't need to change a lick of code in our Dev/Staging/UAT, or Prod boxes. We had religiously used synonyms to point to the Linked Server and the names of the Linked Servers were exactly the same in all 3 environments. All we did was drop the existing linked servers, stand up 3 new ones with exactly the same name as the originals and we were done.
This same methodology keeps us from having to make code changes when we promote code from Dev to Staging/UAT, and Prod. Of course, we don't need to change the linked servers for that because they're all named the same.
You can do similar on a single SQL Server. Instead of having synonyms the refer to objects using the 4 part naming convention, you can have a "loop back" Linked Server that points to a database on the same server instance. If that database ever has to move to another machine, all you need to do is drop and recreate the Linked Server with the same name but pointing to a different server.
So, "Particular Name" is whatever you want it to be for the "particular case" you're using it for and keep that same name no matter what happens or where you move things.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 17, 2018 at 1:39 pm
in my shop I try to implement and force the use of synonyms. pitty I can't force them to do it (not being a production DBA), but do try my best.
previous projects within the company I did force it though as I was the architect on it - special setup that required 3 databases (source data which was loaded from remote systems, processing database and final destination db - migration project)
11 developers each with 2 db's (processing and destination) so they could do their testing without messing up the others. Synonyms all the way, created dynamically each time they needed to scrap their dbs and restart.
they really are one of the best things - but if someone messes them up it can also have devastating effects.
My current project, migrating some db's to a brand new centralized server, renaming all db's to a standard (XXXX_dbname where XXXX is the Line of Business) required me to go through all their SP', views and functions and replace all 3 and 4 part naming with synonyms - easiesh for most of it until yet another quite annoying problem - people renaming objects instead of dropping/recreating them with new name. ARGH!!!!
speaking of - any one has a good reason not to revoke execute rights on sp_rename?
As for schemas - we use them intensively depending on the projects - in one of my main projects at the moment we have a schema for each source system, and then a few more for the etl/supporting tables - e.g. error, staging, logging. this on staging db - final db, still being implemented, will be just a few schemas (Data Vault) so config, DV, BV and maybe 1 or 2 more.
November 18, 2018 at 3:46 am
Jeff Moden - Saturday, November 17, 2018 12:54 PMJonathan AC Roberts - Saturday, November 17, 2018 5:59 AMJeff Moden - Friday, November 16, 2018 3:38 PMfahey.jonathan - Friday, November 16, 2018 3:12 PMJonathan AC Roberts - Thursday, November 15, 2018 10:18 AMYou can't create a synonym for a database, just database objects. But you can create a synonym for each object in a database.Thank you for responding to my question. I understand that synonyms cannot be created for databases, which is why I said, " I don't see a way to create a synonym for a database." I also understand that synonyms could be created for each object, which is why I said, "Those objects would need to be maintained individually each time."
I'm asking if there is some other way to create a reference to another database such that changing the reference once would affect all statements.
You could create a specially named linked server that points to the server you're on. So long as you don't change the name of the linked server, you'd be golden no matter where you pointed the linked server.
Jeff,
How do you create a specially named linked server?It's nothing special. It would simply be a linked server that you add to your environment that will always have the same name.
An example of this is that we have a need to read from DB2 computers from our SQL Servers. Originally, they were AS400 boxes and were later upgraded to PowerSystems boxes with computer names, IP address, logins and passwords. We didn't need to change a lick of code in our Dev/Staging/UAT, or Prod boxes. We had religiously used synonyms to point to the Linked Server and the names of the Linked Servers were exactly the same in all 3 environments. All we did was drop the existing linked servers, stand up 3 new ones with exactly the same name as the originals and we were done.
This same methodology keeps us from having to make code changes when we promote code from Dev to Staging/UAT, and Prod. Of course, we don't need to change the linked servers for that because they're all named the same.
You can do similar on a single SQL Server. Instead of having synonyms the refer to objects using the 4 part naming convention, you can have a "loop back" Linked Server that points to a database on the same server instance. If that database ever has to move to another machine, all you need to do is drop and recreate the Linked Server with the same name but pointing to a different server.
So, "Particular Name" is whatever you want it to be for the "particular case" you're using it for and keep that same name no matter what happens or where you move things.
Thanks, whenever I've set up linked servers they always have to have the name of the SQL Server instance. I've not seen an option to change the name of them?
November 18, 2018 at 11:15 am
Jonathan AC Roberts - Sunday, November 18, 2018 3:46 AMJeff Moden - Saturday, November 17, 2018 12:54 PMJonathan AC Roberts - Saturday, November 17, 2018 5:59 AMJeff Moden - Friday, November 16, 2018 3:38 PMfahey.jonathan - Friday, November 16, 2018 3:12 PMJonathan AC Roberts - Thursday, November 15, 2018 10:18 AMYou can't create a synonym for a database, just database objects. But you can create a synonym for each object in a database.Thank you for responding to my question. I understand that synonyms cannot be created for databases, which is why I said, " I don't see a way to create a synonym for a database." I also understand that synonyms could be created for each object, which is why I said, "Those objects would need to be maintained individually each time."
I'm asking if there is some other way to create a reference to another database such that changing the reference once would affect all statements.
You could create a specially named linked server that points to the server you're on. So long as you don't change the name of the linked server, you'd be golden no matter where you pointed the linked server.
Jeff,
How do you create a specially named linked server?It's nothing special. It would simply be a linked server that you add to your environment that will always have the same name.
An example of this is that we have a need to read from DB2 computers from our SQL Servers. Originally, they were AS400 boxes and were later upgraded to PowerSystems boxes with computer names, IP address, logins and passwords. We didn't need to change a lick of code in our Dev/Staging/UAT, or Prod boxes. We had religiously used synonyms to point to the Linked Server and the names of the Linked Servers were exactly the same in all 3 environments. All we did was drop the existing linked servers, stand up 3 new ones with exactly the same name as the originals and we were done.
This same methodology keeps us from having to make code changes when we promote code from Dev to Staging/UAT, and Prod. Of course, we don't need to change the linked servers for that because they're all named the same.
You can do similar on a single SQL Server. Instead of having synonyms the refer to objects using the 4 part naming convention, you can have a "loop back" Linked Server that points to a database on the same server instance. If that database ever has to move to another machine, all you need to do is drop and recreate the Linked Server with the same name but pointing to a different server.
So, "Particular Name" is whatever you want it to be for the "particular case" you're using it for and keep that same name no matter what happens or where you move things.
Thanks, whenever I've set up linked servers they always have to have the name of the SQL Server instance. I've not seen an option to change the name of them?
CREATE PROCEDURE util.CreateLinkedServer
/**********************************************************************************************************************
Purpose:
Create a local Linked Server that has a different name than the remote server using standardized settings that work
the best in most cases.
Example Usage:
EXEC util.CreateLinkedServer @LinkedSvrName, @RmtSvrName, @RmtUser, @RmtPW;
EXEC util.CreateLinkedServer 'LinkedSvrName', 'RmtSvrName', 'RmtUser', 'RmtPW';
Revision History:
Rev 01 - 16 Feb 2017 - Jeff Moden
- Initial Creation and Unit Test
**********************************************************************************************************************/
--===== I/O Parameters
@LinkedSvrName SYSNAME
,@RmtSvrName SYSNAME
,@RmtUser SYSNAME
,@RmtPW SYSNAME
AS
--=====================================================================================================================
-- Create the new linked server according to the input parameters
--=====================================================================================================================
EXEC sp_addlinkedserver
@server = @LinkedSvrName
,@srvproduct = N'NA'
,@provider = N'SQLNCLI11'
,@datasrc = @RmtSvrName
;
EXEC sp_addlinkedsrvlogin
@rmtsrvname = @LinkedSvrName
,@useself = N'False'
,@locallogin = NULL
,@rmtuser = @RmtUser
,@rmtpassword = @RmtPW
;
--=====================================================================================================================
-- Configure the new linked server for our best usage.
--=====================================================================================================================
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'collation compatible' , @optvalue=N'false';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'collation name' , @optvalue=null;
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'connect timeout' , @optvalue=N'0';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'data access' , @optvalue=N'true';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'dist' , @optvalue=N'false';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'lazy schema validation' , @optvalue=N'false';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'pub' , @optvalue=N'false';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'query timeout' , @optvalue=N'0';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'remote proc transaction promotion' , @optvalue=N'true';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'rpc' , @optvalue=N'true';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'rpc out' , @optvalue=N'true';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'sub' , @optvalue=N'false';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'use remote collation' , @optvalue=N'true';
--Jeff Moden
Change is inevitable... Change for the better is not.
November 18, 2018 at 11:42 am
Jeff Moden - Sunday, November 18, 2018 11:15 AMJonathan AC Roberts - Sunday, November 18, 2018 3:46 AMJeff Moden - Saturday, November 17, 2018 12:54 PMJonathan AC Roberts - Saturday, November 17, 2018 5:59 AMJeff Moden - Friday, November 16, 2018 3:38 PMfahey.jonathan - Friday, November 16, 2018 3:12 PMJonathan AC Roberts - Thursday, November 15, 2018 10:18 AMYou can't create a synonym for a database, just database objects. But you can create a synonym for each object in a database.Thank you for responding to my question. I understand that synonyms cannot be created for databases, which is why I said, " I don't see a way to create a synonym for a database." I also understand that synonyms could be created for each object, which is why I said, "Those objects would need to be maintained individually each time."
I'm asking if there is some other way to create a reference to another database such that changing the reference once would affect all statements.
You could create a specially named linked server that points to the server you're on. So long as you don't change the name of the linked server, you'd be golden no matter where you pointed the linked server.
Jeff,
How do you create a specially named linked server?It's nothing special. It would simply be a linked server that you add to your environment that will always have the same name.
An example of this is that we have a need to read from DB2 computers from our SQL Servers. Originally, they were AS400 boxes and were later upgraded to PowerSystems boxes with computer names, IP address, logins and passwords. We didn't need to change a lick of code in our Dev/Staging/UAT, or Prod boxes. We had religiously used synonyms to point to the Linked Server and the names of the Linked Servers were exactly the same in all 3 environments. All we did was drop the existing linked servers, stand up 3 new ones with exactly the same name as the originals and we were done.
This same methodology keeps us from having to make code changes when we promote code from Dev to Staging/UAT, and Prod. Of course, we don't need to change the linked servers for that because they're all named the same.
You can do similar on a single SQL Server. Instead of having synonyms the refer to objects using the 4 part naming convention, you can have a "loop back" Linked Server that points to a database on the same server instance. If that database ever has to move to another machine, all you need to do is drop and recreate the Linked Server with the same name but pointing to a different server.
So, "Particular Name" is whatever you want it to be for the "particular case" you're using it for and keep that same name no matter what happens or where you move things.
Thanks, whenever I've set up linked servers they always have to have the name of the SQL Server instance. I've not seen an option to change the name of them?
Ah... yeah... I've run into that very same problem many times and got tired of asking myself "OK.... how did I do that before?" and so I created a store procedure for my "utility belt" at work that does it for me. Feel free to customize the following to suit your needs.
CREATE PROCEDURE util.CreateLinkedServer
/**********************************************************************************************************************
Purpose:
Create a local Linked Server that has a different name than the remote server using standardized settings that work
the best in most cases.Example Usage:
EXEC util.CreateLinkedServer @LinkedSvrName, @RmtSvrName, @RmtUser, @RmtPW;
EXEC util.CreateLinkedServer 'LinkedSvrName', 'RmtSvrName', 'RmtUser', 'RmtPW';Revision History:
Rev 01 - 16 Feb 2017 - Jeff Moden
- Initial Creation and Unit Test
**********************************************************************************************************************/
--===== I/O Parameters
@LinkedSvrName SYSNAME
,@RmtSvrName SYSNAME
,@RmtUser SYSNAME
,@RmtPW SYSNAME
AS
--=====================================================================================================================
-- Create the new linked server according to the input parameters
--=====================================================================================================================
EXEC sp_addlinkedserver
@server = @LinkedSvrName
,@srvproduct = N'NA'
,@provider = N'SQLNCLI11'
,@datasrc = @RmtSvrName
;
EXEC sp_addlinkedsrvlogin
@rmtsrvname = @LinkedSvrName
,@useself = N'False'
,@locallogin = NULL
,@rmtuser = @RmtUser
,@rmtpassword = @RmtPW
;
--=====================================================================================================================
-- Configure the new linked server for our best usage.
--=====================================================================================================================
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'collation compatible' , @optvalue=N'false';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'collation name' , @optvalue=null;
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'connect timeout' , @optvalue=N'0';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'data access' , @optvalue=N'true';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'dist' , @optvalue=N'false';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'lazy schema validation' , @optvalue=N'false';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'pub' , @optvalue=N'false';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'query timeout' , @optvalue=N'0';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'remote proc transaction promotion' , @optvalue=N'true';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'rpc' , @optvalue=N'true';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'rpc out' , @optvalue=N'true';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'sub' , @optvalue=N'false';
EXEC sp_serveroption @server = @LinkedSvrName, @optname = N'use remote collation' , @optvalue=N'true';
Thanks Jeff, looks a useful script.
November 19, 2018 at 4:29 pm
Jeff's earlier message said, "If you're talking about 3 and 4 part naming in any of the code itself, caveat emptor. As databases grow, they sometimes move and you end up needing to use either Linked Servers or things like OPENROWSET, etc, etc. Also (whether anyone agrees with it or not), there are some shops that want carry the same database on the same instance more than once for "development" purposes. For example, carry one copy of the database for "Project A" and another for "Project B". If any of the code uses even 3 part naming, you have a serious problem." So I'm trying to determine if there is a way to do what he recommends in an environment where objects in DatabaseA need assets in DababaseB.
Using a linked server requires 4-part names - something the earlier section said to avoid - so linked servers are out for this thought experiment. Using synonyms means the objects need to be recreated every time, which means a dedicated script for each environment every time the database is restored, moved, etc. Yes, I know how to manage that, and can do it creating scripts or even a table partially keyed on @@SERVERNAME to dynamically recreate all the necessary objects, but I'm trying to find a simpler way.
The answer may be that there is no simpler way. If object in DatabaseA require assets in DatabaseB, then three-part naming seems to be required, either in the objects or in the synonyms. That means that hosting two copies of both databases on a single server will require rebuilding all objects that use three-part names, either the objects themselves or the synonyms they use. Does that sound right?
November 19, 2018 at 4:57 pm
Using a linked server can also be done through synonyms - so they do fall under the same as a hard-coded db other than the code that is referencing them.
THE main benefit of synonyms is that they are dead easy to change - having hard-coded database names (including linked servers) on code/view means either manually changing the code or recreating the view to point to the new db name.
As for the likes of openrowset - in my shop some teams do use them - and most have by now been changed to use dynamic sql and configuration tables so they can be changed on the fly when required.
November 19, 2018 at 7:43 pm
fahey.jonathan - Monday, November 19, 2018 4:29 PMJeff's earlier message said, "If you're talking about 3 and 4 part naming in any of the code itself, caveat emptor. As databases grow, they sometimes move and you end up needing to use either Linked Servers or things like OPENROWSET, etc, etc. Also (whether anyone agrees with it or not), there are some shops that want carry the same database on the same instance more than once for "development" purposes. For example, carry one copy of the database for "Project A" and another for "Project B". If any of the code uses even 3 part naming, you have a serious problem." So I'm trying to determine if there is a way to do what he recommends in an environment where objects in DatabaseA need assets in DababaseB.Using a linked server requires 4-part names - something the earlier section said to avoid - so linked servers are out for this thought experiment. Using synonyms means the objects need to be recreated every time, which means a dedicated script for each environment every time the database is restored, moved, etc. Yes, I know how to manage that, and can do it creating scripts or even a table partially keyed on @@SERVERNAME to dynamically recreate all the necessary objects, but I'm trying to find a simpler way.
The answer may be that there is no simpler way. If object in DatabaseA require assets in DatabaseB, then three-part naming seems to be required, either in the objects or in the synonyms. That means that hosting two copies of both databases on a single server will require rebuilding all objects that use three-part names, either the objects themselves or the synonyms they use. Does that sound right?
The linked server itself requires 3 or 4 part names. The code that uses it doesn't have to if you use synonyms in conjunction with the linked server. As Frederico points out above and as I've pointed out, they're drop dead simple to modify in bulk and they don't require you to change any of your actual code. Remember that synonyms and Linked Servers are objects that don't change code.
So, yes, if you have two "identical" databases that live on the same instance and they need to either point to different databases or machines, them "just" synonyms will do for those databases that point to databases on the same server and synonyms that point to Linked Server would do for anything that needed to "talk" to other instances or machines. No matter what happens, all you really need to do is change some really easy to find synonyms. For the Dev to Staging to Prod deployments, you probably won't have to even change those, in many cases, if you have the same named linked servers or databases in each environment.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply