The Azure Data Lake Catalog (SQLServerCentral 8/30/2017)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [The Azure Data Lake Catalog]( - [Free eBook: SQL Server Backup and Restore]( - [Automating the Synchronization of RDS SQL Server Agent Jobs in a Multi-AZ Environment]( - [Creating an SSIS project]( (From the SQLServerCentral Blogs) - [Working with the registry from within SQL Server]( (From the SQLServerCentral Blogs) The Voice of the DBA Database Development Made Easy? I ran across [this post on developing database with SSDT](. It has a lot of steps, and reading through it, I find this to make some sense, but I'm not sure I think this is easy. I can see why developers find databases to be a pain to work with. There are a lot of steps in [this post]( to just setup and configure a database project. Databases are fundamentally hard to work with, as the model of maintaining state between changes and ensuring data is not lost can be hard. While the concept is similar to keeping track of configuration files, the scale of data in a database is vast and ever changing. Tooling to manage data that might need to be recovered isn't very practical. I ran across a developer that was trying to automate their database development. They used tooling to deploy a table to a production system. The application connected and data was stored in the table. The developer then dropped a column from a table and deployed this change. It worked, but this was a mistake and this person decided to deploy the previous version of the database, with the additional column restored. The deployment worked, but there wasn't data in the column that had been dropped, and added back. Why not, asked the developer? Many people are of two minds here. One, any tooling or automation should preserve data and allow for rollbacks. In the application world, this makes perfect sense, and even the data our application uses (reference files, configuration files, etc.) are restored if we rollback to a previous version. For the data people, this makes no sense. The data in a column could be of significant size. We often plan for systems to reach millions (or more) rows of data, and trying to save the state of this data before a change isn't practical. Even if we were to store changes items for a few deployments, it's entirely possible that putting the data back wouldn't make sense as related information in other tables might not match up correctly. Consider the case of a financial system and restoring old money values. Who knows what issues would be created? For developers, this highlights one of the things they dislike about databases. They are must manage state transitions across deployments, and rolling back to previous versions isn't often possible. This is one reason that I have often performed a backup before major deployments, and even today, in an automated DevOps process, I'd want to perform a backup if any significant data were being changed or deleted. I've spent a lot of time advocating for DevOps and smoother, modern database development practices for the last few years. I don't want the database to be a hindrance or impediment to change, but I also don't want to compromise the integrity or safety of data. My pitch has always been that our database automation tools at Redgate don't perform any magic. They smooth, and hopefully speed up, the process of making database changes that we've used for decades. They save you time and effort, just as other tools may do, but they can't change the rules of relational database changes. Everyone developing code inside or connecting to a database needs to understand how transactions and data changes work. There are rules and restrictions the protect our data. These mean we need to sometimes plan and consider the consequences of our actions. This should also mean that despite wanting to move faster and make changes, we can't treat data placed in columns as malleable in the way a method in C# can be changed back and forth. We need to account for, and protect, the information stored in our systems. There are patterns that can help you evolve your database from one state to the next, but there isn't any magic that lets you drop and add data storage elements without some preparation for handling the data itself. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( --------------------------------------------------------------- The Voice of the DBA Podcast Listen to the [MP3 Audio]( ( 5.1MB) podcast or subscribe to the feed at [iTunes]( and [Libsyn](. [feed]( The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. ADVERTISEMENT [Foundry]( Using SQL Census to audit SQL Server permissions Redgate have just released SQL Census, a prototype tool that makes auditing SQL Server user access permissions much easier. In this post, Ally Parker shows how it works, tells us what's up next in the tool's development, and explains how you can download it for free. [Try the free prototype]( [Redgate Hub]( Do a lot more with Redgate tools You probably have a favored Redgate tool but if you’ve been using it for a while, you may not be making the most of the latest features. Keep track of features and releases on the new Redgate Hub. [Discover the Redgate Hub]( Featured Contents  [] [The Azure Data Lake Catalog]( Mike McQuillan from [SQLServerCentral.com]() Delve behind the scenes and learn about the catalog used to manage Azure Data Lakes.[More »]( ---------------------------------------------------------------  [] [Free eBook: SQL Server Backup and Restore]( Press Release from [Redgate]() In this free eBook Shawn McGehee offers advice on query tuning, cutting stored procedures, and system process design and implementation for high availability. Discover how to perform backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Redgate's SQL Backup tool.[More »]( ---------------------------------------------------------------  [] [Automating the Synchronization of RDS SQL Server Agent Jobs in a Multi-AZ Environment]( Additional Articles from [SimpleTalk]() Although Azure is the obvious Cloud service to host SQL Server, Amazon Relational Database Service (RDS) for SQL Server is a good choice when your organisation uses AWS. RDS deals with maintenance and monitoring, and supports the use of PowerShell to automate routine tasks. What if a script needs to be triggered by an unscheduled event? Even in this case, RDS can be configured to run scripts to react when something like a failover happens. Laerte Junior shows how easy it is to set up Lambda functions and some PowerShell scripts to automatically synchronise agent jobs after a failover.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Creating an SSIS project]( Kenneth Fisher from [SQLServerCentral Blogs]( Now that SSDT (SQL Server Data Tools) has been installed the next step is to create a project. First, open...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Working with the registry from within SQL Server]( Wayne Sheffield from [SQLServerCentral Blogs]( There is a lot of information within the Windows registry. Sometimes, it would sure be nice to work with the...[More »]( Question of the Day Today's Question (by Steve Jones): I have this R code, setting an American football position list: football <- c("CB", "OLB", "LT", "WR", "SS", "DE", "LG", "HB", "CB", "MLB", "C", "QB", "FS", "DT", "RG", "FB" ,"CB", "SLB", "RT", " ", "CB", "DE", "TE", "WR") dim(football) <- c(4, 6) When I print this, I get these results: [,1] [,2] [,3] [,4] [,5] [,6] [1,] "CB" "SS" "CB" "FS" "CB" "CB" [2,] "OLB" "DE" "MLB" "DT" "SLB" "DE" [3,] "LT" "LG" "C" "RG" "RT" "TE" [4,] "WR" "HB" "QB" "FB" " " "WR" If I just want the offensive line (the xT, xG, C) row, what do I type? Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 2 points in this category: R Language. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT [The Phoenix Project: A Novel about IT, DevOps, and Helping Your Business Win]( tag=redgatsof-20 linkCode=as2 camp=1789 creative=9325 creativeASIN=0988262592) The company's new IT initiative, code named Phoenix Project, is critical to the future of Parts Unlimited, but the project is massively over budget and very late. The CEO wants Bill to report directly to him and fix the mess in ninety days or else Bill's entire department will be outsourced. [Get your copy from Amazon today]( tag=redgatsof-20 linkCode=as2 camp=1789 creative=9325 creativeASIN=0988262592). tag=redgatsof-20 linkCode=as2 camp=1789 creative=9325 creativeASIN=0988262592 Yesterday's Question of the Day Yesterday's Question (by Steve Jones): If I run this code on the right SQL Server platform, what is returned? EXPLAIN SELECT CAST (AVG(YearlyIncome) AS int) AS AverageIncome, CAST(AVG(FIS.SalesAmount) AS int) AS AverageSales, G.StateProvinceName, T.SalesTerritoryGroup FROM dbo.DimGeography AS G JOIN dbo.DimSalesTerritory AS T ON G.SalesTerritoryKey = T.SalesTerritoryKey JOIN dbo.DimCustomer AS C ON G.GeographyKey = C.GeographyKey JOIN dbo.FactInternetSales AS FIS ON C.CustomerKey = FIS.CustomerKey WHERE T.SalesTerritoryGroup IN ('North America', 'Pacific') AND Gender = 'F' GROUP BY G.StateProvinceName, T.SalesTerritoryGroup ORDER BY AVG(YearlyIncome) DESC; GO Answer: This returns the estimated execution plan of the query without running it Explanation: This will return the execution plan for the query without running the query in an Azure SQL Data Warehouse. Ref: EXPLAIN - [click here]( --------------------------------------------------------------- [» 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 2016]( : [SQL Server 2016 - Administration]( [Setting up New Sql Server]( - A company we are developing for have purchased a new Server There will be about 4 users Trying to install SqlServer 2016... [Job reported: Unable to terminate process (reason: Access is denied)]( - Using SQL Server 2016 SP1 running on VM a SQLAgent job is hanging on one step that executes an SSIS... [Strange transaction log activity]( - I'm suddenly seeing unusually large transaction log backups for a small database, and the application team swears that nothing has... [SQL In-Place Upgrade Issue]( - I inherited a SQL 2014 machine and am trying to do an in-place upgrade and when it gets to the... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [Solved - Can I join these tables with a query to obtain the final result?]( - Hi all, I'm wrapping my head around a problem that I'm not being able to solve. Perhaps one of you might... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [Script or Ways to check if SSRS encryption key backed up]( - Hi Experts,      Can anybody help me finding out how to check if the SSRS encryption key have been backup up... [The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\120\COM' directory. System returned errorcode 5.]( - Hi Publisher: SQL 2008 Distributor: upgraded from SQL 2012 to SQL 2014 Subscribers: SQL 2008 / 2012 Current error on transactional replication: The distribution... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [Any tool/query to extract table list from a complex query]( - Hi All, Is there any tool/tsql-query to pull all the tables involved in a sql query. Assuming it is a... [Finding Consecutive Values For a Record]( - I have a table that shows if a record has a value for each month. A zero is given for... [BCP out not unloading all records]( - I have a SQL Server Agent job running nightly that unloads data from tables on SQL Server 2008 R2 using... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [Calculating averages]( - I have the following sql code that gets me Total no of days taken for each workflow step to process:... [Does HIPAA require that test data not have any real data?]( - This is a question that I'd ask in a free form forum, but I couldn't find one. If I've missed... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Query Help]( - Here is the basic schema of table CREATE TABLE Test (ID INT, ProductCode VARCHAR(50), Quantity INT) INSERT INTO Test SELECT 1,'ABC', 1 UNION SELECT... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [PLE]( - Hi All, Appreciate if anybody can help understand why the physical memory is divided by 4 to determine ideal Page Life... [Right Size for LDF physical disk]( - Dear friends, Please, help me to take action, the facts are: Big database 1.5 terabyte database size, recovery model simple, 12 % Pct... [do you have a StripNonNumeric ITVF function?]( - we all know Inline Table Value Functions perform at least a couple of order of magnitude faster than a scalar... --------------------------------------------------------------- [SQL Server 2008]( : [T-SQL (SS2K8)]( [running stored procedure in a loop to get Top level Demand(s) form a Supply]( - i would like to create a stored procedure to find the Top Level Demand for a Supply . I have a... --------------------------------------------------------------- [Programming]( : [Powershell]( [Install SQL Server Named Instance using silent install PowerShell]( - Hi Does anyone works on SQL Server Named install using PowerShell using .ini files? If you have any runnable script please share. Basically... --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [Data flow concept - Thoughts wanted]( - Looking for thoughts on this. I have an application where we receive data from customers via text file. Unfortunately, we... --------------------------------------------------------------- [SQL Server 7,2000]( : [Replication]( [Cleaning up Replication Monitor]( - I have sucessfully removed an old publication and it subscriptions, but I am still seeing references to it in the... 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](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com