A Fine Slice Of SQL Server
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 closedSteve 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 closedJack Vamvas shows us how to check the status of a Windows service using Powershell:
Question: How can I check for the SQL Server Reporting Service service status with Powershell? The status checks should be for remote servers
Click through for the answer.
Comments closedBrent 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 snapshots, SQL 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 closedDeployment is a crucial move in the ML workflow. It is a mark where we want to implement our ML model into utilization. Later, we can practice the model in practical life.
But how can we design the model as a treatment? We can develop an Application Programming Interface (API). With that, we can reach the model universally, can be a mobile application or web application. In Python, there’s a library that can assist us in building an API. It’s named Flask.
This article will explain how to construct a REST API for our machine learning model utilizing Flask. Without further ado, let’s begun!
Flask is the first step, but then I’d want to reverse proxy it with gunicorn or Nginx afterward.
Comments closedJeff Iannucci adds on to tempdb configuration advice:
Step 1: Provision a separate drive. Put your TempDB away from your precious user data files, as well as the other system databases. This probably isn’t your job, so ask your friends on your infrastructure team to provision and mount a T: drive or whatever of a size that you gauge appropriately. What’s appropriate? Hey, I don’t know your system – that’s why they pay you the big bucks, friend.
Read on for the full set of tips.
Comments closedSteve Hughes hits on one of the tricky administrative bits of Azure Synapse Analytics:
So you can copy Azure SQL Database using the Azure Portal, PowerShell, Azure CLI, and T-SQL. However, this functionality is limited to Azure SQL Database and does not work for Azure Synapse databases (a.k.a. SQL Pools). Early in 2021, the ability to use the copy functionality to copy databases between subscriptions is also supported but requires security work to make sure the permissions in the database servers and networking allow that to happen.
There’s a lot involved in the process, leaving me to provide the sage wisdom that it’s easier not to put it in the wrong subscription to begin with if you can avoid it.
Comments closedDeepthi Goguri summarizes a detailed session from Bob Ward:
While I was preparing for my Tempdb presentation, I learned many interesting facts about Tempdb. Thanks so much Bob Ward (t|g) for providing me with the resources to prepare for my presentation. Bob Ward has presented an amazing 3 hour session about Tempdb for the PASS Summit couple of years ago. This information is invaluable.
Read on for 15 notes of interest.
Comments closedAlex Stuart shows how to fail over SSISDB in SQL Server 2012 or 2014:
Hopefully not many people are still configuring SSIS instances on SQL 2012 or 2014 – especially HA instances – but if you are, this post is for you.
If you’re running SQL Server 2016 or above, having the SSIS catalog function correctly in an AG is supported by built-in functionality to manage the DMK (database master key). In 2012/2014 however there is no such support. Without intervention this makes SSISDB unable to be opened after a failover, because the DMK isn’t open – leading to errors such as “Please create a master key in the database or open the master key in the session before performing this operation.”
Read on to see how to resolve this error, and then how to do this automatically.
Comments closedKenneth Fisher asks the important Stockdale questions (Who am I? What am I doing here?):
It works when I run it this way™ but not when I run it through xp_cmdshell!
It’s a permissions issue. When you run xp_cmdshell you are running under the ??? account.
Little bit more detail. The extended stored procedure xp_cmdshell creates a windows command shell. This shell has to be run under a windows/active directory account. Obviously you can’t get access to a windows resource (a directory for example) using a SQL Server login. The trick is to be able to tell them what account xp_cmdshell is using within that shell. There are two possibilities here.
Read on to learn about those two possibilities.
Comments closed