11

No, this is not possible.SQL Server 2017 backups cannot be restored by any earlier version of SQL Server ref Also, regarding detatching and reattaching per the docs: After being attached to SQL Server 2017, the database is available immediately and is automatically upgraded. This prevents the database from being used with an older version of the ...


10

Memory architecture was changed in SQL Server 2012 such that there was little need to worry about the MemToLeave setting anymore, especially if using 64-bit SQL Server. And, starting with SQL Server 2016 (which you are using), SQL Server is only available in 64-bit (see the "Note" at the top of the "What's new in Database Engine - SQL Server 2016" page). So, ...


9

You have a quorum issue in this setup. There are only two participants in the cluster: SQL1 SQL2 If SQL1 goes down, there's no way for SQL2 to know that it should be the primary. For all SQL2 knows, the network has gone down and SQL1 is still operating as the primary. Thus, SQL2 goes to the RESOLVING state (it's not sure what to do). This is to prevent ...


8

You could create some auditing on the execution of the sys.sp_query_store_force_plan procedure. Create the server audit USE master ; GO -- Create the server audit. CREATE SERVER AUDIT ServerAudit TO FILE ( FILEPATH = 'D:\AuditFiles\' ) ; GO -- Enable the server audit. ALTER SERVER AUDIT ServerAudit WITH (STATE = ON) ; Create ...


7

Might look into this: https://docs.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide. Especially the query on what's currently allocated. It will give you somewhere to focus diagnostics on. I've had this problem before, and it ended up being repeated calls to a CLR procedure that would never close itself down when it was ...


7

When an application establishes a TCP connection, it specifies a port for the inbound (receive) port, and uses a (somewhat) randomly selected port for outbound (source, or send). The inbound port for AG replication is 5022 by default, so all replication traffic is sent TO port 5022. Accordingly, you see the network reads occurring on port 5022, as SQL ...


5

"Very few syntax changes" -- Excuse my while I laugh! SQL Server has a lot of features that MySQL does not have. Any use of such is not just a syntax change, but possibly a query rewrite. TOP 10 --> LIMIT 10 -- but only approximately. ROW_NUMBER --> does not exist SEQUENCE --> AUTO_INCREMENT -- but only approximately dbname.dbo.tablename --> dbname....


3

Since this not production, it's not so critical to do this the "right" way. Having said that, running QA the same way as production is beneficial for ensuring everything goes well in production. "Best practices" would be to gracefully shutdown each VM prior to rebooting the host. That allows the AG to realize the databases need to fail over to the ...


3

Specifying Failover Partner in the connection string is only for an initial connection. After connecting the client is notified of the failover partner, and will use that for reconnecting in case of a failover. [After connecting to the principal] the data access provider downloads the server instance name of the current mirror server. This name is ...


3

Variable length columns only store the actual data present in the column. In other words, the defined length of a varchar column has no bearing on the amount of physical storage allocated. However, querying the data requires a memory allocation that is commonly calculated to be the number of rows expected multiplied by 50% of the defined column size. So, ...


3

Adding to the answer of Shekar Kola. Personally I always suggest and use custom scripts over maintenance plans as these are easier to deploy on multiple servers, and easier to configure. If you're going for the route of the custom scripts instead of the maintenance plans, consider using Ola Hallengren scripts. Ola hallengren website These are some amazing ...


3

Replication is not a DR technology in SQL Server, so you should never consider that for DR purposes. In SQL 2016 Standard Edition, you should look at Basic Availability Groups. Setting up a Basic AG for this database will provide you with DR capabilities and will allow for failover without breaking the flow of data between your replicas.


3

Ideally you would normalize your data. If you can not do that, then there are at least two ways to accomplish this. DB FIDDLE For Both Examples You can view the execution plans of these simplified queries here. UNPIVOT > JOIN > PIVOT Method One way is to UNPIVOT your Table1, then JOIN to Table2, and then PIVOT back to a single row. declare @Table1 ...


3

Yes, it required license as both of the instances are active/running. Availability Group has set of databases that you want to synchronize to secondary replica. It's not necessary to add all databases to availability group, you can add the databases for which you want fault tolerance. So if your primary replica goes down only those database are part of the ...


3

You most likely created the data file with minimum size of 5GB since you stated: I have a database with five empty tables, let's say db1. The .mdf size of db1 is 5gb But you didn't say anything about INSERTS / DELETES. Despite it being empty, the data file has reserved this space on disk. You can check / ensure it is empty via SSMS. Pinal Dave Disk ...


3

Is it normal to share CPUs on a VM of SQL Server? Yeah, it's pretty common. A lot of times VMs are used to consolidate lots of SQL Servers (especially ones that don't have extreme performance requirements) onto one host. This can save on licensing costs, since SQL Server can be licensed at the host level. Is it a good idea? I mean, it depends a lot on ...


3

With Hyperthreading enabled, you currently have 4 hard NUMA nodes with 10 physical cores and 20 logical cores each. On SQL Server 2012 and 2014 you will have 4 matching soft NUMA nodes with 20 logical cores each. Upon upgrading to SQL Server 2016 or higher, auto soft NUMA will automatically kick in. You'll now have 2 soft NUMA nodes per hard NUMA node for a ...


2

I'm not aware of such tool, but you can easily create one your self (foreign-keys-in-a-sql-server-database): SELECT C.TABLE_CATALOG [PKTABLE_QUALIFIER], C.TABLE_SCHEMA [PKTABLE_OWNER], C.TABLE_NAME [PKTABLE_NAME], KCU.COLUMN_NAME [PKCOLUMN_NAME], C2.TABLE_CATALOG [FKTABLE_QUALIFIER], C2.TABLE_SCHEMA [FKTABLE_OWNER], ...


2

It is not possible. Everything includes granting access, which means they can change whatever access you give them. There are some encryption methods that can be used so DBA's can have full access but not have readable access to the data. I believe Transparent Data Encryption (TDE) is one option, but I am not an expert on it. Make a list of "everything" ...


2

Plan XML The query plan XML shows no <WaitStats> and the query time stats are <QueryTimeStats ElapsedTime="0" CpuTime="0" /> & CompileTime="7" CompileCPU="4" As far as the query plan is concerned, it should have ran instantly. The query plan shows no issues. Build version Since your build version is 13.0.4522.0 (SP1 CU10 + Security Update) ...


2

Answer content originally left in comments No, because VMWare doesn’t even see SQL Server, it only sees Windows. It schedules CPUs at the level of the VM, not at the level of a process in a VM. – Gaius You license SQL by the core... why are you sharing that with anything other than SQL? Yes, oversubscribing CPU's are normal practice for virtualization, ...


2

The process does not possess the 'SeSecurityPrivilege' privilege which is required for this operation. That's pretty cut and dry. Even if you're in the Administrators local group (which you should be to run setup) things like group policy can still be going on in the background and locking things down. All of the required permissions are not held by the ...


2

Why do we still get the highest permissions, even when AD group is removed from logins, and disabled in DB users? This is because testuser is a member of both AD\testgroup1 and AD\testgroup2 and both those groups exist in the database with object permissions, resulting in cumulative object permissions for the testuser role member. There is no ...


2

Your currently installed instance is at version 13.0.4466.4 (2016 SP1-CU7-GDR), and your installation media is at a lower patch level for the same major version (in your case, 2016 SP1, without the CU). When you install an additional component (Replication, Full-text search, etc) with install media at a lower version, the installation will succeed, but ...


2

The information required to generate the queries and the counts is all available in the catalog views, like sys.columns and sys.foreign_key_columns. We need to find all of the child tables, and then count how many rows in each child table meet the same criteria as the parent ID. CREATE PROCEDURE dbo.GeneratedPathedDeletes @ParentTable nvarchar(512), ...


2

The stored procedure DatabaseBackup is not supported on Amazon RDS. As you note, there is logic that checks for the RDS platform. If these checks return that your server is an RDS instance the following code block is encountered. IF @AmazonRDS = 1 BEGIN SET @ErrorMessage = 'The stored procedure DatabaseBackup is not supported on Amazon RDS.' ...


1

I believe, log-shipping would not work in your case (AG1 ot AG2), due to following reasons. Database cannot be restored while it's already part of AG/Mirroring Basically, Log-shipping does backups on primary replica, and restores on secondary replica with defined interval time Log-Shipping would be good choice when Secondary replica in Log shipping ...


1

The biggest advantage of putting the database files on page blobs is for very large databases is you can use File-Snapshot Backups for Database Files in Azure. But it's a more advanced and somewhat more tricky feature than using Azure Disks, which are very simple and user-friendly. So the basic guidance is to use Azure Disks, unless you have a scenario ...


1

Considering following points may help you to plan backup schedule properly: You can schedule your backups with following methods other than Maintenance plan Custom script (stored procedure) - SQL Agent Any third party tools I personally would like to go with custom script as it's flexible. Here are some basic notes about 3 type of backups (hope you ...


1

As per other comments, it sounds like the cost threshold for parallelism is too low, and is making queries go parallel even when the process to do so/ gather the streams makes your process take longer. Try setting that to 50, or experimenting with different numbers. Also, worth a read: https://www.brentozar.com/archive/2017/03/why-cost-threshold-for-...


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