All Questions
Tagged with sql-server-2017 sql-server-2016
41 questions
0
votes
0
answers
91
views
SQL Server 2016 upgrade
We need to upgrade our SQL Server 2016 to SQL Server 2019. We are also using replication for our test and production environments.
What my question is: what are the downfalls of upgrading from SQL ...
0
votes
0
answers
52
views
SSRS 2016 compatibility with SQL 2017 after SQL2016 upgrade
We have an SQL Server database engine on the 2016 version. We also have SSRS in the 2016 version. I plan to upgrade the SQL database engine from the 2016 version to the 2017 for it to participate in ...
- 5
-1
votes
2
answers
235
views
Whether SQL standard edition has NUMA support?
Link: https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-2017#RDBMSSP
For NUMA Aware Large Page Memory and Buffer Array Allocation the table ...
- 3,499
0
votes
2
answers
1k
views
AlwaysOn Availablity Groups and database count limits
Update:
Basic answer: I asked for something that isn't part of the design of AOAG.
Which in my eyes makes this a 'not a good question to ask'.
Unless this offends someone, I'll keep it here in the odd ...
- 103
0
votes
2
answers
48
views
does SQL automatic backup runs even the machine/server is offline
I am wondering if the SQL jobs like automating the database backup runs even the machine/server is offline?
- 13
0
votes
0
answers
32
views
How To Grant A Specific Login Under Acitve Directory Group To View Server State In SQL Server [duplicate]
I want to grant only a login who is under an active directory user group to view server state in sql server. How can i do it?
- 1
0
votes
2
answers
668
views
What Is The Best Way To Clone A Database From Production To Development Environment? [closed]
We use SQL Server at our company as DBMS. I have a scheduled job working monthly. The job restores every databases on production server to development server at the end of the month but there is ...
user220660
0
votes
1
answer
92
views
No One Can Login SQL Server Instance Even Though Its Services Are Running
I have an issue. Today, our developers had reported to me a problem that they couldn't reach SQL Server database server several hours ago. I have checked the services and i saw that they were in ...
- 47
0
votes
1
answer
232
views
Handling Reporting Services and SharePoint Integration considering deprecation
With one of our clients, we're currently faced with a dilemma. SQL Server Reporting Services Report Server (SharePoint mode) seems to have been deprecated, since the last version to support it is SQL ...
- 153
0
votes
2
answers
3k
views
SQL Server 2016 -> 2019 AlwaysOn Rolling Upgrade Migration
I have a migration concept with an AlwaysOn configuration from SQL Server 2016 (on windows server 2016) to SQL Server 2019 (on Windows Server 2019) which are in different sub-networks.
So with ...
- 3
0
votes
2
answers
460
views
Default trace vs auditing in SQL Server
Currently I am working in an environment that has the default trace enabled and I would like to propose that we add SQL server auditing to our environment.
I'm looking for some points to put forward ...
- 303
1
vote
0
answers
713
views
Naming Convention for SQL AD Group Logins (Potential Bug)
My team is attempting to come up with a new naming standard for our Active Directory groups within SQL Server to help manage permissions.
DOMAIN\ENVIRONMENT_DATABASE_FUNCTION
EXAMPLES:
TEST\...
- 255
1
vote
1
answer
5k
views
The OLE DB provider "MSDASQL" for linked server "(null)" reported an error
I created a linked server to MySQL database server on my SQL Server database server. I tested the connection. It succeeded successfully for MySQL connection. I can see the databases on MySQL instance ...
- 69
1
vote
1
answer
913
views
Need advice on how to migrate from existing database mirroring to availability groups
Need some practical/general guidance on moving from database mirroring to AOAG
Current setup - 2 standalone servers with database mirroring configured as async.
Server A: Windows 2016 running SQL ...
- 2,210
1
vote
1
answer
51
views
Where Database Alerts & Operators Exist In SQL Server?
My SQL Server database server has crashed. I have system databases backup. I will reinstall the sql server but i don't know how to find my operators, alerts and config settings. Is there a way to find ...
- 69
2
votes
1
answer
1k
views
How to upgrade SQL Server 2016 and 2017 Standard to 2017 Enterprise edition
We have one SQL Server 2016 Standard instance, and two SQL Server 2017 Standard instances installed on the same server. Now my management wants to install SQL Server 2017 Enterprise edition and ...
- 31
1
vote
0
answers
13
views
Is there a way to provide a client without administrator access additional information when they receive Error 18456?
When a user fails to log in to SQL Server, they receive this error:
Login failed for user 'foo'. (Microsoft SQL Server, Error: 18456)
On the client side, this error is always State 1. This is ...
- 482
3
votes
1
answer
6k
views
Limit TLS and Cipher Suites on SQL Server 2016-2019
I'm wanting to configure some SQL 2016+ servers to use only TLS 1.2+ and a subset of Cipher suite options (no ciphers considered weak or compromized). I'm having trouble tracking down how to ...
- 201
4
votes
2
answers
254
views
SQL Server 2017 Distributed Availability Groups
My Environment is SQL Server 2017 Distributed Availability Groups, details as follows:
Configured SQL Distributed AG from DC1 to DC2.
DC1 (Production): TEMPDB1 AND TEMPDB2 (HOSTED ON WSFC1)
DC2 (DR)...
- 65
1
vote
1
answer
126
views
Compatibility level of databases based on sql server version
We need to check for databases that are not in the correct compatibility level on a particular instance. For example, on a SQL Server 2016 instance, if there are databases that are not in ...
- 11
0
votes
2
answers
522
views
How to find Sum of dynamically generated time attribute in sql server 2012
I have a table with two columns (TASKID and Dateadded). I am trying to find difference between timings and would like to calculate the sum of the diff (time) attribute.
select taskid,dateAdded,CAST((...
13
votes
1
answer
2k
views
Temporarily moving a SQL Server 2016 database to SQL Server 2017 and then moving back. Is it possible?
If I take a backup of a database from a SQL Server 2016 instance and then restore it to a 2017 instance to do some work on it.
Can I then turn around and back up that database from the 2017 instance ...
- 241
1
vote
1
answer
2k
views
Error after SQL-Server Update: The prepared statement with handle xyz was not found
In the last weeks we updated two of our customers. Both got a new SQL-Server and both have now this error: "The prepared statement with handle xyz was not found."
The error occures several times ...
- 11
2
votes
2
answers
992
views
Adding a database with Service Broker to an existing Availability Group on a new replica
Server 1 - SQL Server 2016 (Primary in existing AG)
Server 2 - SQL Server 2016 (Secondary in existing AG)
Server 3 - SQL Server 2016 (Offsite Secondary in existing AG)
Server 4 - SQL Server 2017 (New ...
- 23
0
votes
0
answers
63
views
How to fix ERROR_SCRIPTDOM_NEEDED_FOR_SQL_PROVIDER error
I'm getting below error while executing PS script
Error Code: ERROR_SCRIPTDOM_NEEDED_FOR_SQL_PROVIDER
More Information: The SQL provider cannot run because of a missing dependency. Please make sure ...
- 135
5
votes
2
answers
4k
views
"the maximum number of '1' dedicated administrator connections already exists" message in error log when connecting to DAC from Powershell
I'm connecting to SQL Server (2016 and 2017 latest builds) via the Dedicated Administrator Connection (DAC) from PowerShell.
The following error message is recorded in the SQL Server Error Log:
Date ...
- 217
0
votes
1
answer
255
views
Incorrect GrantedQueryMemory(KB) Value in SSMS Dashboard report
Just checking by to see if someone else has witnessed the same and tried a fix or has been fixed:
Issue:
I ran below query on stackoverflow2013 database
select
ParentId as [Post Link],
count(...
- 794
2
votes
0
answers
368
views
Aggressive Tempdb spill process
We are having an issue with aggressive tempdb spills that significantly effect overall server performance and, in rare cases, cause AlwaysOn failures.
Tested on SQL2016 and SQL2017 Enterprise (recent ...
- 165
1
vote
2
answers
241
views
Upgrade SQL Server 2014 AG to SQL Server 2017 AG
Is it possible to migrate SQL Server 2014 AG (Windows 2012, SQL Server 2014) to SQL Server 2017 AG (Windows 2016, SQL Server 2017) using rolling upgrade method?
What I mean is
Build 2 SQL Server ...
- 673
2
votes
3
answers
2k
views
Is "SET IDENTITY_INSERT" required to insert arbitrary GUID into [uniqueidentifier] column?
SQL Server 2016 and 2017, Standard Edition
I need to insert raw data (from another table) into a table that has two uniqueidentifier columns.
Does this require that I use SET IDENTITY_INSERT Carrier ...
6
votes
2
answers
6k
views
How to create a synonym for a column in SQL Server?
The following is not working. I want to create a synonym on a column in SQL Server 2017. How would I go about doing this?
CREATE SYNONYM dbo.[ProductTest] for [dbo].[Product].[ProductId]
GO
select ...
user168914
1
vote
0
answers
116
views
How to check the errorlog for important messages
I would like to receive an email every day from each of "my" SQL Servers, with information from the ErrorLog.
I would like an easy way to determine if there is any important new information, that I ...
- 2,239
0
votes
1
answer
325
views
Behavior Differences Running SQL 2017 in 130 Compat Mode vs. Running SQL 2016 in 130 Compat Mode?
We have a new application which includes ETL scripts, R, and .NET code all actively being developed on SQL 2016 architecture.
I just recently received approval to start setting up new environments ...
- 87
4
votes
1
answer
2k
views
Mixing SQL Server 2016 and 2017 in a distributed availability group
I have an old Availability Group made up of 2 SQLServer 2016 instances (2 Windows 2016 servers in WSFC)
I also have 2 new SQLServer 2017 instances (2 Windows Server 2016) that I originally wanted to ...
- 43
14
votes
2
answers
14k
views
Error updating temporal tables
I have implemented the System Versioned Temporal Tables on the table "Orders". The application is using different access patterns to modify the data in this table. There are direct statements coming ...
- 1,428
2
votes
1
answer
1k
views
Make encrypted differential backup to same media
I create encrypted backup as follow:
BACKUP DATABASE XXX
TO DISK = 'C:\Backup\XXX.bak'
WITH INIT, FORMAT,
ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = MyCertificate)
Then I try to ...
- 123
1
vote
0
answers
717
views
SQL Transactional Replication - some tables frozen, but others working fine
Background
We are "republishing" 20 SQL Server 2012 source databases, using SQL 2016 as the republisher and subscribers, with SQL 2017 acting as the single distributor for both "hops" (Hop 1 being ...
- 662
10
votes
2
answers
2k
views
Select TOP N Rows missing from SSMS temporal table
I am using temporal tables within my database and when I right click on my table in Management Studio 2017 (v17.4 14.0.17213.0) I do not see the Select Top 1000 Rows in the context menu (there is no ...
- 103
2
votes
1
answer
667
views
Is there a service broker target for an extended event session
We use Service Broker heavily for asynchronous background processing.
Additionally we want to use it for extended event processing.
Is it possible to use Service Broker listener as target for an ...
- 120
5
votes
1
answer
559
views
Is it possible to create encrypted backups using encrypted by password certificate?
I have encrypted by password certificate, which I want to use for backup encryption. The issue is I am not able to use the certificate (in T-SQL statement or maintenance plan) because I am not able to ...
- 4,318
5
votes
1
answer
43
views
Lifespan of Launchpad-Launched Exterrnal Runtime Processes
When SQL Server uses SQL Launchpad to launch an external runtime (e.g. R or Python) when sp_execute_external_script is used, what's the lifespan of the external runtime process?
Does each ...
- 659