October 30, 2015 at 5:23 am
In DEV we have some VIEWs direct onto a 3rd Party Production DB.
Before deployment it is likely that these VIEWs will be changed to physical tables with some sort of overnight process freshening them up. The VIEWs were also intended to allows the Target table to be moved elsewhere, i.e. only one place to change the code - and I thought that was a smart move!
Now we find that, during DEV, the fact that the Other DB is in production and its data changes is a PITA. For example, the accounts staff have just done a Month End and all the lovely data we were using in DEV to test This & That has now gone!!
So I think we would be better having a restored COPY of Prod DB and point the VIEWs at that, and then we can be in control of when we choose to freshen up the data. Of course if I build that CopyDB today then I can restore yesterday's PROD data into it and we can get all that lovely test data back again!
So ... should I go through all the VIEWs and change them from
CREATE VIEW MyView
AS
SELECT Col1, Col2, ...
FROM OtherDB.dbo.SomeTable
to
CREATE VIEW MyView
AS
SELECT Col1, Col2, ...
FROM [highlight="#ffff11"]CopyDB[/highlight].dbo.SomeTable
or is there some advantage to creating a Synonym for "SomeTable" instead? (Yeah, I'll presumably have to change the VIEWs too, this time, but not again in future if/when it changes?)
I *think* that each OtherDB table is only referenced in only *one* VIEW, so maybe it is as broad-as-it-is-long?
But I've never used Synonyms before so I'd appreciate any thoughts as to whether there are other benefits, or disadvantages.
October 30, 2015 at 5:34 am
Your Dev db is on the same instance as the Prod db? If yes, that's the first best practice to apply: don't do this!
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 30, 2015 at 6:21 am
Still waiting for the IT guys to "rebuild" the old server for DEV after we migrated from it to a shiny new box for Prod ... its "real soon now" apparently ... but you are absolutely correct.
But even once that is done my problem remains, I still want the 3rd party data that I am accessing in a separate database, and I may well need access to current data (during testing at least) rather than a static copy DB.
I suppose what I really want is a Synonym to a Database (or Server.Database) rather than to an Object within a database. I don't think there is a way to do that?
October 30, 2015 at 7:17 am
Turns out there are nearly 200 files (one per object) that include a reference to remote database - rather more than I had assumed 🙁
My thought is to change all the code from
OtherDB.dbo.TableName
to
OtherDB.TableName
i.e. change from Database Reference to Schema Reference, and then set up a Synonym for that Schema & Table to point to the actual database (and table)
This would keep the other database's table names out of my namespace (I think?) as they will all be neatly grouped under the OtherDB schema.
I might just create all the Schema.Table synonyms and see how they look in SSMS / other tools and see if I like them like that.
Otherwise I think I need to create DummyDatabase, fill that full of Synonyms to the real database name that I am currently needing to reference, and then change all my "OtherDB.dbo.TableName" code usage to become "DummyDatabase.dbo.TableName"
October 30, 2015 at 1:58 pm
I suggest using synonyms for all such remote tables. But use only logical names, that reflect the business purpose, not any physical attributes (server name / location / etc.). You could even leave the table name the same as it is on the other server, if that's the best name for it. You want the physical location to be independent of the logical use.
Who knows? Maybe tomorrow some/all of those tables will move onto the same instance, or into a cloud somewhere. That should be, as much as possible, irrelevant to your code.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 31, 2015 at 1:43 am
Thanks. I went with a Schema with the same name as the remote database (which is, for me, the name of the 3rd party Vendor) and then I restored "yesterday's backup" to a new DB and this is the script I then used:
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'VendorName')
BEGIN
EXEC( 'CREATE SCHEMA VendorName' )
END
IF (SELECT OBJECT_ID('VendorName.ACCOUNT')) IS NOT NULL
DROP SYNONYM VendorName.ACCOUNT
CREATE SYNONYM VendorName.ACCOUNT
FOR RESTORE_VendorName_20151025.dbo.ACCOUNT
IF (SELECT OBJECT_ID('VendorName.ACTIONS')) IS NOT NULL
DROP SYNONYM VendorName.ACTIONS
CREATE SYNONYM VendorName.ACTIONS
FOR RESTORE_VendorName_20151025.dbo.ACTIONS
...
If the DB changes I can just globally change
RESTORE_VendorName_20151025.dbo.
to some other DB name and rerun the script.
Sadly I couldn't figure out a RegEx that would only match table names used in FROM, JOIN etc. so I had to modify the files by hand which took a while, but in future I should be able to adjust them at will.
I'd still like MSSQL to have a Database Synonym/Alias feature ...
In the APP code I had to change
CREATE VIEW MyView
AS
SELECT Col1, Col2, ...
FROM [highlight="#ffff11"]VendorName.dbo.[/highlight]SomeTable
to
CREATE VIEW MyView
AS
SELECT Col1, Col2, ...
FROM [highlight="#ffff11"]VendorName.[/highlight]SomeTable
November 1, 2015 at 7:46 pm
Kristen-173977 (10/31/2015)
I'd still like MSSQL to have a Database Synonym/Alias feature ...In the APP code I had to change
CREATE VIEW MyView
AS
SELECT Col1, Col2, ...
FROM [highlight="#ffff11"]VendorName.dbo.[/highlight]SomeTable
to
CREATE VIEW MyView
AS
SELECT Col1, Col2, ...
FROM [highlight="#ffff11"]VendorName.[/highlight]SomeTable
At work, we don't allow the use of 3 or 4 part naming conventions anywhere except in the synonyms themselves and it has worked out extraordinarily well. It combines the idea that Scott suggested about aliases with the convenience of having the alias be the same as the object name (most of the time... there are exceptions and Scott's alias idea shines there) so you don't have to change mindset. There have been times where we've had to redirect to a view instead of a table (don't ask) but didn't want to make any changes in the app. We used a synonym for that, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2015 at 1:03 am
Jeff Moden (11/1/2015)
At work, we don't allow the use of 3 or 4 part naming conventions anywhere except in the synonyms themselves and it has worked out extraordinarily well.
I could do with implementing that policy, retrospectively!!, here. We only use 3-part naming in VIEWs so I thought I was well insulated, but having gone through this exercise I now realise it is a lot easier to change a Synonym Script with all the aliases for a given database all-in-one-place than it is to edit one line in each, of lots of, separate View scripts.
We only (I think!) use 4-part naming as OPEN_QUERY and that would be with a linked server, so I would have the remote Table name hard-wired, but we usually use OPEN_QUERY with an INSERT INTO #TempTable using dynamic SQL, so the Table name is somewhat configurable, as such, and the Linked Server name can be recreated pointing to a different server, if the need arises.
Would I be better off with Synonyms there too do you think?
It combines the idea that Scott suggested about aliases with the convenience of having the alias be the same as the object name
I very specifically don't want OtherDB's table names polluting my namespace, as we have very specific naming conventions, and find them very useful, but using a Schema to segregate the Alien Names is working Just Fine so far 🙂
There have been times where we've had to redirect to a view instead of a table (don't ask) but didn't want to make any changes in the app. We used a synonym for that, as well.
I found a couple of functions that we are calling, in the OtherDB, so being able to Alias them too is handy 😉
January 27, 2020 at 4:49 pm
When you create database project in visual studio (you need to install SQL Server Data Tools), you can Add DACPAC package as reference to your remote database and create synonyms to this DACPAC
CREATE SYNONYM [OtherDB].[SomeTable]
FOR [$(OtherDB)].dbo.SomeTable
Where [$(OtherDB)] is variable, which you can set up in publish profile, for example you can have OtherDB name for production and OtherDB_Dev for testing purpose. Or you can directly change the reference to remote source.
The beauty of this approach is that in all cases you update only DACPAC reference and all synonyms will change automatically (this applies to all objects, not only synonyms but views and procedures, etc...).
The advantage of synonyms is more transparency of what remote objects your database use, and also Entity Framework ORM can use remote tables in one context with your database.. if you reference remote object directly (I don't know how it works with views), database always trigger distributed transaction which is performance costly and complicate application layer. I found this advantage recently.
I use this approach with synonyms in last two projects and it seems to be working fine.
I would like to know the experiences of others, if they successfully use synonyms for all remote objects and what schemes do they save them, or if they use another technique ?
January 27, 2020 at 7:31 pm
Jeff Moden (11/1/2015)
At work, we don't allow the use of 3 or 4 part naming conventions anywhere except in the synonyms themselves and it has worked out extraordinarily well.I could do with implementing that policy, retrospectively!!, here. We only use 3-part naming in VIEWs so I thought I was well insulated, but having gone through this exercise I now realise it is a lot easier to change a Synonym Script with all the aliases for a given database all-in-one-place than it is to edit one line in each, of lots of, separate View scripts.
We only (I think!) use 4-part naming as OPEN_QUERY and that would be with a linked server, so I would have the remote Table name hard-wired, but we usually use OPEN_QUERY with an INSERT INTO #TempTable using dynamic SQL, so the Table name is somewhat configurable, as such, and the Linked Server name can be recreated pointing to a different server, if the need arises.
Would I be better off with Synonyms there too do you think?
It combines the idea that Scott suggested about aliases with the convenience of having the alias be the same as the object nameI very specifically don't want OtherDB's table names polluting my namespace, as we have very specific naming conventions, and find them very useful, but using a Schema to segregate the Alien Names is working Just Fine so far 🙂
There have been times where we've had to redirect to a view instead of a table (don't ask) but didn't want to make any changes in the app. We used a synonym for that, as well.
I found a couple of functions that we are calling, in the OtherDB, so being able to Alias them too is handy 😉
As long as you have the same owner for both schemas, so that permissions changing is maintained, a separate schema should be just fine. If you have different owners, that could get a little tricky with permissions depending on the details of your permissions setup.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply