Forum Discussion
Troubleshooting relocated database
Hi...
First time post to this forum. I've seen a strange characteristic after relocating a database from one server to another.
Overview:
My database SampleDB (data and log) reside on a SAN storage volume that is connected via fiber to the physical server that is running SQL Server 2014. I currently have 2 physical servers (Host1 (Server 2012 Standard) & Host2 (Server 2019 Standard)) that are running SQL Server 2014 Enterprise.
Initially the SampleDB volume (with DB files) was connected to Host1. The SampleDB volume (with DB files) is now connected to Host2. Primarily everything appears to be working. Particularly as I issue commands from the SQL Server Management Studio on Host2.
Migration Steps Completed:
Host1: Took SampleDB offline.
Host1: Detached SampleDB.
Host1: Detached volume from SAN.
Host2: Attached volume from SAN.
Host2: Attached SampleDB.
Host2: Various admin tasks, such as updating User rights to newly attached SampleDB.
Host2: Ran various queries against database and all looks good.
Application Interface:
I have a thick-client .NET application that interfaces with backend web-services. Those backend web-services interface with the database using stored procedure calls. I updated the backend services to point to the SampleDB on Host2. Restarted all the services and started up my thick-client .NET application. One of the first steps in the thick-client application is to login with a username/password, the credentials provided on the thick-client UI are passed to the backend services and those services interface with the database to validate the user credentials.
This authentication process completes successfully, however, it did seem a little sluggish after relocating the database to Host2. (I've since completed and UPDATE STATISTICS process and reindexed the various tables in my SampleDB.) Generally speaking the authentication process is not too slow, it just feels a little sluggish. I'll describe the more pressing issue in the next section.
I am actually planning to take Host1 out of service because it is an old OS and because the hardware is older.
After relocating the database, I though it made sense to stop all the SQL Server services on Host1. I did this for a variety of reasons; I didn't think they would be needed any longer and to protect against any possible conflict between the two database engines (Host1 & Host2). Plus I am planning to take Host1 out of service.
Strange Behavior:
This brings me to the issue that caused me to reach out for assistance from the community.
After stopping the SQL Server services on Host1, I started up my thick-client application. What I discovered was that the application based authentication process was taking several minutes to complete. (Versus perhaps 10 seconds when the Host1 SQL Services were running.)
If I start back up the SQL Services on Host1, the authentication process goes back to roughly 10 seconds.
This is very strange. Host1 has no connection to the SampleDB or the volume that SampleDB is stored on. Host2 has the volume and the database connected.
Questions:
Is it possible that my database that was relocated to Host2 has some information that is causing it to look for SQL information on Host1?
If my .NET web services are correctly communicating with the SampleDB on Host2 based on the datasource settings (which point to Host2), is it possible that they may somehow still be trying to find the database on Host1? (This seems absurd to me.)
Summary:
I'm grasping at straws here. I'll take any suggestions of things I can look at that may give me some insight into why stopping the SQL Services on Host1 might impact an application that is properly interfacing with the database that is on Host2.
Thanks,
Brett
For both your questions, the answer is yes, it's possible.
Out of the two questions, if I were a betting man, I'd wager it's the second scenario of your web services host doing the looking.
As far as actions go, I'd recommend running a network trace on the web services host using something like Wireshark to see if any traffic is passing from that host to the old database host.
When the SQL services on the old host are shut down, you may find a lot of failed connections. Conversely, when the services are started, you may see successful connections to the various SQL services - the database and browser services possibly being the most likely.
A low-tech alternative you can try on the old host if the services are started is checking the active connections through connecting to the old host using something like SQL Server Management Studio with sufficient permissions and running:
SELECT d.name , c.net_transport , c.client_net_address , c.client_tcp_port , c.connect_time , c.last_read FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_connections AS c ON s.session_id = c.session_id INNER JOIN sys.databases AS d ON s.database_id = d.database_id WHERE c.net_transport <> 'shared memory' ORDER BY d.name , c.client_net_address , c.client_tcp_portWhich produces output similar to the following:
You might then be able to identify the IP address of either the web services host (most likely) or even the new SQL host (I'd say this is highly unlikely, but anything is possible) which in turn might help you further investigate where any additions references may be coming from.
It won't necessarily be easy to locate any references, independent of whether they're in a configuration file or buried somewhere within a table of the database, but there's not much we can offer for finding those.
The options above are only useful in the context of proving/disproving that something is still aware of the old host and trying to talk to it, but all that's going to do is confirm your existing suspicion, not solve the cause.
Cheers,
Lain
3 Replies
For both your questions, the answer is yes, it's possible.
Out of the two questions, if I were a betting man, I'd wager it's the second scenario of your web services host doing the looking.
As far as actions go, I'd recommend running a network trace on the web services host using something like Wireshark to see if any traffic is passing from that host to the old database host.
When the SQL services on the old host are shut down, you may find a lot of failed connections. Conversely, when the services are started, you may see successful connections to the various SQL services - the database and browser services possibly being the most likely.
A low-tech alternative you can try on the old host if the services are started is checking the active connections through connecting to the old host using something like SQL Server Management Studio with sufficient permissions and running:
SELECT d.name , c.net_transport , c.client_net_address , c.client_tcp_port , c.connect_time , c.last_read FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_connections AS c ON s.session_id = c.session_id INNER JOIN sys.databases AS d ON s.database_id = d.database_id WHERE c.net_transport <> 'shared memory' ORDER BY d.name , c.client_net_address , c.client_tcp_portWhich produces output similar to the following:
You might then be able to identify the IP address of either the web services host (most likely) or even the new SQL host (I'd say this is highly unlikely, but anything is possible) which in turn might help you further investigate where any additions references may be coming from.
It won't necessarily be easy to locate any references, independent of whether they're in a configuration file or buried somewhere within a table of the database, but there's not much we can offer for finding those.
The options above are only useful in the context of proving/disproving that something is still aware of the old host and trying to talk to it, but all that's going to do is confirm your existing suspicion, not solve the cause.
Cheers,
Lain
LainRobertson thanks for your input. Your suggestions caused me to look even harder at my server-side application services. It turns out that this is where the issue resided. Our server-side services use log4net and there was a datasource in the log4net.config file that was pointing to Host1.
After updating datasource in the log4net.config file to point to Host2, the performance issues being observed on the client-side application were resolved.
Thanks for helping me think more clearly about this!
Good stuff!
Nothing worse than unexplained performance hits you just know shouldn't be there yet you can't quite put your finger on the cause of.
Cheers,
Lain
