Press "Enter" to skip to content

Category: Administration

Is sysname Case-Insensitive?

Solomon Rutzky tries Betteridge’s Law of Headlines:

Over time I’ve used a variety of SQL Server versions with a variety of instance-level collations. This has lead me to conclude that sysname had to be all lower-case in some earlier versions if the instance-level collation was either binary or case-sensitive. However, it was not clear exactly which versions and what scenarios truly affected the behavior of sysname name resolution, so I played it safe and continued to always specify that particular data type in all lower-case, even leaving comments in scripts that it must remain as all lower-case (just in case someone goes through and tries to make it upper-case to be consistent with the other data types).

But then, while researching another topic, I recently found the following in some old documentation ( Breaking Changes to Database Engine Features in SQL Server 2005 ):

Read on for the results of Solomon’s archaeological expedition.

Comments closed

Optimizing for Ad Hoc Workloads

Chad Callihan explains the importance of a feature:

Anytime you run a query, SQL Server needs to build an execution plan to use as directions for best executing that query. These execution plans can be stored in your plan cache to be reused in the future if that same query is ran. Instead of resources going into rebuilding the plan each time, SQL Server can use the same plan as the previous execution. This is great for queries that run over and over. On the other hand, what if you have a large number of queries that will run once but never again? Plans never to be reused are taking up valuable space in plan cache. If this looks like your workload, consider enabling the Optimize for Ad hoc Workloads feature.

Click through to learn more about the feature. I haven’t seen many (any?) cases where Optimize for Ad Hoc Workloads doesn’t help at least a little on net.

Comments closed

SQL Server: Side-By-Side Install or Direct Upgrade?

Kenneth Fisher lays out preferences between two upgrade paths:

Most discussions like this start with the fact that an in-place upgrade is far easier but riskier if anything goes wrong. Side-by-side is more work and you run the risk of forgetting something. But if something goes wrong you just move back to the old instance.

I thought about something that isn’t usually part of the discussion this week while working on a side-by-side migration.

Read on for Kenneth’s insight. My general preference is side-by-side updates on a new server, as that helps get rid of operating system bit rot as well.

Comments closed

Using Ola’s Maintenance Solution on RDS

Jack Vamvas takes us through a couple of nuances around using Ola Hallengren’s SQL Server Maintenance Solution on Amazon RDS:

I’ve used the Ola Hallengren Maintenance Solution across various SQL Server environments . I was recently asked by a colleague about how adaptable they are to the AWS RDS SQL Server environment. 

I checked the Ola Hallengren FAQ and there is a comment :

Read on to learn the details.

Comments closed

SchemaDrift Available in Beta

Kiana Bergsma announces a new tool:

Save yourself Time and Money with Steve Stedman’s new database comparison tool. Following in its cousin’s (Database Health Monitor) footsteps, SchemaDrift is FREE! It is currently in Beta phase but it is free to download for personal or business use.

We only ask that you give us feedback. Let us know what you like and dislike. How can we make this product even better and we’ll send you emails on update releases. Comment down below or message us through our website.

Click through for a download link, as well as a FAQ in video form.

Comments closed

Hyperconvergence and SQL Server

Robert Sheldon gives us a primer on hyperconverged infrastructure:

A growing number of organizations have deployed hyperconverged infrastructure (HCI) systems in an effort to simplify IT operations, better utilize resources, and lower costs. They might house the systems in their own data centers, colocation facilities, edge environments, or office closets. Regardless of the location, many of the organizations are running SQL Server on their HCI systems, often alongside other applications. Although it means deploying SQL Server to a virtualized environment, such a practice has become fairly common, especially with the advent of the cloud. This article covers hyperconvergence, another option for SQL Server.

Despite how common hyperconvergence has become, some IT teams might still not be familiar with HCI or are familiar with HCI but have not deployed SQL Server to an HCI platform. In either case, they might now be considering HCI for SQL Server and need to better understand what this looks like before deciding on new infrastructure. Although HCI can make it easier to provide a platform for SQL Server, decision-makers should know what they’re getting into before going down this route.

Click through to learn more about hyperconverged infrastructure and where it can help (or hurt).

Comments closed

A Checklist for Database Post-Restoration

Randolph West wants you to keep some things in mind after you restore that database:

Whenever I restore a database — especially one I obtained outside of my regular environment (for example a customer database, a development database, or even a sample database like WideWorldImporters) — there are a few things I like to check to make sure it’s configured for peak performance.

Note that some of this advice may apply only to non-production databases.

Click through for the list.

Comments closed

Setting up a Full-Text Index in SQL Server

Steve Jones walks us through setup for a new full-text index in SQL Server:

A full text index allows you to search a little more freely than standard T-SQL with a LIKE or wildcards. It’s useful for going through large amounts of text, mainly hundreds or thousands of words.

To get started, you need to know a few things. First, this system in modern SQL Server (2008+) is set up on all instances. You don’t enabled FTS like you would for In-Memory OLTP tables or FILESTREAM.

Next, you need a catalog for the FTS indexes, which is a logical container.

Next, a table with data.

Finally, you create the index. In this post, I’ll look at SSMS and the GUI. In another one, I’ll look at the T-SQL itself.

With all that in mind, click through to read Steve’s post and set up your own full-text search process.

Comments closed

Developing a Patch Strategy

Brent Ozar shares a patching strategy:

Decide how you’re going to detect problems. Every now and then, an update breaks something. For example, SQL Server 2019 CU7 broke snapshotsSQL Server 2019 CU2 broke Agent, and so many more, but my personal favorite was when SQL Server 2014 SP1 CU6 broke NOLOCK. Sure, sometimes the update installer will just outright fail – but sometimes the installer succeeds, but your SQL Server installation is broken anyway, and it may take hours or days to detect the problem. You need to monitor for new and unusual failures or performance problems.

Click through to see the high-level strategy elements.

Comments closed