synonyms vs views when using a linked server

Question

synonyms vs views when using a linked server

November 5, 2018 at 10:45 pm

This was removed by the editor as SPAM
November 5, 2018 at 10:49 pm

This was removed by the editor as SPAM

Viewing 7 posts - 16 through 21 (of 21 total)

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

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

JSB_89 - Saturday, November 3, 2018 6:42 PM
Jeff Moden - Saturday, November 3, 2018 12:12 PM
JSB_89 - Friday, November 2, 2018 4:24 PM
The company is driven by business so whatever the business needs we need to provide, evn for setting up the QA test env they need the latest prod data not with any test data. they want to see the lifecycle of the acc how it reacts with different scenarios .
If it's THAT critical... setup replication. But, before you do, have you tied down the Dev and QA environments so that they're at least as secure as Prod so that no one can walk off with critical PII and other sensitive information that will have you reading about yourself in the morning news? If you haven't, then do what the business really needs and protect it.
we do have replication setup for DataWarehouse environment .. and we get only a subset of tables but the reports and QA and DEV need all the tables . we do redact sensitive info once we restore the DB to lower env's
HTH

Doesn't help when you provide full read access to the production environment.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

JSB_89 - Saturday, November 3, 2018 6:42 PM
Jeff Moden - Saturday, November 3, 2018 12:12 PM
JSB_89 - Friday, November 2, 2018 4:24 PM
The company is driven by business so whatever the business needs we need to provide, evn for setting up the QA test env they need the latest prod data not with any test data. they want to see the lifecycle of the acc how it reacts with different scenarios .
If it's THAT critical... setup replication. But, before you do, have you tied down the Dev and QA environments so that they're at least as secure as Prod so that no one can walk off with critical PII and other sensitive information that will have you reading about yourself in the morning news? If you haven't, then do what the business really needs and protect it.
we do have replication setup for DataWarehouse environment .. and we get only a subset of tables but the reports and QA and DEV need all the tables . we do redact sensitive info once we restore the DB to lower env's
HTH

If you have a SAN, with "SAN Snapshot" capabilities, you could try those. They're nasty fast. The redaction you do will still be the thing that takes the longest.

--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)
Intro to Tally Tables and Functions

JSB_89 Ten Centuries Points: 1066 More actions · Answer 3

Thanx Jeff will check with the SAN team, but going back to my original question views and synonyms are a big no no correct??

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

JSB_89 - Sunday, November 4, 2018 8:32 AM
Thanx Jeff will check with the SAN team, but going back to my original question views and synonyms are a big no no correct??

If you use views or synonyms in conjunction with linked servers from your Dev and QA boxes to point back to the Prod system and you allow users to use those views or synonyms, then you're making a mistake for the following reasons...
1. Synonyms don't allow for data obfuscation.
2. Data obfuscation provided by views can be broken pretty easily and data masking can easily be broken even by a rank newbie.
3. If someone makes a mistake in code in the lesser environments, you could paralyze your Prod box.

The bottom line (IMHO) is that there should never be something in a lesser environment that can pull data directly from the Prod system. If you need something to be in the lesser environments, the Prod system should push it there after the data has been "cleaned". The exception would be to do a restore or a SAN snapshot and not expose it to the users of the lesser environment until it has been cleaned or the sensitive data is actually encrypted.

--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)
Intro to Tally Tables and Functions

JSB_89 Ten Centuries Points: 1066 More actions · Answer 5

Jeff Moden - Sunday, November 4, 2018 9:13 AM
JSB_89 - Sunday, November 4, 2018 8:32 AM
Thanx Jeff will check with the SAN team, but going back to my original question views and synonyms are a big no no correct??
If you use views or synonyms in conjunction with linked servers from your Dev and QA boxes to point back to the Prod system and you allow users to use those views or synonyms, then you're making a mistake for the following reasons...
1. Synonyms don't allow for data obfuscation.
2. Data obfuscation provided by views can be broken pretty easily and data masking can easily be broken even by a rank newbie.
3. If someone makes a mistake in code in the lesser environments, you could paralyze your Prod box.
The bottom line (IMHO) is that there should never be something in a lesser environment that can pull data directly from the Prod system. If you need something to be in the lesser environments, the Prod system should push it there after the data has been "cleaned". The exception would be to do a restore or a SAN snapshot and not expose it to the users of the lesser environment until it has been cleaned or the sensitive data is actually encrypted.

Thanks Jeff , i'll check with the SAN guys and update here if there is a possibility .