A Fine Slice Of SQL Server
Deb Melkin uses a favorite feature of mine:
Have you ever wanted to know how to create a parquet file directly from your SQL Server 2022 instance? Did you even know that it was an option?
Setting this up sounds like it would be really complicated, but it’s much simpler than it seems. Join me as I show you how:
Click through for the video.
Comments closedIf you are a production DBA (or Accidental prod DBA) you’ve gotten that frantic call in the middle of the night. Or maybe during little Suzy’s soccer game? Something broke, alerts are firing, nobody can order your widgets and the help desk tech has no idea where to start.
I’ve been on call since 2006. These are the most common things I’ve been called for at the worst times:
Read on for Kevin’s list. It’s a good one. I also get my share of “replication broke” but fortunately for most people, replication is pretty uncommon in environments.
Comments closedVlad Drumea changes some settings:
You might have noticed when scripting out stored procedures in SSMS that it prepends two SET options before the CREATE PROCEDURE statement.
These are:
- ANSI_NULLS
- QUOTED_IDENTIFIER
Read on to learn more about how these behave and why it’s important to set them in a specific way.
Comments closedPeter Schott needs to perform a series of bulk inserts:
Sometimes you need to extract a large number of tables into some other format. I’ve written about BCP earlier as a quick option to move data around, but what if you need a delimited text file or a way to repeat the calls without too much trouble across a set of servers? I had this come up recently and wrote up a combination of PowerShell, the sqlserver module, and the bcp.exe tool to allow for a variety of extract types and also, importantly, ensure that we get header data with those extracts in case we need them to pull in to another process. This is doable without the header piece, but many processes (and humans) expect some sort of header data. Thus – a slightly expanded process.
Click through for a script and an explanation of the process.
Comments closedRahul Miglani comes up with a list:
As organizations embrace cloud computing, multi-tenancy has become a popular architectural choice, enabling multiple customers (tenants) to share a single cloud environment. However, one of the biggest challenges in multi-tenancy is data isolation—ensuring that each tenant’s data remains private, secure, and accessible only to authorized users.
Microsoft Azure provides several data isolation strategies that allow businesses to securely manage and scale multi-tenant applications while ensuring compliance with regulatory standards like GDPR, HIPAA, and SOC 2.
In this blog, we will explore key data isolation strategies in multi-tenancy Azure architecture, their advantages, and best practices for implementation.
Reading through the list, the same set of options are available on-premises, though the calculus can be a bit different.
Comments closedArshad Ali announces a connector:
We are pleased to announce the availability of the Fabric Spark connector for Fabric Data Warehouse (DW) in the Fabric Spark runtime. This connector enables Spark developers and data scientists to access and work with data from Fabric DW and the SQL analytics endpoint of the lakehouse, either within the same workspace or across different workspaces, using a simplified Spark API. The connector will be included as a default library within the Fabric Runtime, eliminating the need for separate installation.
Click through to check out its capabilities. This is a tiny step toward where I think Microsoft Fabric should go: any tool accessing the same data, eliminating separate lakehouses vs warehouses and having to remember that you can’t use this syntax in this scenario unless you connect to it this way and sacrifice one live chicken.
Comments closedAnthony Nocentino jump-starts an availability group:
In this post, the fifth in our series, I want to illustrate an example of using the T-SQL Snapshot Backup feature in SQL Server 2022 to seed Availability Groups (AGs) with storage-based snapshots. Efficiently seeding an Availability Group is essential for maintaining high availability and ensuring effective disaster recovery. With the introduction of T-SQL Snapshot Backup in SQL Server 2022, snapshots can now be created at the storage layer. This advancement significantly speeds up the initialization of secondary replicas, particularly in environments that handle large databases.
This post will walk through a PowerShell script that effectively seeds an AG using T-SQL Snapshot Backup, dbatools, and Pure Storage FlashArray.
Click through for the script and the process.
Comments closedAndy Brownsword wrote code with an error in it:
When constructing or investigating a SSIS package we can run into a variety of issues. To help resolve there are various techniques which can be used to troubleshoot the package.
Whilst we have the Progress tab for the package to tell us what’s happened during execution, it’s usually more effective to debug packages in flight. Below we’ll look at a few ways we can achieve this:
Never having written code that doesn’t work perfectly the first time, this post is, of course, merely academic for all of us perfect developers. But for those people who may have let a little something sneak in or have to deal with the errors that others have seeded into our beautiful programs, Andy provides three separate techniques for troubleshooting packages in flight.
Comments closedMonica Rathbun truncates some data:
SQL Server performance issues often stem from easy fix bottle necks that can be fixed with the right tuning strategies. This short blog will focus on the DELETE statement.
Click through for the full story. One minor correction that I’d offer is that the TRUNCATE TABLE command is logged, which is why you can roll it back in a transaction. The logging process is much less intensive than with DELETE because of the deferred drop logic that Paul talks about in the link.
That aside, Monica’s key point is absolutely correct: DELETE operations tend to be quite slow, especially as the number of records you need to delete increases. This is where techniques like batching delete operations can help reduce the pain level, but if you’re deleting every (or almost every) row from a table, there’s probably a better method. Unless replication is involved in the mix—in that case, there are no good methods for anything because replication hates us.
Brent Ozar does some sleuthing:
Over the last several years, Microsoft has been putting less and less effort into Cumulative Update documentation. We used to get full-blown knowledge base articles about fixes, but these days, we get a collection of footnotes with deceiving hyperlinks that look like they’re going to lead to more information – but they simply lead back to themselves.
So whenever a new Cumulative Update drops at SQLServerUpdates.com, before I install it, I like to:
- Log my dev server’s list of current sys.all_objects, all_columns, messages, configurations, etc to tables
- Apply the update
- Query the new contents of sys.all_objects, all_columns, etc to see what new ones were added
Click through for a script that does just this. It’s not as good as having detailed patch notes, but it beats having nothing.
Comments closed