Bridging Snowflake and Azure Data Stack: A Step-by-Step Guide (2024-06-03)

SQLServerCentral Newsletter for June 3, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Bridging Snowflake and Azure Data Stack: A Step-by-Step Guide]( - [Who’s Changing the Table? Answers and Discussion]( - [From the SQL Server Central Blogs - Automating Container Image Builds with Docker Build Cloud and Github Actions]( - [From the SQL Server Central Blogs - Power BI: Refreshable Excel Files in PBI Service]( - [Deciphering Data Architectures]( Question of the Day - [The SQL Assessment API]( The Voice of the DBA  DBCC CLONEDATABASE Semi-Discontinued I saw [a tweet that DBCC CLONEDATABASE was being discontinued for production databases]( which both scared me and didn't make sense. I've used this a few times for a quick copy of a database and like how it works. Discontinuing it seemed strange to me. Then I [read the blog post]( which notes that it's not being supported for production deployments. The post doesn't explain why, but I'm guessing this is because all the stats and other metadata moves, and this might mess up the optimizer if different data is added. I don't know who deploys production databases like this, but I could see people who have federated or sharded databases using this to create a new blank copy and then uploading data into it. Or maybe people who need new databases that are distributed onto remote office/edge devices used it? If you use this to create production dbs, let me know. This command will still be used for generating schema-copies for diagnostic and troubleshooting purposes, which is what it's there for. I assume this means Microsoft Support will support you using this to investigate strange query issues or if you create a database at their request. However, there isn't a mention of this being used for development and test environments, which is where I use it. Specifically, [I've used it quite a bit lately with Redgate's subsetter utility](. I need a target that's shaped like the source, and dbcc clonedatabase gives me that. I don't really care about stats or anything else, I just need schema set up to move data around. It's useful there. It's also useful for a quick test of a deployment, where I can ensure I get the latest production schema and then run a deployment against it (hopefully using [Flyway]( looking for errors. If you use this command, don't worry. The tool isn't going away, and the restriction against production copies doesn't take place until March 1, 2025. That's nearly a year from now to change your process. If you've never used it, well, I don't have a good reason to start using it, but you ought to be aware of how it works. Learning about new features is handy, as you might discover a problem the knowledge can solve. However, if you don't know it exists, it will never enter your mind as a solution. Be curious and try things. It's easy, and it's fun. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Bridging Snowflake and Azure Data Stack: A Step-by-Step Guide]( diponkar.paul from SQLServerCentral This article shows how to access Snowflake information from Synapse Analytics. [External Article]( [Who’s Changing the Table? Answers and Discussion]( Additional Articles from Brent Ozar Blog Your challenge for this week was to find out who keeps mangling the contents of the AboutMe column in the Stack Overflow database. [Blog Post]( From the SQL Server Central Blogs - [Automating Container Image Builds with Docker Build Cloud and Github Actions]( dbafromthecold@gmail.com from The DBA Who Came In From The Cold In a previous post we went through how to use Docker Build Cloud to remotely build a Docker container image from a Github repository. In that example, we kicked... [Blog Post]( From the SQL Server Central Blogs - [Power BI: Refreshable Excel Files in PBI Service]( DataOnWheels from DataOnWheels Ever since Excel made its debut in the 1980’s, it has been used as a quick way for end users to input and manipulate data on their own without... [Deciphering Data Architectures]( [Deciphering Data Architectures]( Additional Articles from SQLServerCentral Data fabric, data lakehouse, and data mesh have recently appeared as viable alternatives to the modern data warehouse. These new architectures have solid benefits, but they're also surrounded by a lot of hyperbole and confusion. This practical book provides a guided tour of these architectures to help data professionals understand the pros and cons of each.   Question of the Day Today's question (by Steve Jones - SSC Editor):  The SQL Assessment API What is the SQL Assessment API? 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) Adding Hints with Query Store I have a query that is being tracked in the SQL Server 2022 Query Store. I want to add a hint to this query to ensure that the MAXDOP for this query is one. How can I do this with minimal code changes Answer: Use sp_query_story_set_hints to set the hint for a particular query Explanation: In Azure SQL Database, MI, and SQL Server 2022, we have the ability to force a hint for a plan in the query store. You need the plan id and then you can run sp_query_story_set_hints to add the hint. Ref: - sp_query_story_set_hints - [ - Query Store Hints - [ [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 [Backup with Checksum Show No Issues, While DBCC CHECKDB Reports Errors]( - I took a backup with the CHECKSUM option, which was successful and showed no errors. However, when I restored the same backup, it showed DBCC CHECKDB errors. I want to understand why the checksum did not detect any issues, but DBCC CHECKDB did. What are the issues that a checksum will not detect but DBCC […] [Linked Server Product Name?]( - Just came across something weird. Was wondering if anyone else has ever seen this. All the years I've worked with SQL server linked server objects, I have always put "SQL Server" in product name. All the code examples on the internet include that or mention (in BOL) "Product name Type the product name of the […] [Can index fragmentation cause the database to enter suspect mode?]( - My database was in suspect mode, and when I checked using DBCC CHECKDB, it returned errors. As mentioned in the link below, I used the REPAIR_ALLOW_DATA_LOSS option with the DBCC CHECKDB command, but few errors persisted. Later, I dropped and recreated the indexes. This resolved the issue. My question is whether index fragmentation could […] SQL Server 2016 - Development and T-SQL [Multiple deadlocks occurring with contentious code]( - We are seeing multiple frequent deadlocks occurring with the following extract of code, which forms part of a stored procedure called sp_WS_Save_Record_Details. We have a web service that calls multiple instances of this stored procedure simultaneously. Records in the Record_Stub table are identified by an external reference. The code is trying to ensure that if […] [select string removing two sets of characters]( - Hi , I need help replacing a certain strings from a query response. My searches are pointing to a replace in the actual table but I am looking for sql that returns a different value than what is in the table. I am selecting a varchar(25) field and want to return the characters without the […] SQL Server 2019 - Administration [Fixing snapshot replication login error]( - Hello experts, We set up the Brent Ozar SQL Agent alerts, one of which sends out an email for Severity 20 errors. We started getting this one every minute yesterday. I was able to run a Profiler trace and narrow down the error to a failed login attempt by a SQL Server replication job. But […] [Swapping ports between 2 instances - can't understand results]( - Have 2 instances on the same TestServer 1. Default 2014 Dev edition on port 1433 2. Named 2019 Express instance "SQLExpress2019" on port 15191 SQLBrowser is working. TCP=1433 port is open on this TestServer. I swapped ports between the 2 instances and restarted the services. Can't tell if UDP 1434 is open.. UDP port 1434 […] SQL Server 2019 - Development [Monitoring if something is deleted in DB]( - Hello, I have long-term task, which came one year ago, and just now I have a little free time to start working on it. I will explain a little bit of the task, and then what I have done so far. Some time ago, even before I worked here, colleagues of mine using the web-application […] [is azure an option for multiple instances of dev engine and ssas?]( - Hi as you can see at ‚ we would really like multiple instances in dev or on our desktop of the engine , ssas etc so we can manage multiple projects at the same time.  i realize if we use azure instead we are probably excluding ssrs and ssis but wonder if the community can […] [Excel blobs & BCP extract commands...]( - Hi everyone, I have been asked to investigate storing Excel files in Sql Server as a BLOB. These files could be of any size and I will not know what headers they will have beforehand. They could have multiple sheets per Excel. We may need to re-constitute the file/s promptly at any stage. I haven't […] [Unable to connect to SQL 2019 using Pyodbc]( - Development Environment I am learning how to use pyodbc and accessing database in SQL server 2019. Here are the software versions I am using: SQL server 2019 VS Code 1.89.1 Microsoft ODBC driver 18 for SQL Server Python 3.12.3 pyodbc 5.1.0 Developer laptop: Windows 11 Connection Errors: When running python test program in VS, it […] [Aggregating over multiple columns]( - Hello friends, See setup code below. I have a working solution but I am concerned that the DISTINCT is going to kill performance. I am hoping to see some alternative ways to solve this problem so I can compare performance with my real data. DROP TABLE IF EXISTS #Sales CREATE TABLE #Sales ( Country VARCHAR […] Connecting [Can you connect Power Apps to SQL Server if you work in the government?]( - My agency has been using the combo of on-premises SQL Server for the back end and local desktop software for the front end. Now upper management is saying that they want web applications only. And they really want to use our MS 365 license rather than have to pay for anything else. I'm investigating Power […] Design Ideas and Questions [Database design help and tips for a small app used to coach tennis players.]( - Hi,I am a tennis coach and creating a database/app to automate tasks related to students, programs, practices, performances, and testing. I am finishing the database design and have a few questions, mostly regarding the program and practice "time" aspects of the system. First, here is a summary of the "business rules": There are multiple programs […] SQL Server 2022 - Development [I have few snapshot transactions, now i need to put noloc in all tables.]( - I have few snapshot transactions , now my admin has informed me that put no lock in all tables in snapshot transactions, as it was blocking locking deadlocking with others. so I suggested to put read uncommited instead of snapshot when u are putting no lock in all tables of the transaction. q1) snapshote takes […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -