Hot answers tagged

25

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 needs a connected client to query return results and messages (informational, warning, and error messages). You have a few options to maintain the connection/...


22

As mentioned by Nick and Martin, the eventual status of your query depends on whether SQL Server knows about your network cable pull before the query completed. From Books Online (though I find it interesting that there are equivalent topics for this in 2000, 2005, 2008, and 2008 R2, but not 2012 or 2014): If an error prevents the successful completion of ...


15

If client takes long time to receive data and in turn send acknowledgement to SQL Server that it has received the data SQL Server has to wait, due to this wait SQL Server will not release the locks held by the query unless acknowledgement is received from client. This is not accurate, it is dependent on the isolation level. At the default READ ...


13

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've generated 15MB of log activity - but that also means that your transaction log will need to be at least 15MB large (assuming that you're in full recovery ...


12

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 job set to start immediately, assuming that this is configured and your login has sufficient privileges.


10

http://dev.mysql.com/doc/refman/5.6/en/innodb-implicit-commit.html says: If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction. That means if your session disconnects for any reason, either by choice, or else because an error occurs like the network connection fails, etc., ...


10

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 example, the registry key may be the following: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\SuperSocketNetLib\...


9

Your assumptions are incorrect. The slave does not bind to 3306 (or whatever port it is listening to) when making its outbound connection to the master, and the port the slave is "listening to" for inbound client connections is otherwise irrelevant in replication. The slave establishes a TCP connection from a high port to the master's port (e.g. 3306), ...


9

Mark's answer cleared up a lot of my confusion, but I wanted to post my findings after I tested this using NetBalancer to emulate latency. I had my local machine call a remote SQL server and execute both SELECTs and INSERTs on a table within a small transaction. On the remote machine, I connected to the local SQL instance and used a WHILE loop to ...


8

@Aaron is correct. Creating a transaction before your commands is your best bet. If you can't remember to do that then one option is to go into the Tools-Options setting and turn on SET IMPLICIT_TRANSACTIONS. This will automatically start a transaction as soon as certain commands are run. This includes UPDATE, DELETE etc. This appears to be a fairly ...


8

Not aware of any built-in method to record bytes over a particular port so if you really need that level of detail, Wireshark is a good bet. If the server is dedicated to SQL Server I'd be reasonably confident that the majority of traffic over the network was related, so the perfmon counter Network Interface\Bytes Total/Sec should give you a broad guideline....


7

have fixed sqldwdev01 the reason I couldn't connect to UAT instance is because of a couple of reasons there was a dynamic port specified for the instance under SSNetworkConfig\Protocols for UAT, so had to remove that and specify 1435 (as 1434 is in use) then restart the service still cant connect remotely to the instance name but can connect using the port ...


7

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 through the buffer pool No. The backup subsystem opens its own channels to the database files to avoid the performance hit of having to read everything into SQL ...


7

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 instance.


6

Changing the IP Address for a machine running SQL Server is not a problem at all. You'll need to restart the SQL Server after changing the IP Address so that the SQL Server will start listening on the new IP address. But that's it. If no users actually connect to the machine then there's nothing else to do. If users did connect to the machine they'd need ...


6

As I understand it, you want to monitor network traffic in between a web application hosted in IIS and SQL Server, both of which are running on the same server. This is going to be complicated by a few things: Most packet sniffers don't support monitoring traffic over a loopback interface like you are using if your web app connects to SQL Server on ...


6

The /128 in the address is the NUMBER OF BITS to match... not a range. So you would want 10.8.101.0/24 (match the first 24 bits). As an additional example, if you wanted to restrict it to 10.8.101.128 to 10.8.101.255 then you'd do 10.8.101.128/25 Please see the address subsection in the documentation


6

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 same underlying network adapters (cards). You can choose to use instance types with faster networking ports, though. The EC2 instance configuration page lists ...


6

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), transaction logs written during periods of inactivity are generally 9.5 kb per TRN file. But other than that overhead, the transaction logs are going to ...


6

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 port at the outset, then it will never change.


6

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 significant pain if you have a "shared" server that hosts a number of smaller applications and you want to set up an alias for each application. If you are using a ...


5

An MDF files is not the database, as such. It is merely the on-disk storage used by the SQL Server database engine. When your app "uses" the MDF, it is actually attaching them to an SQL Server install and running queries against that. This is transparent really. Instead of this, you can install SQL Server on the network (even Express edition), attach tehe ...


5

The DMV sys.dm_exec_connections has per-connection statistics for traffic, including bytes send/read. But it gets reset every time a physical connection is closed and also is a bad indicator of throughput, specially spikes. That being said, traffic between client and server should never be a concern. Specially for a web app, where large return sets are not ...


5

Most likely, it will finish a DDL command behind the scenes, but I wouldn't bet my job on it. What if your CTAS action runs out of tablespace? You would never see the error. If you have an unrealiable Internet connection, then the best solution is probably Simple/basic solution Use VNC or RDP to "jump" to a desktop or server that is in your datacenter. ...


5

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 Server system, assuming there is adequate space. Then copy the backup to the network share, typically using a tool that can resume interrupted transfers, and delete ...


5

Aaron's 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 in winerror.h as: An unexpected network error occurred. If this was a problem with the session timing out, you would almost certainly be seeing ...


5

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 SQL Server instance port generally adds very little to security - it is a "theatre" action rather than a real security benefit, something to put on a checklist to ...


4

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" Click on "Protocols for MSSQLSERVER" (or whatever your local instance name is) Set "TCP/IP" to Enabled I just ran into this problem tonight. Contrary to @...


4

I certainly wouldn't want to include the server name in the TNS alias. It would seem highly probably that this would change over time as databases move from one server to another and as organizations move to things like RAC where there would be multiple servers. Assuming that your service names are chosen meaningfully, I would expect that the service name ...


4

Databases require certain guarantees from the storage it runs on to implement ACID. Unfortunately, it is not always possible for the database to determine if those guarantees are met. Here are some requirements that the storage (no matter if it is NAS, direct attached or SAN) must meet: Write Ordering: Writes my happen in the order they are confirmed to ...


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