A Fine Slice Of SQL Server
Andy Leonard recommends not using backup-and-restore as an approach of moving SSIS packages around:
First, please do not misunderstand. You should back up SSISDB just like you back up all other databases – especially in Production. You should also conduct Disaster Recovery exercises in which you restore SSISDB from the latest backup, or avail yourself of Always On availability groups and / or Windows Server Failover Clustering.
With that caveat in mind, read on to see why.
Comments closedAndy Galbraith troubleshoots a problem:
Unfortunately a common error in many of our client environments is this:
—
Error: 8623, Severity: 16, State: 1.
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.–There are many potential causes for this, and the text of this particular error is very well-written because the primary cause is exactly what is listed – a complex query.
Read on to see how to find this complex query, as well as a few examples of complex queries.
Comments closedOne way you can work around the above scenario is to install multiple virtual machines. Now the first thing you might realize is that this will also take up a lot of compute and storage.
In reality, I use to use this method myself in the past using Hyper-V. To reduce the amount of storage the virtual machines used in Hyper-V I use to used parenting disks.
Since the introduction of containers and Docker this has become a less popular option. However, you can still read an old post of mine with tips in here.
Click through for additional options.
Comments closedTomaz Kastrun has a not-so-useless function:
The full moon function, or should we call it fool moon – due to it’s simplistic and approximate nature, calculates the the difference between the date (only date, no time, no long/lat coordinates) and Julian constant. Should you be using a different calendar, don’t run the function, just look out the window.
The function is written based on generalized equation for julian day numbers and months. Another one could be to calculate RMSE of the predicted values and realization of lunar behavior (lunatic start time). In this case – reversed engineering – you would use the the approximate date/time for the first new moon after that date if the synod period was constant. This number than obtained is only empirically proven by recursively solving for the new “possible date/time” of lunar behavior and calculate the prediction error. In order to minimize the RMSE value of the difference between the full moon dates/times predicted formula and the dates/times for the full moon over the next 10 years you get something like this.
Click through for the function as well as sound advice if it’s not a full moon.
Comments closedAs described in the blog post Apache Kafka® Needs No Keeper: Removing the Apache ZooKeeper Dependency, when KIP-500 lands next year, Apache Kafka will replace its usage of Apache ZooKeeper with its own built-in consensus layer. This means that you’ll be able to remove ZooKeeper from your Apache Kafka deployments so that the only thing you need to run Kafka is…Kafka itself. Kafka’s new architecture provides three distinct benefits. First, it simplifies the architecture by consolidating metadata in Kafka itself, rather than splitting it between Kafka and ZooKeeper. This improves stability, simplifies the software, and makes it easier to monitor, administer, and support Kafka. Second, it improves control plane performance, enabling clusters to scale to millions of partitions. Finally, it allows Kafka to have a single security model for the whole system, rather than having one for Kafka and one for Zookeeper. Together, these three benefits greatly simplify overall infrastructure design and operational workflows.
Read on to see where this story is at and what kinds of changes you’ll have to make to code.
Comments closedAndy Leonard clues me in on a new connection type:
SQL Server Management Studio – SSMS – supports viewing the contents of Azure Blob Storage containers.
I’m still going to use Azure Storage Explorer for all of my Blob Storage heavy lifting, but it’s nice to see that if I just want to see a listing during a presentation or something, I can.
Comments closedPeter Schott extends Tim Mitchell’s work:
I wrote about cleaning up the SSIS Catalog a while back, but needed to revisit this recently for a different use and needed something that can run in an ongoing manner. My earlier post still works, but I recently adapted some code from Tim Mitchell to create a stored procedure that can do that cleanup. Tim wrote the majority of this. I adapted it to wrap it in a stored procedure to handle varying batch sizes for the deletes as well as to ensure all of the tables are deleted in smaller sets of rows.
Here is the code to create the stored procedure. If your SSIS Catalog is not named SSISDB, adjust accordingly.
Click through for the script. It’s interesting to note how frequently cleanup processes for functionality in SQL Server is inadequate for the task at scale. I’ve regularly seen people write these sorts of things for SSISDB, Query Store, replication, ML Services (though that, at least, was changed), etc.
Comments closedThat said, I wanted to provide some updates with more of a focus on Tabular mode, as it has clearly become the new standard for how most organizations deploy SSAS. I recommend referring to that original series for a focus on Multidimensional mode, but much of the information provided in this blog post will be applicable to both modes. I recommend referencing Allen White’s blog post on SQL Server counters to learn more about many of the Windows-level counters that apply across the board, as I won’t speak to them here.
Let’s dive into the 15 SSAS performance counters you should be monitoring.
Click through for the set, as well as explanations for why.
Comments closedMagda walks us through a function new to Microsoft Excel 365:
Microsoft Excel 365 gives us a new function – LET(). It is not available for every Microsoft 365 subscriber though. A few weeks ago my Excel did not have this function, but now I can work with it. I did a few examples when I was learning this feature and want to share them with you today.
Click through for those examples.
Comments closedMichelle Gutzait walks us through recommendations on hosting SQL Server in Windows on VMware:
VMware has created a very detailed best-practice document for us, specifically for SQL Server. You may find the latest one here.
In case the link doesn’t work for you, or you have a different version of VMware, you can search for the proper SQL Server best practices on the VMware site.
Here are the main best practices VMware recommends, and the most important based on Pythian’s experience (SQL Server on Windows):
Click through for a detailed checklist.
Comments closed