A Fine Slice Of SQL Server
Kenneth Fisher has evil afoot:
Unfortunately, if there is a problem with the server / server name the sp_addlinkedserver doesn’t seem to throw an error and sp_addlinkedsrvlogin throws an error that breaks me out of the try block. So after my Google-fu failed me I did what I frequently do and asked on #sqlhelp. And I was given an answer that led me to the solution.
Click through for the solution. But not for the answer as to why he’s growing batches of monsters linked servers.
In this blog post, I discussed some facts about Azure SQL Database Serverless. If someone wants to avoid those limitations and disable ‘Auto-pause,’ how costly is it?
Click through for a sample calculation to understand the trade-off between faster performance and lower bills.
Comments closedGarry Bargsley shows us three methods for creating schema-only clones of databases:
Have you ever been asked to make a schema only copy of a database? What is your preferred method to complete this request? Below we will discuss how to create a schema only database copy.
Here are three approaches that are easy to understand and perform. One approach will use PowerShell and the dbatools module. The second method will use built-in SQL Server functionality, depending on your SQL Server version. The third way will use a third-party (paid) tool from Red-Gate software.
Click through for the three options.
Comments closedDavid Klee continues a video series:
If you followed the last video, I showed you how to set up windows perfmon for ongoing performance metric collection on all of your critical SQL Server machines. However, having this raw data in a portable format doesn’t mean you have an easy means to access that data. In this training video, I show you a PowerShell script that we released that helps you export this data into a SQL Server database table so that you have access to the raw data to perform ongoing performance analysis with whatever favorite tool you prefer.
David walks through the process in a video, so check it out.
Comments closedTaiob Ali gives us a briefing summary on Azure SQL Database Serverless:
Occasionally, load balancing automatically occurs if the machine cannot satisfy resource demand within a few minutes. For example, if the resource demand is 4 vCores, but only 2 vCores are available, it may take up to a few minutes to load balance before 4 vCores are provided. The database remains online during load balancing except for a brief period at the end of the operation when connections are dropped.
Click through for more points along these lines.
Comments closedPaul Randal takes us through a change to SQL Server 2019:
After beating my head against the proverbial wall for an hour, I wondered if I had the wrong symbols somehow. I checked with the excellent SQLCallStackResolver tool from GitHub (authored by Arvind Shyamsundar from the Product Group) and that worked fine with the symbols I had, so it had to be something within SQL Server.
Read on to see the answer.
Comments closedMichael J. Swart applies Swart’s 10% Rule to maximum simultaneous user connections:
The maximum number of user connections that SQL Server can support is 32,767. That’s it. That’s the end of the line. You can buy faster I.O. or a server with more CPUs but you can’t buy more connections.
I actually mentioned this limit in the post where I introduced Swart’s 10% rule: “If you’re using over 10% of what SQL Server restricts you to, you’re doing it wrong” In that post, I was guarded about that statement as it applied to the user connection limit. But I’d like to upgrade that to elevated.
This is Threat Level Vermillion, people!
Comments closedJack Vamvas shows how you can check on the SQL Server Agent status using T-SQL:
I want to check if SQL Server Agent is running using t-sql. I know I can check through the services.msc or other methods such as Powershell scripts to report on the SQL Server Agent status , but the requirement is to extract this information via t-sql. Do you have a script using t-sql to get the SQL Server Agent Status?
There is a method and Jack shows us the way.
Comments closedGarry Bargsley gives us two methods for copying an Azure SQL Database:
Copying an Azure SQL Database is a vital skill when managing cloud databases. Recently, a request was received from the “business”. They wanted to create a copy of an Azure SQL Database that was in a development environment. The database has been certified and early testing was accepted. They now want an exact copy in QA to start integration testing. The process of making an Azure SQL Database copy is straightforward. There are several different ways to perform this action.
Two methods chosen will use the Azure Portal and PowerShell to demonstrate the completion of this request.
Click through for the demos.
Comments closedAmeena Lalani shares some good info on the tempdb database:
Tempdb is a SQL Server temporary system database. Everytime SQL Server restarts, new Tempdb database is created. Whatever data was stored in the tempdb is lost. When TempDB gets recreated its data and log files reset to their last configured size. SQL Server records only enough information in the tempdb transaction log to roll back a transaction, but not to redo transactions during database recovery. This feature increases the performance of INSERT statements in tempdb. Tempdb database is always in Simple recovery mode. If you try to change it to Full Recovery mode, you will receive the following error message.
Click through for more info on how the database is special, types of issues you can run into as concurrency grows, and ways to resolve those issues.
Comments closed