26
votes
Accepted
Disconnect from network without stopping the query
As the answer referenced in your question indicates, SQL Server will cancel the executing query and rollback the transaction when it detects the network connection has terminated. A SQL Server session ...
- 28.5k
13
votes
Accepted
Does taking transaction log backups every 15 minutes consume more disk space than log backups every 6 hours?
No, but it may make your transaction log smaller - so your SQL Server will use less space.
To keep things simple, say you generate 1MB of transaction log activity every minute. After 15 minutes, you'...
- 43.2k
12
votes
Disconnect from network without stopping the query
Not directly using normal tools (SSMS etc.) and access libraries - if you disconnect progress should stop and any active transactions will get rolled back.
You could run the statements via an agent ...
- 32.4k
10
votes
Accepted
Static DAC ports for named instances
Taken from the Microsoft Support site:
To configure a static port for the specialized Dedicated Administrator Connection (DAC), you must update the registry key that corresponds to your instance. For ...
- 70.5k
8
votes
Is network traffic encrypted when writing remote backups using SQL Server TDE?
Yes, backups are encrypted while moving over the network because TDE data is encrypted on disk, and the backup operation never decrypts it.
Paul Randal's Backup Myths:
Myth 30-09) backups read data ...
- 10k
8
votes
Accepted
Client application disconnects from database/s it is connected to after 10 minutes of inactivity
pgAdmin keeps throwing up a connection timeout every 10 minutes or so.
I would think that the problem might be related to a router (or, in some cases, a stateful firewall) between your pgAdmin client ...
- 13.6k
8
votes
Cannot give NT SERVICE\MSSQLSERVER permissions on network drive
The accepted answer is wrong. The 'NT SERVICE\MSSQLSERVER' is not a local service account, it is virtual account.
Virtual Accounts are running tasks in the context of the computer they are installed ...
- 181
7
votes
Accepted
Always On Availability Groups Network Configuration in AWS
In AWS EC2, you don't get the ability to add additional network ADAPTERS to your VM.
You can add additional network INTERFACES, which are just different IPs/subnets/routes/etc, but they're using the ...
- 43.2k
7
votes
Accepted
How to connect to sql server with dynamic IP Address
Instead of connecting via IP address, connect to your machine's name. If you're connecting locally, you can use LOCALHOST as the name, or just a period ("."), which connects you to your local default ...
- 43.2k
6
votes
Does taking transaction log backups every 15 minutes consume more disk space than log backups every 6 hours?
There's a little bit of overhead in each transaction log file written out to disk, even if no changes occurred in the DB. In some databases I have (SQL Server 2014, compression turned on for backups), ...
- 2,193
6
votes
Full Back up over to Network failed due to unknown network error 59
If you are having network reliability issues:
speak to your network/SAN team (and/or use a different file share that doesn't exhibit the problem)
Back up the file, temporarily, to the local SQL ...
- 182k
6
votes
Accepted
Full Back up over to Network failed due to unknown network error 59
This answer provides great advice on how to work around this issue. I thought I'd add an answer to provide some more technical details.
According to Microsoft's err.exe utility, error 59 is defined ...
- 70.5k
6
votes
Accepted
Dynamic Ports are being set to different ports
The point of dynamic ports is that you don't care what port the server runs on. SQL Server Browser directs clients to the dynamically chosen port.
If you don't want dynamic ports, just assign a ...
- 70.5k
6
votes
Accepted
Are CNAME aliases for connection strings a good idea?
One minor drawback would be that you would need to add a Subject Alternative Name (SAN) to the certificate that is bound to SQL Server if you are using encrypted connections. This could be a ...
- 7,984
5
votes
How fast can I pull data through a SQL Server client connection?
Some things to try:
Wider data sets - if you're using a narrow table (just a few fields), SQL Server may be waiting for the client app to ack the data and request the next set of rows. Since you're ...
- 43.2k
5
votes
Connection to localhost dramatically slower than to network server
Try enabling TCP/IP on your local instance:
Open SQL Server Configuration Manager (mmc.exe -> [Ctl] + [M] -> Add "SQL Server Configuration Manager" snap-in)
Expand "SQL Server Network Configuration"
...
- 432
5
votes
Accepted
SQL Server - login failure every day at same time with the service account
Using an Extended Events session I was able to track down the issue and see that it came from the syspolicy_purge_history job that SQL Server creates by default to clean up Policy Management records. ...
- 2,053
5
votes
Accepted
Which TCP port to choose for SQL Server to listen to for security reasons?
This question may be better suited to https://security.stackexchange.com/ as the idea and implications are not at all specific to SQL Server, they apply to any other internal service.
Changing the ...
- 32.4k
5
votes
Accepted
What ports does SQL Server use on Linux?
Assuming the defaults, there are two ports SQL Server listens on which can be verified with netstat -ln,
ms-sql-s TCP 1433 for the server
ms-sql-m TCP 1434 for the Dedicated Administrator Connection ...
- 64.7k
4
votes
Adding secondary replica from different subnet for AlwaysON
IP address for each subnet. Documented here: https://msdn.microsoft.com/en-us/library/hh213080.aspx#Prerequisites
- 674
4
votes
Accepted
Will matching SQL Server network packet size to mtu increase performance
TLDR: set it lower if it helps you, monitor your packet sizes to see if you are sending more packets than you need.
The MTU controls how large each network segment can be on all the points between ...
- 755
4
votes
Need SQL Server VLAN advice.. is it best practice to have all SQL Servers under 1 VLAN/network zone?
Is it a best practice to have all SQL Servers in 1 or 2 VLANs within your businesses network?
This is enterprise and personal preference. Some enterprises setup areas for specific things, some don't. ...
- 35.6k
4
votes
Accepted
Why changing the order of network protocols does not make any sense?
I believe the issue has to do with what client is used when establishing connectivity to Server B from Server A rather than how you order your protocol order on the server itself.
The first hint comes ...
- 9,507
4
votes
Accepted
Connecting to a remote SQL named instance with named pipe
Although the named instance TCP port 50002 was blocked, port 445 is used for named pipes (SMB) and is apparently allowed by the firewall.
The SqlClient driver tries TCP/IP first and then falls back ...
- 28.5k
3
votes
MSSQL Mirror Configuration Troubleshooting: Witness server forcibly disconnecting every 100 seconds
We reported the issue to Microsoft support and got it fixed. I have also tested SP1 CU1 and the database mirroring error messages have disappeared. So I think that all is good.
https://support....
- 1,415
3
votes
SQL Server Express 2014: Network Access
SQL Express installs by default with a named instance of SQLEXPRESS. See https://technet.microsoft.com/en-us/library/ms143744(v=sql.90).aspx as it applies to SQL Express 2005. The settings you give ...
- 196
3
votes
Accepted
If shared memory, named pipes, TCP/IP network protocols are all enabled which has priority?
If no protocol is specified , the connection will try based on the order. This is specified in the Enabling a Protocol section in Choosing a network protocol
- 3,322
3
votes
Is network traffic encrypted when writing remote backups using SQL Server TDE?
... is the backup operation data encrypted as it travels from computer A to be written to disk at computer B?
Yes, it is decrypted when it enters the buffer pool and encrypted when it leaves. In this ...
- 35.6k
3
votes
IP configuration for MultiSubnet AAG setup
There are a few things going on in your question. To start I presume you are referring to an Always On Availability Group, just to be clear. You shared a link about multi-subnet failover.
Multi-...
- 18.2k
3
votes
Multi-homed SQL Server with High Availability Groups
Is there any way to set up the Failover Cluster and High Availability
Group to tell it: Send synchronous replica traffic for SQL-ATL01 <-->
SQL-ATL02 over 192.168.99.x Send asynchronous replica ...
- 47.7k
Only top scored, non community-wiki answers of a minimum length are eligible