Distributed Computing Principles and SQL-on-Hadoop Systems (2019-09-06)

 SQLServerCentral Newsletter for September 6, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Distributed Computing Principles and SQL-on-Hadoop Systems]( - [Managing Data Masking Rules in Larger Teams using Source Control]( - [How to Stop a SSIS Package Execution from Code]( - [From the SQL Server Central Blogs - Letter To My Father]( - [From the SQL Server Central Blogs - My Organizer's Notes and Goodbye after #SQLSatBR 2019]( Question of the Day - [Transient Synonyms]( [Redgate University]( The Voice of the DBA  Turn Out the Lights This editorial was originally published on Jan 18, 2016. It is being re-run as Steve is on holiday. I suppose like most parents, and my parents before me, I spend a lot of time telling my kids to “turn out the lights”. What is it about kids and leaving lights, TVs, game consoles, and other devices perpetually running? I’ve tried explaining the cost to them, but I think until I find a way to make chargebacks against their allowances, the logical argument isn’t likely to work. It isn’t that they (or me, way back when) don’t care; they just are thinking about other things. I console myself that at least the LED lights I've installed mean I’m not spending as much as used to! What does that have to do with IT and databases? We’ve been a lot like my kids – we run hardware without much if any thought to the cost. We disable the BIOS settings that might throttle the CPU down because we (think) we need max power all the time. We worry about maxing out the capacity, so we over provision everything and are close to gleeful when we see a production server running at 5% utilization, knowing that we have room to handle the spikes that will surely come. We’ve seen CIO’s push back obliquely on this via virtual machines (VM). VMs have a lot of advantages, but in terms of reducing some of the waste due to over provisioning, they are reasonably effective. VMs are magic – more or less – to us, and this means that we still don’t have to learn to turn out the lights. To be fair, it makes sense to drive that behavior down the stack to the hypervisor, but there is a difference between designing for less usage and using a hypervisor to mask it. Chargebacks are another technique, pushing the cost back down to the department that is generating the demand (or that doesn’t care to optimize). Short of chargebacks, we don’t think about costs a lot. Hardware is the ‘cost of doing business’ and it’s a capital expense, something everyone is used to and accepts. As we move to the cloud and the charge by the minute/gigabyte/service call/virtual machine model, we move to the land of operating expenses (OPEX), and every CIO out there actively manages those. I think we’re going to see a lot more thought going into trying to use services that charge per use versus provisioning virtual machines that run all the time. Learning to scale up and scale down key resources effectively is going to matter. I can see the time coming when the CIO has the utilization chart up on a screen in a meeting saying, “why did it take us 30 minutes to scale down after the sale ended?”. Spend a few minutes thinking about how your business might change if everything was an operating expense. Would it make it easier to get someone to agree to mark a table as compressed or get a miserably inefficient proc fixed? Will archiving data suddenly become interesting? Will you someday soon hear the CIO shouting down the hall to turn off the lights? Andy Warren [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Distributed Computing Principles and SQL-on-Hadoop Systems]( Frank Banin from SQLServerCentral.com A look at SQL-On-Hadoop systems like PolyBase, Hive, Spark SQL in the context Distributed Computing Principles and new Big Data system design approach like the Lambda Architecture. [Managing Data Masking Rules in Larger Teams using Source Control]( Additional Articles from Redgate What if you have several people in the team who are responsible for data security across your databases, and they need to work together to develop and maintain the data masking configurations, which must then be applied consistently as part of an automated provisioning process? How should they do it? The solution turns out to be simple: source control. [How to Stop a SSIS Package Execution from Code]( Steve Jones - SSC Editor from SQLServerCentral In this tip we look at a way to stop the execution of a SSIS data flow task that is running by executing an outside process to stop the data flow immediately. From the SQL Server Central Blogs - [Letter To My Father]( SQLFlipFlopsDBA from SQLFlipFlopsDBA We spread my Fathers ashes over the past weekend. It was one of the hardest things... From the SQL Server Central Blogs - [My Organizer's Notes and Goodbye after #SQLSatBR 2019]( willassaf@gmail.com from SQL Tact Some 2019 tips for other SQLSat organizers from my last year as lead organizer of SQLSatBR. I hope as a fellow organizer, you can pull some ideas and inspiration...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Transient Synonyms On SQL Server 2017, I run this code in a batch to create a temp table: CREATE TABLE #songs ( songid INT, title VARCHAR(200), releasedate DATE, genre VARCHAR(100)); I then do this: CREATE SYNONYM SongStaging FOR #songs Now I drop the temp table. DROP TABLE #songs My ETL process will recreate the temp table using the code above when it runs. This stored procedure is then called: CREATE PROCEDURE dbo.LoadSongs AS INSERT Songs ( songid, title, releasedate, genre ) SELECT songid, title, releasedate, genre FROM dbo.StagingSongs WHERE title NOT IN (SELECT title FROM Songs) GO I then run the ETL process and the stored proc, what happens during this flow? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Restoring Expired Certificates I have a certificate that is used for TDE in my SalesSQL instance. This certificate expired last month. I now need to restore a backup of this certificate on a different instance for a DR test. What happens when I run the CREATE CERTIFICATE FROM FILE command on the new instance using a backup of the expired certificate? Answer: The certificate is created with a warning about the expired certificate Explanation: The certificate will be created, but a warning is returned that the certificate is expired. Ref: CREATE CERTIFICATE - [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](. --------------------------------------------------------------- SQL Server 2017 - Administration [business intelligence development studio(Bids)]( - Hello, I have a SQL 2017 and I want to use business intelligence development studio(Bids), I dont see the App on the start menu. Integration service are exist. Tanks! [SQL Server Upgrade]( - I have old physical SQL Server which is less than 2 yrs old, so we are planning to upgrade to SQL 2017/2019. So thinking of using the same old physical SQL Server for the upgrade. Would need to load the new windows version and SQL Server. Do you see any reason that it should not […] SQL Server 2017 - Development [SSAS - Aggregation issue: “N/A” members be excluded from Excel “Grand Total "]( - We’ve build a Finance OLAP Cube in SSAS which is mostly used/browsed by Accountants. Due to a “self-service” policy that is followed, users are in the meantime required to make use of Excel (with a data connection to the cube) as their reporting and/or analysis tool. One of the dimensions, the Accounts dimension, contains the […] [SSIS - Adding Columns to existing table and load data]( - I am working in SSIS (Sql server version 2012) , I have a requirement to add two new columns to a destination table which already has five years of data. How can i add these two new columns and bring data to the destination table which already has 5 years of data. Thanks [Alert me Export table data to .MSG file using Stored Procedures or any plugin ?]( - Is it possible with a stored procedure to write/export/output the data as .msg outlook file using existing table data? [SQL SEREVR QUERY]( - I HAVE A TABLE called TBLGENDER LIKE BELOW IN THIS TABLE 2 people getting 8000,2 people getting 5500,2 people getting 3500 AND 4 people getting 5000 ID NAME GENDER SALARY CITY 1 SHIVA MALE 8000 LONDON 2 MAMATHA FEMALE 6000 SYDNEY 3 NIHAL MALE 5000 DELHI 4 ROHAN MALE 3500 DELHI 5 PANDA FEMALE 3500 […] SQL Server 2016 - Development and T-SQL [Using log4net in a dll which is called via CLR function]( - Hello I am refactoring older code which is part of an SQL Server CLR Function call. The code has a horrible local logger implemented, I would like to use a standard logger such as log4net. What are the pros and cons of such an approach? Is it feasible? Has anyone done something similar before? Best […] [Issue with max() and aggregate, missunderstanding]( - Hi all, this is my first post here and I've been looking and trying to understand this for hours the various ways people have tried to get around this issue. My trouble is as follows. I have a database with three tables linked together by 'DocumentNo'. Two main linked tables which output all rows with […] [Help with Query Results and Collation]( - I should know the answer to these questions, but I'm having trouble with a couple of my queries. First, my goal here is to compare data from a table in a source database with data from a table in another database to see whether there are any differences. Here's a simple example: DECLARE @TableA TABLE […] Administration - SQL Server 2014 [SQL 2014 (or any other versions) Standard - ~Embedded Licensing~]( - Anyone have any insights on how this licensing works and what the "Use Rights" are? I can't seem to find much about it. Most I gathered (and I could be wrong) is it is dedicated solely to the application that uses it. No other databases can be on it for any other reasons and no […] SQL 2012 - General [MEMORY CONSUMPTION SQL 2012]( - hi all, as you can see in attachment sql take 122 giga memory. and in report consmtion i see only 94 taken by sql . ( this report generated from sql) and idea where 30 giga is hidden :)? and how i can seem them ? thanks alot sharon  [SSIS adding new columns to existing table]( - Hello All, I am working in SSIS (Sql server version 2012) , I have a requirement to add two new columns to a destination table which already has five years of data. How can i add these two new columns and bring data to the destination table which already has 5 years of data. Thanks [How can I convert %5B and %5D to [ and ] in a string](5b-and-%5d-to-and-in-a-string) - Hi, I have a string nvarchar(255) and I am try to convert the ASCII codes in it and also extract part of the string. Here is an example of a value: urn:ADL:CC_LB_SF_EN_D_C_%5BLB_LS_TCD%5D_%5BLB_LS_CotCF%5D_28_1 This is what I would like to convert it to: urn:ADL:CC_LB_SF_EN_D_C_[LB_LS_TCD]_[LB_LS_CotCF]_28_1 Then I would like to select what is between urn:ADL: and the […] SSRS 2014 [Simple Cascade Parameters not updating]( - I have a parameter called SearchCriteria with four Available Values R, W, M, or A for Range of Dates, Last Week, Last Month, or Last Year respectively. I have another parameter to include a statistics Table at the End of the Report. For all but the Range of dates I want the stats table to […] SQLServerCentral.com Website Issues [A while ago I stopped receiving my daily and weekly newsletters.]( - A while ago I stopped receiving my daily and weekly newsletters. I am not sure why. Today as a test I unchecked the selections, saved the unchecked values and rechecked the selections. I've done that before and it didn't help. Can you please let me know why I am no longer receiving these newsletters. It's […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -