March 31, 2010 at 10:52 am
About to migration production environments, doing some preparation on the reporting side.
If I have tables in REALDATABASE
REALDATABASE.dbo.customers, with real data in it
REALDATABASE.dbo.sales, with real data in it
And some views in REPORTINGDATABASE (same server)
REPORTINGDATABASE.dbo.report1, which is a complex view pointing to customers and sales
REPORTINGDATABASE.dbo.report2, which is a complex view pointing to customers and sales
(...there are many of these)
Can I add new views:
REPORTINGDATABASE.dbo.customers as "SELECT * FROM REALDATABASE.dbo.customers"
REPORTINGDATABASE.dbo.sales as "SELECT * FROM REALDATABASE.dbo.sales"
(I won't actually use SELECT *, I will list the fields!)
...and repoint my complex views to these simple views.
...then later, if I need to rename REALDATABASE, I just have to modify these new "simple" views, and all the "complex" views (report1, report2, etc) will update.
Are there performance/design implications?
Many Thanks
Andrew
March 31, 2010 at 12:10 pm
No performance issue. This is right approch. You can keep the linked server name same even if I need to rename REALDATABASE. You do not need to change the simple views also.:-)
March 31, 2010 at 2:18 pm
Using views is one approach - another approach is to use synonyms. For example:
-- First, create a schema for your synonyms. Not necessary, but organizes them for you
CREATE SCHEMA RealDB AUTHORIZATION dbo;
GO
-- Now, create your synonyms
CREATE SYNONYM RealDB.Customers FOR REALDATABASE.dbo.Customers;
CREATE SYNONYM RealDB.Sales FOR REALDATABASE.dbo.Sales;
...
Use the synonyms in your complex views
Now, if you need to move the database - rename it or whatever, just drop the synonym and recreate it.
DROP SYNONYM RealDB.Customers;
CREATE SYNONYM RealDB.Customers FOR NewREALDB.dbo.Customers;
DROP SYNONYM RealDB.Sales;
CREATE SYNONYM RealDB.Sales FOR NewREALDB.dbo.Sales;
...
You can also create synonyms for linked server objects, as in:
CREATE SYNONYM RealDB.Customers_Remote FOR RemoteServer.REALDB.dbo.Customers;
Again, move the linked server - change the db, etc... you just drop and recreate the synonyms.
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
April 1, 2010 at 10:37 am
Andrew-443839 (3/31/2010)
...then later, if I need to rename REALDATABASE
Just out of curiosity... any particular reason you foresee the need of renaming REALDATABASE in the future?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 1, 2010 at 10:55 am
PaulB-TheOneAndOnly (4/1/2010)
Andrew-443839 (3/31/2010)
...then later, if I need to rename REALDATABASEJust out of curiosity... any particular reason you foresee the need of renaming REALDATABASE in the future?
It's an ERP database going through a lengthy (all weekend) application upgrade process, which will be replaced with a (very similar) database with a different name.
Some of the reporting is based on some complex custom views sitting in another (empty) database on the same server. I want to simplify the reporting "switchover" as much as possible.
I'll probably go ahead with the "intermediate" views solution, thanks to everyone for the input.
Andrew
April 2, 2010 at 5:28 am
Jeffrey Williams-493691 (3/31/2010)
Using views is one approach - another approach is to use synonyms.
I would tend to prefer the synonym approach - assuming the database in question is SQL Server 2005 or above (regardless of compatibility level).
April 2, 2010 at 8:36 am
Paul White NZ (4/2/2010)
Jeffrey Williams-493691 (3/31/2010)
Using views is one approach - another approach is to use synonyms.I would tend to prefer the synonym approach - assuming the database in question is SQL Server 2005 or above (regardless of compatibility level).
I agree with you - that's why I suggested it. But, I am curious why you prefer this approach over using views.
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
April 2, 2010 at 9:10 am
Jeffrey Williams-493691 (4/2/2010)
I am curious why you prefer this approach over using views.
Synonyms were created to meet exactly the sort of need presented here.
They are easy to define, and avoid ugly SELECT * constructions.
They also don't go wrong if the definition of the referenced object changes - no need to refresh views or schema-bind (which isn't possible anyway outside the same database).
To my mind, views were always a workaround solution - synonyms seem...well...nicer. 🙂
April 2, 2010 at 9:13 am
Paul White NZ (4/2/2010)
Jeffrey Williams-493691 (4/2/2010)
I am curious why you prefer this approach over using views.Synonyms were created to meet exactly the sort of need presented here.
They are easy to define, and avoid ugly SELECT * constructions.
They also don't go wrong if the definition of the referenced object changes - no need to refresh views or schema-bind (which isn't possible anyway outside the same database).
To my mind, views were always a workaround solution - synonyms seem...well...nicer. 🙂
Exactly the same reasons I prefer them also. Thanks.
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
April 2, 2010 at 9:27 am
Jeffrey Williams-493691 (4/2/2010)
Exactly the same reasons I prefer them also. Thanks.
Heh, so I passed? 😉 😎
April 2, 2010 at 10:50 am
Paul White NZ (4/2/2010)
Jeffrey Williams-493691 (4/2/2010)
Exactly the same reasons I prefer them also. Thanks.Heh, so I passed? 😉 😎
No - more like I passed. Just confirms my own reasons for using synonyms is all 😀
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
April 2, 2010 at 4:04 pm
Hey, I like sysnonyms, one of the things I always wanted SQL Server to take from ORACLE, and now it's done.
Regards
Richellère
July 5, 2010 at 10:13 am
As a (rather late) post script to the thread, in the end we did use synonyms, with a successful outcome. 🙂
Thanks again.
Andrew
July 6, 2010 at 5:39 am
Andrew-443839 (7/5/2010)
As a (rather late) post script to the thread, in the end we did use synonyms, with a successful outcome. 🙂Thanks again.
Andrew
Good to hear, thanks for the update 🙂
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply