Managed Vs Unmanaged Tables – Data Engineering with Fabric (2024-03-20)

SQLServerCentral Newsletter for March 20, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Managed Vs Unmanaged Tables – Data Engineering with Fabric]( - [Leave it better than you found it]( - [From the SQL Server Central Blogs - #ThankfulThursday]( - [From the SQL Server Central Blogs - How to Retrieve all the Spark Session Configuration Variables in Microsoft Fabric]( - [SQL Server 2022 Administration Inside Out]( Question of the Day - [Always On Setup]( The Voice of the DBA  Re-platforming Re-platforming is the process of moving a system to a new platform. Imagine taking an ASP.NET/SQL Server website and moving to Azure Functions on Azure SQL Database. Or maybe taking a Java client/server app with Oracle and moving it to a series of microservices against MongoDB. Those changes could be a net benefit to your organization in the end, but they aren't quick or easy. They're often fraught with various challenges that can cause a lot of stress while creeping over budget. There's [a post that talks about some of the things you might think about]( if you embark upon a re-platform. Often this takes place when an organization is looking to modernize their tech stack. Quite a few of the technology DevOps success stories take place when the older structures are not maintainable, but also not able to handle increased workloads or performance requirements. It's often said that a complete rewrite rarely makes sense. I think that's likely true, but that doesn't mean it isn't something you should do. It says that you should rarely do it, and you need to pick a good time when your old application, database, network, or some combination of these can no longer meet your anticipated future growth demands. This is likely a debate that your organization should have every year or two and ensure that continued investment in the current platform makes more sense than a new one. Once you decide to move forward, it will be a journey, which means understanding how new and old systems will interact becomes important. Compatibility, at least for some time, is very important. I'd say this is especially critical for how your data systems will work together and what options you have to replicate/ETL/etc. the data between systems. Even if you plan to make a cutover from one system to another, data migration is fraught with issues. Anyone trying to ensure data moves cleanly from one place to the next should have strong checks, usually with automated tests, that evaluate if your data is moving correctly. Row counts aren't enough, or not the only metric. Random checks of individual rows, as well as checks for outliers, should be a regular part of any project that will migrate data. Learn where you struggle to correctly transform data because there will be places where this happens. I don't believe that wholesale re-platforming often makes sense. Leaving SQL Server to go to another database platform, or even when you might try to change the paradigm with NoSQL or a Data Lakehouse, often costs a lot. Getting a return after staff training, migration costs, and even delays from inefficiencies during the process often equals many years of licensing. I think slow migrations are a better idea, perhaps adding something like ElasticSearch or Redis, experimenting with a small data lakehouse for a particular purpose, and replicating some data to Neo4J for complex hierarchy queries. These evolving mechanisms give you the chance to experiment and learn about the process. It will help you understand if there is a positive ROI if you continue. In many ways the world of technology changes slowly. We have lots of new shiny toys, but many of them don't magically solve your problems. There are always tradeoffs and without strong domain knowledge, you may have more unknown unknowns that reduce your chances of success. Re-platforming can work, but make the decision carefully and proceed methodically, not with haste. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Managed Vs Unmanaged Tables – Data Engineering with Fabric]( John Miner from SQLServerCentral Learn how to get started with Microsoft Fabric along with the differences between managed and unmanaged tables. [External Article]( [Leave it better than you found it]( Additional Articles from SimpleTalk This is for the folks who still have to log into remote machines and do work manually on the box. Yes, we still exist, and we will for as long as we’re still using physical servers in data centers and even IaaS. Not everyone has transitioned to server core and full-on PowerShell remoting for everything [Blog Post]( From the SQL Server Central Blogs - [#ThankfulThursday]( Tim Radney from Tim Radney - Database Professional Wednesday March 13th 2024 I had the honor of speaking at the  Redgate Summit in Atlanta and got to meet a lot of new people and get to hang... [Blog Post]( From the SQL Server Central Blogs - [How to Retrieve all the Spark Session Configuration Variables in Microsoft Fabric]( Koen Verbeeck from Koen Verbeeck I was trying some stuff out in a notebook on top of a Microsoft Fabric Lakehouse. I was wondering what some of the default values are of the configuration... [SQL Server 2022 Administration Inside Out]( Site Owners from SQLServerCentral Dive into SQL Server 2022 administration and grow your Microsoft SQL Server data platform skillset. This well-organized reference packs in timesaving solutions, tips, and workarounds, all you need to plan, implement, deploy, provision, manage, and secure SQL Server 2022 in any environment: on-premises, cloud, or hybrid, including detailed, dedicated chapters on Azure SQL Database and Azure SQL Managed Instance.   Question of the Day Today's question (by Stewart "Arturius" Campbell):  Always On Setup While setting up Always On for SQL server, I write the following script: USE [master] GO CREATE AVAILABILITY GROUP [HR_AG] WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY, BASIC, DB_FAILOVER = ON, DTC_SUPPORT = NONE, REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0) FOR DATABASE [HR] REPLICA ON N'DBServ01' WITH (ENDPOINT_URL = N'TCP://DBServ01.xxxxx.mycompany.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = MANUAL, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)), N'DBServ02' WITH (ENDPOINT_URL = N'TCP://DBServ02.xxxxx.mycompany.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = MANUAL, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)); GO What will the result be if this is executed in an instance of SQL Server 2022? (choose 2) 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) SQL Express Agent How can I run SQL Agent with SQL Server Express 2022? Answer: No SQL Server Express edition includes SQL Agent Explanation: No edition of SQL Server Express includes SQL Agent. Ref: Management Tools (Features) - [ [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](. --------------------------------------------------------------- Administration - SQL Server 2014 [Strange Deadlock Graph showing multiple DB but isolated]( - Hi Experts, I am observing strange deadlock behaviour where one deadlock graph have 2 isolated databases involved. Can any experts here shed some light on this?  Regards SQL Server 2019 - Administration [How can we enhance statistics information collection]( - Currently we are collecting database statistics information using select * FROM sys.dm_db_index_physical_stats( 14, NULL, NULL, NULL, 'SAMPLED') For some larger databases this statement takes more than 23 minutes to complete. Is there anything we can do to speed up this process and collect the same data?  [when dc is down can we remove database in dr out of always on secondary replica]( - HI All, primary server is in always on secondary is a replica when primary DC is down due to unexpected power off , can we make the secondary database online by removing them from availability group when dc is down can we remove database in dr out of always on secondary replica thanks naga.rohitkumar [SQL server 2019 alwayson problem]( - Hi, I've SQL server 2019 STD always on set up with 2 nodes and file share witness (located on third server). yesterday we had an network issue that the main switch was down for 15 minutes. after the network is back i see that the whole SQL server alwayson cluster was deleted from the computers […] [Rebuilding/Reorganizing in a very transactional table]( - Hello masters, It's been quite some time since i last posted here but i encountered a really hard opponent. We've got this table, this table has around 50-60m rows of transactions and this is only in one month, we've got this daily process that moves the transactions from the main to the historic one (basically […] [Database Ownership - Does it Matter ?]( - From a practical standpoint, does it matter who is shown as the owner of a database ? What if it's a network user, and their account is deleted when they leave the company? [SSRS 2019 Migration]( - Hello everyone, i have been trying to migrate SSRS 2012 to 2019 using powershell script which i found here [ microsoft/ReportingServicesTools: Reporting Services Powershell Tools (github.com) ] Unfortunately the samples mentioned in the script is not helping as well. Pls find the options i have tried with it & their respective exception Can anyone pls help […] SQL Server 2019 - Development [EXEC (@SQL) AT QASERVERNAME; Fails with read-only database error.]( - All, Need a linked server guru.  Trying the following in a stored procedure: SET @SQL = 'DROP TABLE IF EXISTS DB_NAM.SCHMA_NAME.TBL_NAME'; IF @@SERVERNAME = 'QASERVERNAME' BEGIN; SET @HUBServerName = '[QAHUBSERVERNAME]'; EXEC (@SQL) AT QAHUBSERVERNAME; END; Then I get this: Msg 3906, Level 16, State 2, Line 10 Failed to update database "DB_NAM" because the database […] [ACCIDENTAL DUPLICATE...]( - Accidental Duplicate... my bad... [nested subqueries vs separate queries performance difference]( - Hi everyone I am looking at improving the performance of my queries. There is an approach I am considering but I don't know if it will actually improve things or not. I won't know until I try (which I will) but I am interested in getting some feedback from the community. Query 1 select * […] Reporting Services [pagename property for my tablix doesnt show fields]( - Hi , i went to set page name on a tablix mostly for naming worksheets when report is saved to excel.  From what i can see, ssrs is saying no dataset is attached to the tablix. Which isnt true. Can someone help? at the moment that total line is hidden and i'd have to ask […] [Using Parameter and Booksmarks together?]( - Hello, I have a SSRS 2016 Report with multiple tables on every pages. User should select a particular table and jump to the page of this table. Is it possible to do? I did not find any way to do it. Try: I create a textbox with a Action to Bookmark and the expression of […] SQL Server 2022 - Administration [Log shipping setup failing with error - Access is denied]( - We are setting up a log shipping from the Dev to the Reporting Server. However, the GUI process is failing with the below error. Source VM: XXX-SQL-D Target VM: XXX-RPT-D-DB Error from Log File: Cannot open backup device '\\XXX-SQL-D\i$\MSSQL\Backup\LSTONY\LOG\LSTONY.bak'. Operating system error 5(Access is denied.). RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201) […] SQL Server 2022 - Development [very slow performance using WHERE on Oracle linked server]( - I have a linked server pointing to an Oracle instance using OraOLEDB.Oracle. The table contains about 40K rows. My issue is that while I can get fine performance doing a SELECT or DELETE with no WHERE clause, the moment I add a WHERE it is just horrible. Even selecting a single row takes 20+ minutes. […] [Grouping Count]( - Hi All, I have domain in Column A. Here I need to count how many domain and need them to flag 1, 2, 3 and so on till maximum. How could I do them in SQL Server. Please find the attachment excel file for reference. Thanks   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -