June 24, 2016 at 8:20 am
I use synonyms for testing. I have a testing DB that has business object mock ups, staged data and testing inputs. I then create/sync the synonyms to the application db object and fire of my tests. I don't have to worry about excluding a schema during comparisons or otherwise clutter up the application db with test fixtures. It's been working fantastic for several years.
The only gottcha I discovered is that you can't create a synonym of a synonym. So, in my sync routine, if the object in the app db is a synonym, I create the same synonym using the app db's object source. Very easy and clean.
Now I can kick off the testing db's orders_place, orders_fullfill, client_create, client_update test fixtures, that call the app db synonym for placing and fulfilling orders and managing clients. My testing scenarios are pre-defined with expected results. Nothing get promoted to the QA environment without going through regression tests. QA becomes focused on business value and validation of new features, that get added to the regression suite. We've increased our ability to deliver because we catch bugs so much earlier and know that we haven't broken anything else. The only down side - regression testing is taking longer because the number of tests has increased. It became my justification for requesting bigger/better hardware in DEV. 😀
--Paul Hunter
June 24, 2016 at 8:45 am
We started using synonyms a couple years ago and have found them to be a great way to manage lower environments especially for restores.
Also, we usually create a schema for them of the database name. i.e. customer database synonym for the customer phone table would be:
customer.syn_customerphone
June 24, 2016 at 10:45 am
bob.probst (9/11/2014)
I use synonyms to refer to any object outside of the database that I'm working in. They make for a useful way to encapsulate an object that I otherwise have no control over. If a database moves or a foreign object gets renamed, I just update synonyms and move on. They are also very useful for moving between dev and prod environments where external objects may be in different locations.
In our environment, the database names have a suffix for different environments such as _DEV, _QA, _UA, _PROD. I've found synonyms to be the easiest way for the developers to manage their queries that use multiple databases, so they're not putting hardcoded database names in their stored procs. Makes it easier to promote their code because then they don't have to change everything querying xxx_DEV database when it's time for testing or rollout.
June 25, 2016 at 8:59 am
Chris Harshman (6/24/2016)
bob.probst (9/11/2014)
I use synonyms to refer to any object outside of the database that I'm working in. They make for a useful way to encapsulate an object that I otherwise have no control over. If a database moves or a foreign object gets renamed, I just update synonyms and move on. They are also very useful for moving between dev and prod environments where external objects may be in different locations.In our environment, the database names have a suffix for different environments such as _DEV, _QA, _UA, _PROD. I've found synonyms to be the easiest way for the developers to manage their queries that use multiple databases, so they're not putting hardcoded database names in their stored procs. Makes it easier to promote their code because then they don't have to change everything querying xxx_DEV database when it's time for testing or rollout.
Amen to that! Same thing here. Without synonyms, it would take forever and be an incredibly error prone process. To wit, we don't allow anything more than the 2 part naming convention in any of our code and it has been an absolute godsend. In many cases, it's also allowed us to keep databases up an running massive refreshes of data occur even in production databases. While using the "Set A" version of tables, we can create and populate "Set B". Once "Set B" has been loaded and completely validated, we just repoint the synonyms. Total "downtime" for even an hours-long process is less than a couple dozen milliseconds. It doesn't break anything if the synonym is in use, either. The system will wait (be thoughtfully blocked for the drop) until it's not being used and then the drop just continues with no fanfare or errors.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2016 at 1:54 pm
I have recently encountered a case where I have to process a database with literally thousands of views. In these views the first 29 columns are always the same, and the columns start diverging with column 30. One important part of my code is interested in only the first 29 columns. I use two little bits of dynamic SQL to create and drop a SYNONYM of a fixed name on the different views (Books Online has a specific note that SYNONYMs can be created and dropped in dynamic SQL). Then the rest of the ~100 lines of code in my stored procedure can be inline SQL using the SYNONYM to reference the current view of interest. (It's kind of like interfaces in C#.)
I did not compare performance differences between using the SYNONYMs and having most of the procedure's code in dynamic SQL; whatever little difference there might be doesn't really matter to this particular application. Color coding in the editor and "CREATE PROCEDURE"-time code checking gives me more reliability to my development process to get more correct results.
I do wonder, though, about the optimizer's behavior when it encounters a SYNONYM. Most of the time it checks the cache based on exact text matches. When the definition of a SYNONYM changes, and the objects referencing it do not, one would hope the optimizer has some notion that things have changed.
Sincerely,
Daniel
June 27, 2016 at 11:07 pm
Never used synonyms, but I inherited an old database that has badly named objects with no real naming conventions. For example, there is a table called Order that contains sales order header data (actually, multiple types of orders, but that's another issue) - being a reserved word SQL intellisense gives me grief whenever I am writing queries, having to always wrap it in [] or alias the table. I could simply create a view and reference that, but I like the idea of using a synonym instead.
Chris
June 28, 2016 at 7:30 am
While a synonym will allow you to create a better name, it does nothing to handle the mix of order types. You may want to explore views that use the "with check option". It will not permit you to save a record that would violate the where clause. Add in schemabinding and you have a stable and properly named object.
Something like this...
create view dbo.SalesOrderHeader
with schemabinding
as
select list
, of
, columns
from dbo.[Order]
where OrderType = 'SalesOrder'
with check option;
--Paul Hunter
June 28, 2016 at 3:07 pm
aj2red (6/24/2016)
If you live in a universe where you control database and scheming naming conventions, then I imagine you don't need to synonyms.However, I don't.
For example:
We have written multiple interfaces between different Packaged Software applications.
Each one sits in its own database.
Because multiple environments sit on the same server, I have examples like this:
Server1/VendorADatabase
Server1/VendorBDatabase
Server2/VendorATestDatabase
Server2/VendorADevDatabase
Server2/VendorBTestdatabase
Server2/VendorBDevDatabase
To both create interfaces and reports, I need to reference multiple databases. I don't want to hardcode database names in my code. Synonyms (and views) solve the issue.
To make life easier, we have one stored procedure that automatically generates the appropriate synoyms and view each time a database gets refreshed. I posted on this a while ago:
For those who say there's no need for this, please explain how you'd solve this problem.
Not to imply that synonyms are not needed, but if they did not exist you would not be stuck here either. In your SSMS window type CTRL +H , and there you have the find and replace feature. Though not as work saving on the developer as the synonym, still it gets the job done without too much fuss "relatively" speaking.
----------------------------------------------------
June 28, 2016 at 3:20 pm
Control-H?
Two things:
1) Promoting objects - things to be checked every time something gets moved.
2) copying production to test or dev.
let's say you have 200 objects in production.
You copy production to test. You now need to fix 200 objects.
if that works in universe, go for it.
June 28, 2016 at 3:46 pm
aj2red (6/28/2016)
Control-H?Two things:
1) Promoting objects - things to be checked every time something gets moved.
2) copying production to test or dev.
let's say you have 200 objects in production.
You copy production to test. You now need to fix 200 objects.
if that works in universe, go for it.
This does not carry forward my response to your initial question given the scenario you presented, as much as it does asking new questions.
a) I wouldn't change things in production without impact analysis. That is not escapable regardless.
b) Why would you need to change 200 objects (or create 200 synonyms)? To change them back to what they were named in the first place? I doubt this is a realistic scenario in contrast to the case of just changing the name of the database, as you listed directly in your question ( was the basis for your specific initial question which I addressed for you) when a Dev or Test version of the database is created to work from.
----------------------------------------------------
June 28, 2016 at 4:01 pm
MMartin1 (6/28/2016)
aj2red (6/24/2016)
If you live in a universe where you control database and scheming naming conventions, then I imagine you don't need to synonyms.However, I don't.
For example:
We have written multiple interfaces between different Packaged Software applications.
Each one sits in its own database.
Because multiple environments sit on the same server, I have examples like this:
Server1/VendorADatabase
Server1/VendorBDatabase
Server2/VendorATestDatabase
Server2/VendorADevDatabase
Server2/VendorBTestdatabase
Server2/VendorBDevDatabase
To both create interfaces and reports, I need to reference multiple databases. I don't want to hardcode database names in my code. Synonyms (and views) solve the issue.
To make life easier, we have one stored procedure that automatically generates the appropriate synoyms and view each time a database gets refreshed. I posted on this a while ago:
For those who say there's no need for this, please explain how you'd solve this problem.
Not to imply that synonyms are not needed, but if they did not exist you would not be stuck here either. In your SSMS window type CTRL +H , and there you have the find and replace feature. Though not as work saving on the developer as the synonym, still it gets the job done without too much fuss "relatively" speaking.
Don't need that, either. Before synonyms, there were "pass through views", which can actually be even more effective.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 28, 2016 at 4:04 pm
We keep a database and table in each lower environment to map the database names to the appropriate server\database for each distinct environment.
Then I have sql script to dynamically drop and create all the synonyms using the table as the cross reference.
That runs in a second or so no matter how many objects there are.
Our restore procedure for lower environments includes this step.
I suppose you could have something similar in production environments, but I can't imagine why. We never restore from lower to upper environments.
June 28, 2016 at 4:50 pm
I do find the comment that a previous post made on the lack of transparency of synonyms within reporting tools (only show tables and views) a bit unsettling. I also wonder about the performance risk from not knowing you are working with a remote table potentially. I would like to import such records to a local temp table to then build statistics on ideally when needed.
Also, with code , if I have a mistake in the from clause (selecting the wrong table) it can potentially be detected in a review. A mistake in a synonym caused by miscommunication is more hidden .
That said, realise the need to rename vendor objects to more business like naming is a huge benefit as I have been there. But a pass through view can do the same. So I take it these are useful when the object being referenced is changing periodically (which itself raises other questions).
----------------------------------------------------
June 28, 2016 at 5:27 pm
I am wondering about the three part naming convention and its substitution with a synonym. I envision a scenario where the code reads like
/* block 1*/
SELECT ...
FROM ProductionDB_test.dbo.tableA
I instead use the synonym for tableA
/* block 2*/
SELECT ...
FROM synTableA
If I present /* block 1*/ to a live system (assuming no synonym) with this wrong database I would think the statement would fail as it should. I dont think there should be a database named "ProductionDB_test" on a live production system. Else if there is such a database there then what about getting it wrong in the synonym when you have to maintain plenty of synonyms and an error entry sneaks in? Just wondering what prompts security with synonyms on three parts named objects. Thanks for the good article!
----------------------------------------------------
June 28, 2016 at 6:00 pm
MMartin1 (6/28/2016)
I am wondering about the three part naming convention and its substitution with a synonym. I envision a scenario where the code reads like
/* block 1*/
SELECT ...
FROM ProductionDB_test.dbo.tableA
I instead use the synonym for tableA
/* block 2*/
SELECT ...
FROM synTableA
If I present /* block 1*/ to a live system (assuming no synonym) with this wrong database I would think the statement would fail as it should. I dont think there should be a database named "ProductionDB_test" on a live production system. Else if there is such a database there then what about getting it wrong in the synonym when you have to maintain plenty of synonyms and an error entry sneaks in? Just wondering what prompts security with synonyms on three parts named objects. Thanks for the good article!
The second block should use the 2 part naming convention. It helps prevent schema accidents and helps a bit (at least it used to back in the day) with performance.
As for getting synonyms wrong, sure, I agree. It's certainly possible but, once established, synonyms usually don't change and you normally don't restore Dev to Prod to do promotions. As with all else, though, "It Depends". For us, such synonyms have been a life saver because of a sometimes silly but usually effective requirement to name databases with an "underscore environment" extension. This also allows us to have multiple nearly identical databases on Dev boxes so that multiple different tacks on development of large batch processing systems can simultaneously exist without necessarily creating a full environment (other utility, staging, and security databases) for each endeavor.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 61 through 75 (of 80 total)
You must be logged in to reply to this topic. Login to reply