A Fine Slice Of SQL Server
Jess Pomfret takes us through the STRING_AGG() function:
I’m a SQL Server Database Engineer by day, but I must say my blog has a lot more PowerShell and automation posts than T-SQL. However, last week I found a really great T-SQL aggregate function that I had no idea existed, so I thought I’d share it with you.
It’s a good function.
Comments closedChris Adkin explains why support for OpenShift is important for SQL Server Big Data Clusters:
One thing that should become immediately apparent when installing and administering an OpenShift cluster, is that it is a lot more prescriptive and opinionated that vanilla Kubernetes. The simple reason for this is that OpenShift is intended to be deployed to environments that require enterprise grade levels of hardening and security. For example, Red Hat mandates the operating system distributions you must use, to the extent that when deploying a cluster on VMware – Red Hat’s documentation recommends the use of OVA’s, compressed files containing install-able virtual machines.
Read on for the full story.
Comments closedDavid Fowler shows how you can back up databases to Azure Blob Storage:
SQL Server has given us the option to backup our databases directly to Azure BLOB storage for a while now but it’s not something that I’ve had all that much call to use until recently.
So this is just going to be a quick walk through on how you can backup your on premise SQL Servers to Azure BLOB storage. I’m going to assume that you’ve already got an Azure account, if you haven’t, you get set up a free trial which will see you good for this demo.
Performance typically won’t be as good as backing up locally to disk, so if you need the fastest backup performance and cloud storage, the best route would be to write backups to disk and have a separate process which migrates them to Blob Storage, S3, or wherever. But in many cases, doing this directly can work out just fine, especially if you are already using an Azure-based VM.
Comments closedRaul Gonzalez shows us five things you can do with partitioning in SQL Server:
Once we have rebuilt that old data to minimise its footprint and moved it to a cheaper storage tier, if we know no one will have to modify it, it’d be a good idea to make it READ_ONLY.
By making the data READ_ONLY, we can not only prevent accidental deletion or modification, but also reduce the workload required to maintain it, because as we’ve seen before, we can action index maintenance only on the READ_WRITE parts (partitions) of the data where fragmentation might still happen.
Read on for the rest of the tips and note that none of these are directly of the “Make your queries faster” variety, though a couple can have positive performance implications.
Comments closedDavid Klee gives us the lowdown on firmware specifications in virtual machines:
The Register is reporting that future versions of Windows Server OS is going to require the TPM 2.0 chip and Secure boot enabled by default. Secure boot is quite helpful to validate that servers boot into trusted environments. It sounds basic and straightforward, but if your VM administrators are not preparing for this change now, a much-overlooked setting in the hypervisor might backfire and you might not be able to enable this setting. That scenario would be a disaster if your security team suddenly issued a decree stating that you must enable this setting by some date.
Read on to see what this means if you’re using Hyper-V or VMware.
Comments closedUntil recently, the process of picking up a certain number of partitions against a set of data files, always looked mysterious to me. However, recently, during an optimization routine, I wanted to change the default number of partitions picked by Spark for processing a set of data files, and that is when I started to decode this process comprehensively along with proofs. Hopefully, the description of this decoded process would also help the readers to understand Spark a bit deeper and would enable them to design an efficient and optimized Spark routine.
This is important information if you’re tuning Spark cluster performance.
Comments closedThe SQL Server team announces an open-sourced Apache Spark connector for SQL Server:
The Apache Spark Connector for SQL Server and Azure SQL is based on the Spark DataSourceV1 API and SQL Server Bulk API and uses the same interface as the built-in JDBC Spark-SQL connector. This allows you to easily integrate the connector and migrate your existing Spark jobs by simply updating the format parameter!
This appears to be different from the old Spark connector to Azure SQL Database and SQL Server. Also, for anyone potentially confused between it and PolyBase, this is going in the opposite direction: the Spark connector lets you access a SQL Server from an Apache Spark cluster, reading SQL Server’s data and processing it across a number of executor nodes. By contrast, PolyBase lets you read data stored in Spark SQL tables from SQL Server, virtualizing it so that it looks like a regular SQL Server table.
Comments closedGilbert Quevauvilliers encounters an error:
I got the following error DM_GWPipeline_Gateway_SpooledOperationMissing”,”parameters”:{},”details”:[],”exceptionCulprit”:1
This error was caused because the current Virtual machine where the On-Premise Data Gateway was running was cloned and then started up.
Read on to learn about the implications and how Gilbert was able to solve this issue.
Comments closedMike Bronowski takes us through the ImportExcel Powershell module:
Using Add-PivotTable is straightforward (and from now on I am closing the whole Excel). Note the Activate switch at the end. When you open the Excel file the worksheet that was used in the command will show up first.
A long while ago, I had put together Powershell code to do this kind of work with Excel, but back then we needed to use COM. This looks much simpler.
Comments closedMicrosoft.Data.SqlClient version 2 has just been released. This library is the latest and greatest .NET client driver for SQL Server and Azure SQL Database – and will be used by EF Core 5. In addition to a number of new features (which I blogged about earlier), this major version release also includes a number of breaking changes.
Click through for the list.
Comments closed