Andy Levy has an admission to make:
It turns out I was doing this all wrong for months.
We know, but we thought it polite not to say anything about it.
Read on to see how Andy uses Test-DbaBuild to look for new CUs of SQL Server.
Category: Administration
It used to be that the only way to use SQL Server Management Studio (SSMS) against Azure SQL Database Managed Instance (SQLMI) was to create a VM on the same VNET as SQLMI and use SSMS on that VM. That VM was usually called a jumpbox (see instructions here).
But about a year ago Microsoft added a way to use SSMS without using a VNET (announcement) by allowing you to enable a public endpoint for your SQLMI. This made it easy for me to access a SQLMI database on my laptop.
That change enables what James shows us.
Comments closedTaryn Pratt shares an issue with columnstore cleanup in SQL Server 2019:
The output of
sp_who2was repeatedly showingGHOST CLEANUPandCREATE INDEX. Over and over and over again. To be clear, I’m not a clustered columnstore expert, I know enough to be able to maintain them as needed. I went to Twitter and mentioned what I was seeing. I was advised by @sqL_handLe to try trace flag 661 which disables the ghost record removal process, and by Joe Obbish via Erik Darling to enable trace flag 634 to disable the tuple mover background task.Initially, we enabled trace flag 634, but the logs continued to grow. We disabled trace flag 634. Then we enabled trace flag 661, and the logs continued to grow, so we disabled it. Finally, we tried enabling both of the trace flags. The big jumps stopped, but we now had about 400GB of logs that needed to be written to the reporting cluster before we could perform the failover.
While the logs were exploding we wondered if whatever was happening might have been caused by the deletions we did in early February. But why would they be triggered by the upgrade to SQL Server 2019?
Read the whole thing if you’re looking at a migration to 2019.
Comments closedKenneth Fisher needs to know a server’s current pricing tier:
I ran into an interesting problem today. I needed to find out the pricing tier of an Azure SQL DB but while I have access to to the DB via SSMS I don’t have access via the Portal. So I needed something I could use via T-SQL. I did some research, and found something that said it worked but didn’t look right to me. So I asked on twitter.
Click through to see the answer.
Comments closedStuart Moore has a public service announcement for us:
From looking around at some of the Issues that get logged for dbatools, it looks like people never clear out their SQL backup history from msdb. This causes slow queries and potentially misleading backup trees to be returned.
In my (not so humble) opinion you really shouldn’t let your backup history fill up with, let’s be honest, useless junk. In this post I’ll go through why I think you should be keeping way less in there than people think you should
Read on for Stuart’s thoughts on the topic.
Comments closedKevin Hill takes us through a nice use of dbatools:
My client asked me for a way to automate test restores and CheckDB either ad hoc or on a schedule, but not for certain databases that are close to or over 1TB in size.
Read on to see how Kevin pulled it off, all in under 4 hours.
Comments closedAndy Mallon walks us through setting up tempdb:
There are three problems I’ve got to fix. I need to (1) remove those two extra files, (2) grow the
tempdblog file, and (3) even out the size of the data files (and shrink them a little to make room for the larger log file. We’re going to tackle these in the reverse order than I listed them–partially out of necessity, and partially because it’s going to be easier.
Click through to see how Andy sets up tempdb. This is a good way to set up tempdb.
Comments closedSheldon Hull takes us through the Windows Terminal experience:
I’ve long been a Cmder/ConEmu user for Windows as it’s provided a much-needed improvement of the standard Windows terminal.
I’ve started trying to use the newer Windows Terminal project to benefit from the improved performance and support, and found getting it up and running with any customizations was a little time consuming and confusing. This wasn’t something I’d hand off to someone who wasn’t looking for experimentation.
Click through for a few scripts and screenshots.
Comments closedJason Brimhall dumps a server-side trace in favor of extended events:
Some time ago, I wrote an article for SQL Server 2008 to help determine the use of the server since SQL Server 2008 was reaching End Of Life. In that article, I shared a reasonable use of server side trace to capture all of the logon events to the server. Afterall, you have to find out the source of connections and who is using the server if you need to migrate it to a newer SQL Server version. You can read that article here.
Soon after, from various sources, I received requests on how to perform a logon audit using the more preferred, robust, venerable, awesome tool called Extended Events (XEvents). In response, I would share a login audit session to each person. In this article, I will share my login audit solution and give a brief explanation. I use a solution like this on more than 90% of my client servers and I find it highly useful.
Click through to see how.
Comments closedGokul Kamaraj, et al, describe tools available to DBAs in the Cloudera Data Platform:
Cloudera provides multiple mechanisms to allow backup and recovery, including:
– Snapshots
– Replication
– Export
– CopyTable
– HTable API
– Offline backup of HDFS dataThese can be run manually or scheduled using Replication Manager. Backups can also be moved to other instances of the OpDB or alternate storage targets such as AWS S3 or Azure ADLS gen 2.
Even in the Platform-as-a-Service world, there’s still plenty of scope for database administration.
Comments closed