Analyze Azure Cosmos DB data using Synapse Link and Power BI (2021-01-12)

 SQLServerCentral Newsletter for January 12, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Analyze Azure Cosmos DB data using Synapse Link and Power BI]( - [Database version control: Getting started with Flyway]( - [How to Check SQL Server Version When Service is Not Running]( - [From the SQL Server Central Blogs - Saving the Day from Delay: Recap & Series Kick-Off]( - [From the SQL Server Central Blogs - Importance of data governance in the Covid era]( Question of the Day - [Tracking Datetime2 storage]( The Voice of the DBA  Daily Coping Tip Look for the good in others and notice their strengths I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Reassess Your Configuration It's the start of a new year, and while it might feel like COVID-19 has us stuck back in March 2020, I hope that will start to fade as we move into the new year. Certainly our employers want to move forward, and I suspect that there is some hope that organizations will start to thrive in 2021. I saw a post from Kenneth Fisher recently that talked about [an end of year, new year checklist](. It's a basic set of things that you might do as a type of review and cleanup of some of the instances and databases that you manage. Checking things like end of year processes and performing some review of potential items that might no longer be needed. In the past, I didn't do this type of work at the end of the year, or even that often, but when there were slow times, I did have a list of things to periodically review. We had canned queries and reports to help us evaluate resources. Many were automated to ensure the data about systems was available if I had time. One area that I was check once or twice a year was the configuration, trying to determine if any hardware had changed, the workload had altered, or there were proactive configuration changes we could make. This might include removing unused security logins or turning off services that weren't being used. Certainly I was always cognizant of the load that managing lots of databases can have on a DBA. Every additional database is a potential customer that can call or file a ticket. I couldn't often remove any, but I did find ways to remove unused objects and archive data at times. Reducing resource usage often pays itself back over time, especially when the need for more storage arises, which was more of a battle than it needed to be. I might add one thing to Ken's list. I would ensure that you should check on cloud resources that might not be used. One thing I see too often is that cloud resources are easy to spin up and leave up, even if they become unused. Finding a few of these might earn you some points with the people paying the bills. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Analyze Azure Cosmos DB data using Synapse Link and Power BI]( Sucharita Das from SQLServerCentral Introduction In my last article, Using Azure Synapse Link for Azure Cosmos DB , I discussed creating the Synapse Link to query data present in Azure Cosmos DB from my Synapse workspace. Here, I will create a new database in the serverless SQL pool and create views based on the Cosmos DB JSON files. Then, I will […] [Database version control: Getting started with Flyway]( Steve Jones - SSC Editor from SimpleTalk Flyway is a multi-platform, cross-database version control tool. Carlos Robles explains Flyway’s history and shows how it works [How to Check SQL Server Version When Service is Not Running]( Additional Articles from MSSQLTips.com In this article we look at several ways to find the version of SQL Server that is installed when the SQL Server service is not running. From the SQL Server Central Blogs - [Saving the Day from Delay: Recap & Series Kick-Off]( Steve Hughes from DataOnWheels Ironically, I’m a few months behind on a recap for my presentation on building and using a sustainable, dynamic date query in M code – Saving the Day from... From the SQL Server Central Blogs - [Importance of data governance in the Covid era]( Arjun Sivadasan from SQL Roadie One common theme that stood out last year is the oversupply of data to our fingertips. In my line of work as a data professional, I produce, consume and...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Tracking Datetime2 storage How much storage can a datetime2 variable require? 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) CLOSEing a cursor What does the CLOSE command do to a cursor? Answer: Releases all locks but preserves the structure to be opened again Explanation: The CLOSE command releases all locks and resources, but preserves the data structure. The cursor can be reopened and the data read again. Ref: CLOSE - [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 [SQL Service "services" recovery option]( - Good day all. Quick question. Has anyone, under Services > Recovery for SQL Server, set the following and does anyone have any issues or recommendations. I personally have never set this for SQL Service (or any service) and am wondering if any issues.  [Visual Studio 2017 Community Edition license expired error]( - Hello experts, I've run into an issue that many others on the internet seem to report: Although it is free, VS 2017 Community Edition starts presenting a license expired error after 30 days. The main workaround has been to sign into a Microsoft account. However, that workaround doesn't work for computers that are not allowed […] [824 errors]( - Hi All, Today we are seeing 824 errors on one of the database. When I ran checkdb I am seeing below errors. Note: The database hosted on a 3rd part storage appliance called Actifio. Use master Go DBCC CHECKDB (CDP) WITH ALL_ERRORMSGS, NO_INFOMSGS; GO Msg 1823, Level 16, State 6, Line 3A database snapshot cannot […] [Continuous replication from SQL Server to Google's BigQuery]( - Hello everyone, I have a need for enabling a continuous flow of data from a SQL Server production server to the Google's BigQuery. There are a lot of commercial tools for such purpose and most of them use CDC-based methods for propagating changes, examples: Striim ( StitchData ( Has anyone had a proven experience that […] [Rebuilding indexes with Maxdop on AGs]( - I was curious if anyone else has ran into this or has an explanation. We cut over to AGs about a year ago and have noticed a weird behavior when rebuilding or adding indexes. Every time we use a MAXDOP greater than 2, the server bogs down causing a lot of queueing and preventing transactions […] [Unused indexes]( - Hi all, I used such script for finding unused indexes in my DB: Hi all, I used such script for finding unused indexes in my DB: SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name ,IX.name AS Index_Name --,IX.type_desc Index_Type ,SUM(PS.[used_page_count]) * 8 IndexSizeKB ,IXUS.user_seeks AS NumOfSeeks ,IXUS.user_scans AS NumOfScans ,IXUS.user_lookups AS NumOfLookups ,IXUS.user_updates AS NumOfUpdates ,IXUS.last_system_update ,IXUS.last_user_update AS LastUpdate ,IXUS.last_user_seek […] SQL Server 2016 - Development and T-SQL [Update table]( - DECLARE @Name varchar(80) DECLARE @InsuranceReminderDate datetime DECLARE @body varchar(150) DECLARE @Email varchar(100) DECLARE Messaging CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT distinct C.Name,I.InsuranceReminderDate,C.Email from [dbo].[Customer] C inner join [dbo].[Insurance] I on C.ID=I.ID where convert(date,[I.InsuranceReminderDate]) = CONVERT(date, getdate()) I.MsgSent<>1 OPEN Messaging; WHILE 1=1 BEGIN FETCH NEXT FROM Messaging INTO @Name,@InsuranceReminderDate,@Email; IF @@FETCH_STATUS = -1 BREAK; SET @body […] [How to filter out the data?]( - Hello All, Need your help to filter out the data, please. I couldn't find a better term to describe, but hope the example below will instantly clarify what I need: 1. There is a range of simple physical barcodes, applied on imported products - I am sure you have seen many examples in supermarkets around […] [Populate Several fields of new record with values from previous record]( - Hello Everyone - I am a newbie to SQL scripting so please don't take anything for granted regarding my knowledge of SQL, bearing in mind that I have had very limited exposure to it. That been said let me explain my challenge as best I can. I am developing an application using a RAD tool […] [real time logging in production enviornment]( - Hi, What is the best way to do real time logging of SQL server in production environment without affecting the performance of the same. Logging of events like cause of Time Out errors or state of the sql server after the command to exec a stp was called and before that stp was started to […] [Is there anything equivalent to a Last() Function]( - This is just a theoretical question so I don't have any actual DDL to share but hopefully the example I describe below is adequate enough to convey what it is I'm asking. Is there anything equivalent to a Last(@SearchValue,@PrimaryKey) Function that can be used when other traditional aggregations like MIN() and MAX() are being used […] [Query to retried list of names with value and if a value does not exist!]( - Hello, I have 2 tables : Names and NameProperty. In the table NameProperty The property Food as by default a value of YES. The problem is that the default value YES is not showing as line in the table. If a user put it to NO and then saves it back to YES, only then […] Administration - SQL Server 2014 [SQLServer won't start?]( - Hi Not sure what happened, but hoping someone can point me in the right direction. sqlserver won't start, the event log has this error Faulting application name: sqlservr.exe, version: 2014.120.4100.1, time stamp: 0x5535c7e7 Faulting module name: MSVCP100.dll, version: 6.3.9600.19678, time stamp: 0x5e82c88a Exception code: 0xc0000135 Fault offset: 0x00000000000ecf40 Faulting process id: 0x19ec Faulting application start […] Powershell [Using powershell to strip RTF tags and return text.]( - Hi, We've been looking to pull the text from a table in a vendor DB that stores in RTF format. I found a solution on a site but am having trouble getting it to work. It seems to work ok if I pass a result straight to it but if I try and use SQL […] Analysis Services [Dimension key problem]( - Hi, I need to modify an existing dimension that calculates that how late (how many days) shipments are. What the actual shipments (like IDs or weights or whatever) are completely irrelevant in this context. It is late by X days (zero or less than zero = not late) and that's it. I have the expected […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -