Are Synonyms Useful?

Question

Are Synonyms Useful?

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

I take it you never need to make copies of prod then or, perhaps, you use the same method to make such copies? There's just no way that I'd allow more than the 2 part naming convention in stored procedures... While it's overkill, the same holds true when I have to do something in MSDB from a different database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16167 More actions · Answer 2

Most was said already - I have gone an extra step with one of our teams and I have DDL triggers that prevent developers from using 3/4 part names in any code - when code is created I check dependencies and if any is found I rollback the transaction with an appropriate error message.

Only thing that I can't catch easily is when dynamic sql is used - a must use for some cases related to linked servers.

in VS - yes we can use the variables to replace with the appropriate database name - This can be done on the synonyms so we can still avoid the hard code within the code itself.

tsalha SSC Journeyman Points: 81 More actions · Answer 3

Hey Jeff,

Indeed, we deploy empty copies of prod instances via VS. Typically, we are not copying instances containing user data regularly. And if we need to move them to another server, mostly we move both databases (the referred one and the referencer) together.

I did not know, that you could use the vaiables on the synonyms directly. That sounds quite nice.

Tom Uellner SSCrazy Points: 2995 More actions · Answer 4

Our website DBs need to reference data in our main production database and when the web developers started writing code they were using 3/4 part naming This was a huge pain when moving from DEV -> SQA -> PRD as some database names change across servers. It didn't take long to introduce them to synonyms and for them to see how they made their job easier. Most of the synonyms we needed were created at the very beginning and only rarely now do we need to add new ones. We've never had any issues with developers not knowing about this as we are a fairly small team with good communications. For us, they are a huge plus.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

frederico_fonseca wrote:

in VS - yes we can use the variables to replace with the appropriate database name - This can be done on the synonyms so we can still avoid the hard code within the code itself.

Ok... NOW we're cooking with gas!!! Thanks for the tip, Frederico! Might be time for me to finally fire up VS.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Robin Wilson SSCertifiable Points: 5017 More actions · Answer 6

I pass the name of the server and database to the stored procedure and then use the exec command to execute this as a string then in Visual Studio I pass default values to the stored procedure so it can enable such features as switching between a live and training system but defaulting to live by default.

Presumably if I was to use synonyms this would not be session specific and make the change for everyone so useful if I wanted to switch all users between live/training, or for a server move (which I could do above by switching details held in the Application appsettings file too, but not useful if I want users to be able to switch themselves.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Robin Wilson wrote:

I pass the name of the server and database to the stored procedure and then use the exec command to execute this as a string then in Visual Studio I pass default values to the stored procedure so it can enable such features as switching between a live and training system but defaulting to live by default.
Presumably if I was to use synonyms this would not be session specific and make the change for everyone so useful if I wanted to switch all users between live/training, or for a server move (which I could do above by switching details held in the Application appsettings file too, but not useful if I want users to be able to switch themselves.

For front end situations, I agree that synonyms aren't the way to go. But I'm concerned for your data... if you make it so easy for users to switch between training and live production, what is to prevent them from making the mistake of working on a live system when they think they're working in a training system?

Also, you say you pass the name of the server and the database and then use the EXEC command. How are you actually forming that EXEC command? If it's by concatenation to form the string or simple replacement of string tokens, then your system is possibly subject to SQL Injection.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Robin Wilson SSCertifiable Points: 5017 More actions · Answer 8

Hello Jeff

I store the live and training connection strings in the .NET Core system appsettings.json file and use a switch on the end of the URL (?system=Training) but don't actually use the value in the string and only use this to determine if I should pass the live or training connection string. If blank I pass live, if value matches a predetermined value then I pass live else for any other values I pass training.

A bootstrap alert then shows at the top of the screen if the training system is being used so there can be no confusion.

Here is an example of a stored procedure that takes the database as a parameter:

https://github.com/robinwilson16/WLCProgressions/blob/master/WLCProgressions/StoredProcedures/SPR_PRG_SaveProgression.txt

In this example the stored procedure itself is vulnerable where any value could be passed including whole queries but then to cause issues a user would need direct access to SSMS or some other query execution tool so it should be no more vulnerable then any table/query. I could add additional code in each stored procedure but as these procedures are only executed from the main program I do not bother.

In another system I built a system can have different sets of users in the live and training systems and a dropdown is added to the login screen so when logging in the user is authenticated against the target database meaning different accounts can exist in live and training making it possible to only give a user access to training.

I also use this technique to make systems/stored procedures portable between different organisations or if they move different databases to other servers. Or another method I use is views where the main functionality is abstracted away from other systems so where I must use a 3rd party system to obtain titles for IDs then this would differ depending on the system in use so I just need to change the view and the main code remains the same such as this example:

https://github.com/robinwilson16/FISFundingSummariser/blob/master/Install/SPR_FIS_FundingData_1819.txt

So as long as the views return the correct column names everything works.

Robin

Robin Wilson SSCertifiable Points: 5017 More actions · Answer 9

To answer the other part of your question as to how exec is used, as I am passing the database name to the query I need to make the SQL into a string and replace the database name with a variable which is interrogated outside EXEC part so it forms the main string.

For this I need to declare the SQL String as NVARCHAR(MAX) then split this into sections ensuring these are not truncated.

For testing the resulting SQL I found the SQL was still being truncated so a workaround I found was to convert this into XML then it is no longer truncated and the full SQL is outputted along with a triangle bracket at the start and end but I can copy/paste to a new window and run, ensuring I pass parameter declarations above.

SELECT @SQLString AS [processing-instruction(x)] FOR XML PATH('')

Then essentially it is a question of doing this:

DECLARE @Database NVARCHAR(100) = 'LiveSystem'
DECLARE @ID INT = 1

DECLARE @SQLParams NVARCHAR(MAX) = N'SELECT T.fld1, T.fld2 FROM ' + @Database + '.Table T WHERE T.id = @ID'
DECLARE @SQLParams = N'@ID INT'

EXECUTE sp_executesql 
        @SQLString, 
        @SQLParams, 
        @ID = @ID;