November 2, 2018 at 6:49 am
Forum,
In our current environment where we have 2 domains 1-PROD 2-DEV we usually restore previous night 6pm backup daily to help the Dev and QA folks to perform validation and fixes , but as its last night data they might be missing some transactions that came in around midnight , so they wanted to create a linked server and we created a separate DB with access to synonyms . We admins were able to access the DB but QA/DEV/BA were not able to run queries joining multiple tables because of CLR not enabled . Is it better to create views instead of granting via synonyms .? Can we create views for all tables in a DB and how can we create views from newly added tables dynamically.
What are the best practices to implement in this current scenario .
November 2, 2018 at 7:10 am
So let me get this straight, you are basically giving the developers and testers direct access to the production system via linked server. You do realize that this could have significant impact on the production server as queries to the production tables will require that all the data be pulled from production to the dev/qa system before any filtering of data occurs.
For development and testing just how critical are those daily transactions that are done at midnight for development and testing?
November 2, 2018 at 7:24 am
It is mostly financial information and they need it matched to exact $ amount , it is critical . BTW forgot to mention there are 3 nodes (SQL Server 2014 Always on )and the 3rd node is read only and we created linked server to this node .
November 2, 2018 at 8:21 am
... were not able to run queries joining multiple tables because of CLR not enabled
What does CLR have to do with running queries joining multiple tables?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 2, 2018 at 2:21 pm
objects exposing columns with clr types are not allowed in distributed queries, we have linked servers to join tables so whenever we perform that operation or try running a query using synonyms we run into this issue . Again coming back to my original post Synonymns or Views which option is best when used in a linked server and always on scenario ?
November 2, 2018 at 2:32 pm
I guess I am still stuck on why the devs and QA need to test on the most current data possible. What is wrong with testing on day old or older data?
November 2, 2018 at 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 .
November 2, 2018 at 4:27 pm
JSB_89 - Friday, November 2, 2018 4:24 PMThe 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 .
Doesn't explain why it has to be the latest. If that is the requirement, figure out how to get the data from the midnight processing to the dev/qa environment. Perhaps a copy only backup taken after that processing and restore it to the dev/qa environment.
November 2, 2018 at 5:39 pm
Well like I mentioned in the original post, we have 2 domains and we perform a backup in prod and copy to dev environment and then perform the restore.so a 600GB DB takes 2 hrs to backup and copying to different env takes 2 hrs min and restore takes 1.5 hrs so 6hrs . Some agents in the field are already working by then and will have to compare what happened to the ticket over the process from start to finish. Different time zones
Hope this helps ..let me know if you have anymore questions.
November 2, 2018 at 5:41 pm
Are you using differential backups, or full?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 2, 2018 at 5:47 pm
Full backups
November 2, 2018 at 5:49 pm
Diffs could be much faster. Might be worth trying.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 2, 2018 at 5:53 pm
That’s a long process to get approval from IT manager and jump thru all the hoops.
That’s for a later day
November 3, 2018 at 12:12 pm
JSB_89 - Friday, November 2, 2018 4:24 PMThe 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2018 at 6:42 pm
Jeff Moden - Saturday, November 3, 2018 12:12 PMJSB_89 - Friday, November 2, 2018 4:24 PMThe 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
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply