Azure Cosmos DB – HTAP using Azure Synapse Link (2021-11-03)

SQLServerCentral Newsletter for November 3, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Azure Cosmos DB – HTAP using Azure Synapse Link]( - [Don't miss out on 3 days of data platform training]( - [Automating Extended Events data collection]( - [From the SQL Server Central Blogs - What’s my MAC Address in a Command Prompt?–#SQLNewBlogger]( - [From the SQL Server Central Blogs - Scaling your Azure SQL Managed Instance]( Question of the Day - [Protocol Order]( The Voice of the DBA  Daily Coping Tip Set a goal that brings a sense of purpose for the coming months 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. Do We Care About Disks? I ran across [a tip on MSSQLTips from Joe Gavin]( recently, and it got me thinking about hardware and disks. For a lot of us, we deal with servers and storage, and not disks. While we might have a disk on a laptop or desktop, most of the production systems just attach to remote storage. In fact, outside of a portable drive in my bag and the SSDs on my desktop, I haven't actually seen a disk in years, spinning or other. All the systems I connect to are remote, and I honestly would have no idea if the disks are local to the system or remote. I know that this can matter, and certainly impact performance. I just usually don't care. More and more often, I care about the actual performance in latency and IOPs. Years ago I used to know the different RAID configurations, and I would sometimes specifically request them from storage people. Or I'd just set them up myself. That might seem strange to some people, but there was a time when I got a shipment of boxes and assembled a database server myself from pieces and parts before mounting it in a server room. I wonder to what extent any of you care about physical hardware anymore. Perhaps you have a data center in your organization that is owned and managed. There might even be specifications you provide when a new database is needed that correspond to the storage configuration. Perhaps you just request a certain level of IOPs and latency performance. Maybe you have a standard for how databases are stored on disks. Maybe you're like me and don't care. You just ask for a size and then give a thumbs up or thumbs down for the amount of performance. If things aren't running well, you let someone else figure out how to make them faster. In some sense, this feels like I've abdicated some of my role as a database professional, but really, I don't care about individual specs. If I want to know if something is good, or fast, I ask friends who do care and just rely on their advice. In most cases, I just care about turning up or down the performance level, which is why I like the cloud. I can often just provision faster storage and pay more for that. Or go with slower performance and pay less. I like that I can make the decision. This isn't to say the cloud is easy; I just don't care about the details. If I want something faster, I'll reach out to someone who focuses on the performance of that particular cloud platform and system and ask them. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Azure Cosmos DB – HTAP using Azure Synapse Link]( Arjun Sivadasan from SQLServerCentral In this article, we will explore the HTAP capabilities of Cosmos DB. The goal is to derive real-time insights from transactional changes made to Cosmos DB, in a cost-effective manner with minimal overhead. I want the solution to be scalable, reliable and overall simple to maintain. [External Article]( [Don't miss out on 3 days of data platform training]( Additional Articles from Redgate PASS Data Community Summit is just a few days away—don't miss your chance to join the world's largest gathering of data platform professionals. Featuring over 250 sessions and speakers, Learning Pathways, keynotes, and networking opportunities, this is your chance to level up your data career. Registration is free! [External Article]( [Automating Extended Events data collection]( Additional Articles from SimpleTalk It’s not difficult to set up an extended event session, but reviewing and saving the data can be complex. In this article, Edward Pollack demonstrates how to collect extended event data. [Blog Post]( From the SQL Server Central Blogs - [What’s my MAC Address in a Command Prompt?–#SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. The other day I was working with a... [Blog Post]( From the SQL Server Central Blogs - [Scaling your Azure SQL Managed Instance]( BLOB EATER from All About SQL No doubt there will be times where you need to scale up the actual instance in terms of vCores but also you may want to move across tiers (for...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Protocol Order If I want to change the order for client protocols to be tried for OLEDB and ODBC connections to SQL Server, what should I do? 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) Synapse Distributions If I create a table in Azure Synapse, how many distributions (locations) are used to store my table by default? Answer: 60 Explanation: The default, and actually the set number, is 60. These are similar to partitions, but don't confuse these. Partitions are a specific feature in SQL Server. Here, Synapse distributes data across 60 locations. Ref: CREATE TABLE (Synapse) - [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 [Join Only Option - Always On]( - Hi all Hopefully a fairly simple question to start things off after a long time absence I have a requirement to add another node to a current Always On system but the change window to do the work is short so I was thinking to save time I could manually restore the databases to […] SQL Server 2016 - Administration [Intermittent sqlmail success - troubleshooting]( - Microsoft SQL Server 2016 (SP2-GDR) (KB4583460) - 13.0.5103.6 (X64) SQLMail Account configured with SSL checked and Basic Authentication account to smtp.office365.com Suddenly last week emails stopped working. First we thought it was TLS version configuration issue but after I set it up as a scheduled task running every minute I realized the email works SOMETIMES. […] SQL Server 2016 - Development and T-SQL [Execute SQL Task error (SSIS)]( - I am getting this for a simple SQL statement: [Execute SQL Task] Error: Executing the query "" failed with the following error: "". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. update payments1 set [LineItem Amount] = replace([LineItem Amount],'$(','-') It does not […] [Restart SSA in a job?]( - Hi, I have a job that rebuilds indexes after a refresh of a replicated report DB. The issue is sometime I need to restart the SQL Server Agent to get it to start. Is there a command I can use to first restart it?  Thanks  [T-SQL help]( - I need help to write the T-SQL with scenario that if room got multiple room_cd and flagged with M and F then room_type will be flagged with majority of M or F like in 106, with room 1 got 1 to 3 room_cd and flagged with both M and F , but there are 2 […] SQL 2012 - General [what this constrain meaning ?]( - I work on sql server 2012 i see constrain but i don't know what is meaning ALTER TABLE [Parts].[TradeCodes] ADD CONSTRAINT [UC_PartCode] UNIQUE NONCLUSTERED ( [PartID] ASC, [CodeTypeID] ASC, [PartLevel] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] […] [How to get feature key and feature value separated $ based on part id ?]( - I work on sql server 2012 i need to get featurekey and feature value separated $ Based on partid but i don't know how o do that by select sql query ? expected result as below  sample data create table #PartsFeature ( PartId int, Featurekey nvarchar(200), FeatureValue nvarchar(200), ) insert into #PartsFeature(PartId,Featurekey,FeatureValue) values (1550,'Botato','Yellow'), […] SQL Server 2012 - T-SQL [Update table FirstName & LastName from Email addresses]( - I have a table with three fields, FirstName, LastName and EmailAddress. The EmailAddress field is already populated and follows a 100% consistent format e.g.: John.Dunne@MallWins.com, Lisa.Franklin@MallWins.com, Mary.Renaldo@MallWins.com, etc. I need to write an update query that will populate the FirstName and the LastName as one batch. I tried this to do just the FirstName and it gives […] SQL Server 2019 - Administration [SQL Instance Aliases - Urgent assistance needed please]( - I have a migration problem. I cannot get my SQL Server instance aliases to work remotely. I've set up aliases before, and I've double-checked everything. The assigned ports are open, the protocol has been updated with the correct port, the aliases are set up in both spots on the config manager, browser is running, TCP […] [Error connecting using AlwaysON Listener]( - Dear Friends, I have a 2-node Read-Scale Always ON Availability Group to which I added a Listener. Though no issues during the creation of the Listener , the connection to the instance using the Listener is not possible. Just can't get through. Wondering if it has to do with the Name Resolution , or it […] [Setting Up SQL Server 2019 for Optimal Performance]( - Hi I have a very large database (600GB) that I am looking at creating. I will be running finance data analytic algorithms. I am considering AI algorithms too as the second project once the main one (data analytics) is completed. The AI will also be finance related. I have not decided if I should go […] SQL Server 2019 - Development [OK, I still think like a programmer -- is there a better way to do this?]( - My SQL tools tell me this invokes an inefficient step with an index scan. I am treating this as a learning exercise since it's not really a problem, but I have code written by others (vendors) that I see this a lot and I'd like to be able to suggest fixes. The query plan is […] [From the sys.sql_modules (linked.db.schema.vw_xxx) seperate column]( - Hi All, Im working an a issue and have some thinks I would like to fix it. At moment I need the definition of a all views, im jusing the follwoing query to find the definition (SYNTAX): SELECT definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound FROM sys.sql_modules WHERE object_id = object_id( 'view' ); All the views is a […] [Calculate number of times a number can be split evenly into pre-defined groups]( - Hi there I have to write a procedure that can calculate the number of times that 1 number can be divided by 2 evenly, until i get to another specifed number Now in the example. I have a number representing the number of records = 1,555,201 Now I know that the denominator is 800 So […] Analysis Services [SSAS partitions delete - .prt files not deleting]( - I have an unusual problem with an existing cube. When I delete a partition using SSMS or xmla the partition deletes from the cube successfully in SSMS. However, the physical files are not deleting from the server. Does anyone know why this is happening and how to correct it? thanks Jason    [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -