Querying Master Database DMVs in Azure SQL Database (2019-12-12)

 SQLServerCentral Newsletter for December 12, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Querying Master Database DMVs in Azure SQL Database]( - [From Source Control to Database using SQL Compare Command Line]( - [How Does Accelerated Database Recovery Work?]( - [From the SQL Server Central Blogs - ASF 027: Mikael Wedham interview]( - [From the SQL Server Central Blogs - Bad Idea Cowboy Hat: BEGIN TRANSACTION; GO 1000;]( Question of the Day - [The Mangled Outer Join]( [Redgate University]( The Voice of the DBA  THE Database Engine This editorial was originally published on 14 Nov, 2016. It is being republished as Steve is traveling today. Is there a "best" database engine? Is there one database system that you can use for all of your needs? The question is addressed in [a long, but captivating, piece at O'Reilly](. The author talks about finding an HTAP database, which stands for Hybrid Transaction/Analytical Processing. In short, the ultimate database that can handle your OLTP, ODS, BI and analytical needs, while also addressing all the other types of features most of us would want. Tl;dr No. The piece is worth a read in that it examines the requirements and features needed to achieve a high performing database system. This system doesn't look at RDBMS over NoSQL or NewSQL systems, and does a fair job of evaluating why trade-offs are needed by many of the types of databases that have been used by companies in an effort to find a better platform. It's almost a bit of a history of database platforms. What I like about this piece is that they look at the differences between startup companies and enterprises. The former is bound by legacy constraints, but more importantly, their failures are just inherently less impactful. For enterprises, however, there is a certain inertia, not to mention existing skill sets that make adoption of new technologies more difficult. There's also that pesky transaction requirement. Some domains might not need these, but in some situations transactions are really important, and as the article states, "Transaction support was useful in certain cases in spite of its overhead." Throughout the piece, the author seems to move back and forth in examining the advantages of moving to one platform over the other. We have certainly spent time trying to build large monolithic databases, then lots of effort to move data around to other systems. Who remembers the ODS/large data warehouse trend, which then fractured into further movement to "data marts" before it seems many people have moved back to a large single DW against which analysts can run queries?  Movement of data, gluing complex ETL (or ELT) pipelines together requires a lot of resources, and often these can be somewhat brittle, prone to regular, albeit small, failures. The nirvana is a single database platform, but I'm not sure that's possible. All of the requirements we place on databases require some trade-offs. I don't think anyone has found a way around the CAP Theorem, which will inherently limit the ways in which we can meet many requirements. However, I think we can "fake" the success of our platforms at times, especially since our end users don't care how we meet their needs. Just that we meet them. The Azure platform seems to be moving in the right direction, in my opinion. There is a separation of storage from compute, which in database terms means a separation of data storage (the storage engine) from the query engine (where compute resources are needed). Certainly this isn't complete or clean, but already in Azure SQL Data Warehouse, I can scale up or down my compute needs separately from the storage for my data. The Data Lake allows me to store disparate types of data, without specifying a structure, and query the bits from multiple applications. Even the ability to instantly "copy" a database in Azure SQL Database could be used to "fake" the instant replication of data to another system. Granted, there are issues with clients constantly querying databases and maintaining up-time (or switching clients to a new copy), with reconciling changing data across many clients, and certainly the management of all this data. However, as we search for ways to build the ultimate OLTP/ODS/BI/Machine Learning database platform, I'd like to remind you we don't need to build a single engine. We just need to make our clients to think we did by providing a data source that appears to handle all their needs. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums]( [Redgate Database Devops](   Featured Contents [Querying Master Database DMVs in Azure SQL Database]( Steve Jones - SSC Editor from SQLServerCentral Learn how you can query information in the logical server's master database from an Azure SQL Database. [From Source Control to Database using SQL Compare Command Line]( Additional Articles from Redgate Giorgi Abashidze explains how his team use SQL Compare Command line to automate database deployments for their customers, without having access to the real staging or production databases, merely by using our development database contained under TFS Source Control. [How Does Accelerated Database Recovery Work?]( Additional Articles from SimpleTalk Accelerated Database Recovery is new with SQL Server 2019 and Azure SQL Database and used to decrease the time for rolling back large operations and database recovery. In this article, Forrest McDaniel explains how it works. From the SQL Server Central Blogs - [ASF 027: Mikael Wedham interview]( KamilN78 from SQL Player Blog Introduction Mikael Wedham is the first Microsoft Certified Master on SQL Server 2008 in Sweden. He has worked as a developer and database administrator since 1993 and in ’97,... From the SQL Server Central Blogs - [Bad Idea Cowboy Hat: BEGIN TRANSACTION; GO 1000;]( Kenneth.Fisher from SQLStudies One of my favorite presentations is Revenge the SQL by Rob Volk (blog|twitter). It’s a demonstration of a lot of ... Continue reading   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Mangled Outer Join I have these two tables: CREATE TABLE Student (StudentKey INT IDENTITY(1,1) NOT NULL CONSTRAINT StudentPK PRIMARY KEY , StudentName VARCHAR(10) ) GO INSERT dbo.Student (StudentName) VALUES ('Delaney'), ('Kyle'), ('Kendall') GO CREATE TABLE Schedule (ScheduleKey INT IDENTITY(1,1) NOT NULL CONSTRAINT SchedulePK PRIMARY KEY , CourseName VARCHAR(50) , StudentKey INT ) GO INSERT dbo.Schedule (CourseName, StudentKey) VALUES ('Math', 1), ('Math', 2), ('English', 2) I want to get a list of the students that aren't in any courses. I run this code: SELECT s2.StudentKey , s2.StudentName , s.CourseName FROM dbo.Student AS s2 LEFT OUTER JOIN dbo.Schedule s ON s.studentkey = s2.StudentKey AND s.StudentKey IS null What should I do differently? 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) Making a Matrix I have this data frame: > passing.2019 Player Yards TD INT 1 Rivers 2609 12 7 2 Brady 2535 14 5 3 Wilson 2505 22 1 4 Stafford 2499 19 5 5 Rodgers 2585 17 2 I want to convert this to a matrix. How do I do this in R? Answer: > as.matrix(passing.2019) Explanation: The as.matrix() function is used to convert the data frame. > as.matrix(passing.2019) Player Yards TD INT [1,] "Rivers" "2609" "12" "7" [2,] "Brady" "2535" "14" "5" [3,] "Wilson" "2505" "22" "1" [4,] "Stafford" "2499" "19" "5" [5,] "Rodgers" "2585" "17" "2" Ref: as.matrix() - [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 [Always on in standard and enterprise edition]( - Hello everyone! I would glad to know, what is the difference in Always on between Standard and Enterprise edition? And also there is something that change in 2012 and above? Thanks! [Migrating DB encrypted by master key]( - I'm migrating DBs from a 2008 R2 instance onto a 2017 instance. I have one DB that is encrypted by the master key, how do I migrate this successfully across to a new instance? [Log Shipping - What has changed]( - We currently have a situation with a vendor where they will only give us access to our data via 5 minute log ship files that we import into our read only db. this is fine for most queries, but we need a way to get details about what has changed in each period in the […] SQL Server 2017 - Development [Insert in batches using select coalesce option]( - Hi, I am trying to insert multiple tables data into one table using Insert in batches and with select coalesce. I am getting Ambiguos column (id) error. Can some one please help me to resolve the issue. Thank You. ERROR MESSAGE: Msg 209, Level 16, State 1, Line 26 Ambiguous column name 'id'. Line:26 - […] [Fastest Way to Calculate Total Number of records inserted per day]( - Hi, I need to show a number of records per day in the dashboard of the web application if the user reload the page the count will automatically be updated. Initially, we don't have any problem as the records grow to the millions of records the query execution takes slow. The following is the query […] [Fastest way to query Millions of records with Pagination and Total Record Count]( - HI, We had a table that contains a huge volume of data inserted per day it contains millions of rows. Currently, we had 2 million records in staging but once we hit the production the data will be easily more than 10 - 20 million rows Previously we had a pagination query that took more […] SQL Server 2016 - Administration [changing db owner with always on replication (to fix DB properties window)]( - SQL Server 2016 standard edition, with primary databases and one other server set up as secondary failover with always on availability group. There were some issues over the weekend with an application that accesses the databases on this server, unbeknownst to me, they decided just to reboot the database server, which seemed to clear things […] [What\'s the best way to keep a record of all records inserted via an SP call?]( - Hey guys, so I may be overthinking this but basically, I have a bunch of stored procedures which I want to begin keeping historical track of. I want to know which records were updated by which SP & when, and I'm just looking for the best way to do this. Is there any built in […] [Impact value about missing indexes]( - is there relation between Impact from sys.dm_db_missing_index_group_stats and Impact from cache plan? Thanks for all.  [I can't Uninstall SQL Server 2016]( - Hi, I have SQL Server 2016 installed in my machine but I can't uninstall it, it doesn't appear on my Control Panel, I looked again using CMD wmic, product get name, it also doesn't appear. But I can see it, SQL Server on the services and SQL Server Configuration Management. I also tried using CMD […] SQL Server 2016 - Development and T-SQL [BCP utility]( - Hi All I am trying to export some data from a SQL server database to a CSV file. As I need to do this for around hundreds of records, I am using a cursor to loop through and creating dynamic file names to write individual files. This seems to be working ok. Some of the […] Administration - SQL Server 2014 [Trouble connecting in single user mode]( - I have an SQL server which I need to get admin access to. Logged on as administrator to the server I attempt to start in single user (-m) mode. However in this case, instead of dropping into single user (as on other servers in this project), I get a screen full of continuous login failures […] Development - SQL Server 2014 [How to get information about an AG group using powershell]( - Hi Experts, Does anyone has a powershell script which displays below information: for a given AG listener name or AG group name, I need to get Listnener name, AG name, replicas , replica role desc , Can we get this information using pure powershell which reads registry values or do we need to execute a […] SQL 2012 - General [SQL Monitoring]( - Hi All, I have SQL 2012 instance on a vm server which I am testing with Red Gate Monitoring Tool installed on my machine and my machine machine is set a base monitor for testing purpose. Problem I am facing is that it only monitor while I am logged on to the machine. If I […] Strategies and Ideas [Tracking History in Fact Table]( - Hi, I was hoping somebody would be able to offer advice in relation to tracking history in a fact table, in particular whether #2 below would be a viable option? I would have thought that #1 would be bad for performance given the volume of records created through history so maybe not best practice?. If […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -