6

By default SQL will not encrypt backups. From what I see in your screenshot, the backup you're taking there, will not be encrypted either. (The option is just disabled) A simple way would be to just use T-SQL to create your backup. ( Note copy_only unless you want to be starting a new backup chain) (and replace the folder with your backup dir) BACKUP ...


4

This query will show you all T-SQL modules (i.e. stored procedures, functions, etc) on a SQL Server instance that have XACT_ABORT in their code: DECLARE @cmd nvarchar(max) ; SET @cmd = N''; SELECT @cmd = @cmd + CASE WHEN (@cmd = N'') THEN N'' ELSE N'UNION ALL ' END + N'SELECT ServerName = @@SERVERNAME COLLATE SQL_Latin1_General_CP1_CI_AS , db = ''' + d....


4

The team lead is correct in the statement about memory grants given the version of SQL Server you're using (SQL Server 2014, based on the tags here). They are part of the cached execution plan, and the values are re-used across executions as long as the cached plan is in place. Improvements have been made in this area on newer versions of SQL Server: SQL ...


4

The two query plans are identical, they are trivial plans and they just use the cluster index, but the total reads and duration are different. Following the two different BlitzCache results: Total reads in that result set are most likely totalled across multiple executions. See the total rows column is 76,218 but the min, max and avg are identical to ...


3

If you plan to remain on SP2 for now, then you can install any SP2 CUs that you determine are needed. To specifically answer why you should install an SP2 CU at this point, that is somewhat of a murky answer. You don't really need to install a CU unless it has a fix you need, so if you don't have any issues that the CUs fix, then you might consider just ...


3

From comments: The built-in STRING_SPLIT() function was added in SQL Server 2016, not 2014. And for me, I go the SQLCLR route because I am not using Azure Single DB or AWS RDS and thus have no reason not to. I use the SQLCLR library that I created, SQL#, which contains String_Split and String_SplitInts (optimized for splitting a list of BIGINT / INT / ...


2

And, there several different developers which developes that CRM web app. They know the user name and password from the web config file they develope. Some of them can connect to my database with that user and execute some DDL and DML commands. And i want to block them by their client hostname. If a developer connects to db from his/her machine ...


2

First of all, it would be really great if you could provide an actual execution plan for the query you're working on tuning. Otherwise, all of the answers you will get will be somewhat general, and may not perfectly apply to your situation. The question is why, in a production environment with heavy executions, would cardinality estimates affect CPU and ...


2

While it will work, it's not necessary to move tables to a new filegroup. If you simply add a file to the databases existing filegroup(s) SQL Server will start using the new file(s). SQL Server uses a "proportional fill algorithm" when a filegroup has multiple files. So if you add a new file (perhaps on a different volume) to a filegroup, SQL Server ...


1

Sounds like DOMAIN\GroupName is a group and DOMAIN\user.name is a member of that group. This means that DOMAIN\user.name has access to SQL Server through that group. You have 2 options: Create another group, let's say DOMAIN\NewGroupName and grant that group INSERT and UPDATE, then make DOMAIN\user.name a member of that group. This is not the fastest ...


1

Your idea to move tables to a new filegroup should work. It will take those tables offline for the duration of the move, but then they will be available for queries again. You can potentially limit the amount of downtime by doing them strategically, one at a time. Note if you were on Enterprise Edition, you might be able to utilize the ONLINE index ...


1

As Vérace suggested, you can do both: create a surrogate (auto-increment) key to simplify joins and related table structures, and add a unique constraint on the two current PK columns to continue to enforce the proper data integrity. In SQL Server, multiple columns can participate in a unique index / constraint: ALTER TABLE dbo.Orders ADD CONSTRAINT ...


1

The reason the CNAME is not working is you are using a named instance. When you specify the name of the instance as in SERVER\INSTANCE the SQL Server client looks up the port number of the instance via the SQL Server Browser service. Because you are using a name that is different from the actual SQL Server instance name, that lookup fails. Instead of ...


Only top voted, non community-wiki answers of a minimum length are eligible