August 7, 2023 at 9:55 pm
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
August 8, 2023 at 10:20 am
I would speak to your network people. It smells like DNS and/or Kerberos.
ps Also, double check you have no SQL Server alias on you application servers.
August 8, 2023 at 1:37 pm
I would speak to your network people. It smells like DNS and/or Kerberos.
ps Also, double check you have no SQL Server alias on you application servers.
I second this.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 25, 2023 at 2:34 pm
Hi...
This problem has been resolved. Based on feedback I received on another forum, I took a closer look at the application servers and found that I had a connection string in my log4net.config file that was referencing the Host1 server. Once I updated the config file, everything started working properly and I was able to stop the MSSQLSERVER service on Host1 without any repercussions on my client application.
Thanks for all the feedback.
Brett
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply