A Self-Serve System that Allows Developers to Refresh Databases (Part 2) (SQLServerCentral 2019-05-09)

 SQLServerCentral Newsletter for May 9, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [A Self-Serve System that Allows Developers to Refresh Databases (Part 2)]( - [Power BI and The Matrix: A Challenge]( - [SQL in the City Summits – US & Down Under](e2%80%93-us-down-under) - [From the SQL Server Central Blogs - Execute SSIS Package from PowerShell]( - [From the SQL Server Central Blogs - Is Your Availability Group ‘REALLY’ Ready To Failover?]( The Voice of the DBA  The Death of the GDPR When I first heard of [the Sarbanes-Oxley Act](, I was disheartened. It sounds like another over reaction by politicians to a crisis that would create lots of work for companies. At the time I was working for a software vendor and while we weren't bound by many regulations, we did value our ISO certification. As we went through the SOX audit, it felt a lot like our ISO audit the previous year. In fact, we re-used most of our work, and re-did some, making it compliant with both items.  A few years ago, we started [GDPR]( preparations at Redgate, as well as talking and working with customers. We built some products, like [SQL Provision]( and our [Data Catalog](, with the hope that they would be useful to organizations looking to ensure compliance. We have seen many companies trying to comply, and I'm glad that we can help. I think the GDPR is a step in the right direction for the future of how we manage and deal with data.  I'm also glad that companies are taking the idea of data rights and data privacy more seriously. I think those of us in technology have been lax, and management of many companies even worse. I think I may some some evidence in [this piece about possible lack of enforcement of the GDPR by Ireland](. Not to discuss politics, but I completely understand when a very large company brings a lot of revenue to a country, there will be hesitation in disturbing that relationship.  Regardless of the politics, I did find this quote interesting. A report in 2011 on Facebook's data handling practices had this quote: “We do not consider that reliance on developer adherence to best practice or stated policy in certain cases is sufficient to ensure security of user data...” This didn't prompt Facebook to make changes to process, and Ireland has not pressured them to do so, even with the GDPR taking effect last year. I'm hoping this isn't the death of the GDPR as a set of regulations.  I completely agree with the sentiment of that quote. We can't trust that developers will adhere to best practices, or that companies will protect data. Sometimes this is ignorance, sometimes human error, and other times willful disregard for rules and regulations. We do need some sort of enforcement of the data handling practices that we as a society want to see in place. We certainly have to decide what practices to codify in our framework and we need some enforcement.  I don't know that very large tech companies will get fined or forced to better handle data, but for many of us that work in smaller organizations, we don't have the clout or impact of a multi-billion dollar revenue organization. We might see fines that can very much hurt our businesses. We ought to be ensuring we not only follow best practices, but we build data security in by design and default. It's cheaper and easier to do so, and it ensures we don't fail to go back and fix things later.  It's also something each of us, as data professionals, ought to take pride in doing. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [A Self-Serve System that Allows Developers to Refresh Databases (Part 2)]( bkubicek from SQLServerCentral Dive deep into the details of part 2 in this series of creating a solution so Developers can start a restore of Production to test. [Power BI and The Matrix: A Challenge]( Additional Articles from SimpleTalk The Matrix is one of the many built-in visualizations found in Power BI. While the Matrix is easy to populate, filtering it is not so straightforward. In this article, Dennes Torres explains how to add a slicer that does the job. e2%80%93-us-down-under [SQL in the City Summits – US & Down Under](e2%80%93-us-down-under) Press Release from Redgate Redgate are inviting senior data professionals to attend one of the upcoming SQL in the City Summit events taking place in May and June. If you’re interested in learning how your business can benefit from implementing Compliant Database DevOps this event is for you. Find out who’s presenting and register for a Summit near you today. From the SQL Server Central Blogs - [Execute SSIS Package from PowerShell]( Aaron Nelson from SQLServerCentral My friend Andy Leonard has written an excellent blog series on getting SSIS up & running in a Docker container. I would never have even tried to go down... From the SQL Server Central Blogs - [Is Your Availability Group ‘REALLY’ Ready To Failover?]( david.fowler 42596 from SQLServerCentral You know what it’s like, you need to fail your AG over but is it safe to fail over? Perhaps you’ve clicked on ‘failover’ for the AG and there’s...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Strange Sorting I have a table full of orders and another with line items. I want to get a list of the orders and their totals, but grouped. I run this query: SELECT DATENAME(mm,oh.OrderDate) AS MonthName , SUM(oli.qty * oli.unitcost) AS TotalCost FROM dbo.OrderHeader AS oh INNER JOIN dbo.OrderLineItem AS oli ON oli.OrderID = oh.OrderKey GROUP BY DATENAME(mm,oh.OrderDate) ORDER BY CAST(DATENAME(mm,oh.OrderDate) + ' 1900' AS DATETIME) What do I get? Think you know the answer? [Click here]( and find out if you are right.  [Redgate Webinars](    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Getting the time How do I get the system time in R? Answer: No answer set Explanation: Sys.time() will return the system time Ref: Sys.time() - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- [Can i create a table from my parameters?]( - If yes, any links to instructions would be great. I am often times asked to search for parts, and their associated data (price, seller, buyer, size, etc. in our database. note: I use SSMS, but then I load the reports into SSRS/SharePoint for the end user. I am given a list of, for example, 100 […] [Report on the entire SQL Server environment for a newcomer]( - Is anyone aware of a script/SP somewhere that would produce in one big result-set or report the overall information on a given SQL Server? I mean all database sizes, users, security, server configurations, replication, etc. etc. , etc., and EVERYTHING that a newly hired DBA would be interested to see and learn about each particular […] [Question on AWS DMS]( - Good morning Experts, Primary keys are mandatory for transactional replication. If table has no primary key, it will not be replicated. How is it in AWS DMS? Are primary keys mandatory and If table has no primary key, it will not be replicated? [help to fix hash procedure]( - hi, i create a procedure to get hash code from entire table's contents. it works fine in 99% , but in some cases it returns the same number for different data. in this code the ord value is swapped for two records, bat the hash is the same. is there a way to fix the […] [Code snippets removing line spaces]( - I just posted a topic in the SQL 2012 T-SQL forum group and the board removed all the line spaces between sections of code, making (to my eyes) what I posted completely unreadable. It's hard to tell one line from another, even though when I go to edit the post, the code box shows my […] [Converting Substring to DateTime - extra eyes please]( - I'm experimenting with a backup / purge backup solution for our non-prod environment. Every 3 hours, a copy-only backup of a prod database gets taken and pushed down to a non-prod NAS share. I'm only supposed to keep 4 backups (12 hours worth), so I wrote a script to purge them. My original code looked […] [Availability groups and dedicated NICs]( - Hi there,  Looking for some advice here:  I currently have 3 SQL servers in a single AG with a single network card on each server. During times of heavy network usage, we are sometimes seeing failovers to a secondary server. After some research, I see that the way round this is to have […] [Delimited Identifiers in code interpreted as BBCode]( - It seems that if you delimit identifier a single value in brackets using inline code markup (surround it with backticks) the value is interpreted as bbcode markup instead. In all the below examples I use [ U S E R ]. For example, the value in a code sample And in inline code: . It […] [Help in SQL server]( - Hello everyone, I am New in this Forum and in SQL Server, i will glad for your Help for a little quastion: I want to create a job, that execute Backup for five database, every day in certain time. How do i create the jobs? thanks! [Connect to SQL Database in Visual studio 2017 via Active directory]( - Hello! I am authenticating my SQL database via active directory. Now when I am trying to connect to the database via Visual studio then the connection is failing because of login in windows authentication. The problem is I am using laptop given by my employer to work for client. So the windows authentication fails since […] [A set has been encountered that cannot contain calculated members]( - Hi Guys, I have a cube and works for more then 4 years. I get a error when I trying to browse in the Cube: A set has been encountered that cannot contain calculated members The following MDX Im running: SELECT NON EMPTY { [Measures].[% hours] } ON COLUMNS FROM ( SELECT ( { [Bookperiod].[Bookperiod].[All […] [Status Update 6 May 2019]( - Mostly unchanged from previous days. The fixes logged today are mostly backend, infrastructure items that should improve some performance. We've had a few timeout issues that I hope will not re-occur. Two resources out this week, but another developer should be coming online. Perf and Infrastructure - 38 items backlogged, 1 in progress, 3 fixes […] [OLE DB Destination Editor VS OLE DB connection VS ADO NET]( - I am having trouble displaying table as set out in the book "Microsoft SQL Server 2012 Integration Services: An Expert Cookbook" page:96. The Book states i must return to OLE DB Source Editor. In the OLE DB Source Editor, i am not able to view the table that has bee created. I can view the […] [DAX query on filter in Power BI]( - Hi I have created a DAX formula that counts an attribute based on a filter. Is it possible to have a filter coming from two different dimension groups.  In the example below, I want to count all Table A field A records where the TableA.field3 = 1 and TableB.Field2 =2 .  CALCULATE(COUNT('TableA'[FieldA]), Filter('TableA', [Field3] […] [Calculate the sum of a value where a specific column has a lower value (DAX)]( - I have an SSAS tabular Model with a table containing ledger data and another table containing account information. On this account filtering table I have a field called Typeorder. The Typeorders are as follows COA_Type ,Typeorder Revenue ,1 COGS ,2 Operating Expense ,3 NonOperating Expense, 4 Income Tax ,5 Asset ,6 Liability ,7 Equity, 8 […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -