| Author |
Topic |
|
jaybee
Yak Posting Veteran
72 Posts |
 Posted - 2008-10-17 : 14:16:37
|
| Not so much a right/wrong answer as an attempt to decipher the damn thing....You are asked to refresh a database in the test environment from the live server. Describe what questions you may ask and checks you will need to make before carrying out the task, and what steps are required for this task (assume a backup/restore operation).Yep, the question said 'refresh'. Makes no sense to me, but maybe I've just never used that term.Ok, my guess is that the data in the test server needs to be UPDATED from the live - whaddya think?Cheers,JBEdit - this is for a coming interview - the guy very leniently emailed me the question!! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-17 : 14:26:58
|
| yup.exactly...it just says you want to make data in test same as that which is currently in live. so best method is to backup the live server db and restore it to test server. of course, you have to then mnaually run any permission scripts for test and also require to scramble some sensitive live data too (like employee details, credit card numbers,...) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-10-17 : 14:31:34
|
| The questions that I ask of the person requesting it are:1. Do you want the userids copied down?2. Should I automate the refresh? If yes, then how often should it happen?3. Does the data need to be scrubbed?4. Is there anything that you need outside of the user database (besides logins as that was address in question 1) such as DTS/SSIS packages, jobs, other objects in other databases, etc.visakh16, a permissions script shouldn't be required as that's all stored in the database and therefore the restore will take care of that.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2008-10-17 : 15:16:28
|
quote:
Originally posted by tkizervisakh16, a permissions script shouldn't be required as that's all stored in the database and therefore the restore will take care of that.
But, you often have different accounts between prod/qa/dev. We don't restore prod logins on a dev refresh. Which means that we've got to realign some permissions at the object level. It can be mitigated via proper group security, however I've not seen that 100% of the time - each system tends to be it's own exception....Your friendly High-Tech Janitor:http://grayburn.wordpress.com |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-18 : 04:23:39
|
quote:
Originally posted by tkizer
The questions that I ask of the person requesting it are:1. Do you want the userids copied down?2. Should I automate the refresh? If yes, then how often should it happen?3. Does the data need to be scrubbed?4. Is there anything that you need outside of the user database (besides logins as that was address in question 1) such as DTS/SSIS packages, jobs, other objects in other databases, etc.visakh16, a permissions script shouldn't be required as that's all stored in the database and therefore the restore will take care of that.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
yup.it will do. But it will copy permissions just as what it exists in production. But in test you will obvioulsy be maintaining different set of permissions for each role from that in live, like giving key testers permission to insert some test data which obviously wont be case with live. so in such cases isnt it better to keep permissions scripted from existing test db and reapply it after restoration from live? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-18 : 13:32:58
|
quote:
Originally posted by tkizer
On our refreshed databases, we don't do it that way. We simply add users to certain roles rather than running a permissions script.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
But we do it way i told. they script out permissions before and then after restoration applies it back. We also have couple of scrambling procedures which will be run afterwards to scramble sensitive data like salary information in employees table. |
|
|
|
jaybee
Yak Posting Veteran
72 Posts |
Posted - 2008-10-19 : 07:13:14
|
quote:
Originally posted by visakh16
quote:
Originally posted by tkizer
On our refreshed databases, we don't do it that way. We simply add users to certain roles rather than running a permissions script.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
But we do it way i told. they script out permissions before and then after restoration applies it back. We also have couple of scrambling procedures which will be run afterwards to scramble sensitive data like salary information in employees table.
You guys certainly took the ball and ran with it, all I wanted was to ensure I had interpreted the term 'refresh' correctly !!I pretty much agree with the questions and steps put forward, no need for me to repeat by pasting my own methodology. However...Tara/Visakh - I'd say the method of ensuring the test logins are in their pre-restoration state will depend on your specific environments, ie whether you have Windows-only authentication, and whether you have a sizeable number of logins in Test. Visakh - what kind of scrambling/encryption do you use? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-19 : 07:27:46
|
quote:
Originally posted by jaybeeYou guys certainly took the ball and ran with it, all I wanted was to ensure I had interpreted the term 'refresh' correctly !!I pretty much agree with the questions and steps put forward, no need for me to repeat by pasting my own methodology. However...Tara/Visakh - I'd say the method of ensuring the test logins are in their pre-restoration state will depend on your specific environments, ie whether you have Windows-only authentication, and whether you have a sizeable number of logins in Test. Visakh - what kind of scrambling/encryption do you use?
We use windows authentication for all our servers. We've different roles being defined on our dbs with lots of logins under each role.Re. scrambling i dont exactly know what scrambling algorithm they're using. i'm not a dba & dont really take part in refresh process. but will certainly try to find what algorithm they uses and get back to you. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-10-21 : 06:14:00
|
| I'd aslo have a step in there to make sure the internal access control and capacity planning processes were completed (or at least acknowledged) before I'd even think about actioning the request. |
|
|
|
|
Anyone want a crack at this interview question?