I restored a SQL database on a new 2016 server (SQL standard 2016). Database seems to run however something seems wrong.

I restored a SQL database on a new 2016 server (SQL standard 2016).  Database seems to run however something seems wrong.  The database is for a CRM program called "office tools".  I have attached screen shots.

I believe I should see the SQL instance in Sql Server Configuration Manager however it does not appear.
SQLa.JPG
SQLb.JPG
SQLc.JPG
MLV CMAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel_PLDB Expert/ArchitectCommented:
Hi,

I don't find anything wrong there. Please give some more details what's wrong. You are successfully connected to the SQL Server instance, you can see restored database. Nothing unusual there. If you're worried by these XTP messages, please don't. There is a connect item registered for this by Robert L. Davis :
https://connect.microsoft.com/SQLServer/feedback/details/2876739/excessive-non-usable-messages-in-sql-log-when-running-checkdb-in-sql-server-2016

Open in new window

0
MLV CMAuthor Commented:
Hi Daniel_PL.  Thanks for the info.  My concern is when I look in the existing (old server) I can see items in the network config that the Office Tools FAQ points out.  I do not see these items in the new SQL server.

Depending on your network settings, you many have to add an exception to the firewall on your sever for the Dynamic Port associated to your SQL installation. The SQL dynamic port can be located in: Start> Programs> Microsoft SQL Server 2012> Configuration Tools> SQL Server Configuration Manager.

Expand “SQL Server Network Configuration (32bit)” and select “Protocols for NORTH40SYSTEMS”. Double click TCP/IP,  on the IP Addresses tab scroll all the way to the bottom and the TCP Dynamic Port is displayed in there. If you need assistance making an exception for this port in your fire wall contact our support team at (661) 794-2220 or support@officetools.com.

Office Tools server install link
0
Daniel_PLDB Expert/ArchitectCommented:
New SQL Server uses default static TCP 1433 port, because it is a default instance (instance name is MSSQLSERVER). From Office Tools server, or wherever you're running your app, test whether port is open. Either by using telnet <srver address> 1433 or by using port query tool (provided by Microsoft).
https://www.microsoft.com/en-us/download/details.aspx?id=24009

Open in new window


The installation guide you provided is pointing new instance of SQL Server Express. As far as I understand you have your own licensed SQL Server Standard edition with restored database. You should config your software for this server. I'm not sure about this app modules but it seems database is being choosen in Workstation component:
https://www.officetools.com/knowledgebase/workstation-installation/

Open in new window


Regards,
Daniel
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I also don't see anything wrong. You have only a single SQL Server instance installed in that machine (PDC01), right?
0
MLV CMAuthor Commented:
Thanks for the comments.  The server name is "PDC01".  I used the port query tool from a client PC and 1433 is listening.  My concern is the clients can't connect to the office tools database.  I am attaching a screenshot.  The Office Tools client requires the following path "PDC01\NORTH40SYSTEMS".
0
Daniel_PLDB Expert/ArchitectCommented:
You need to install your SQL Server as named instance, or just reconfigure your Office Tools client to point it to PDC01 SQL Server.
0
MLV CMAuthor Commented:
Here is the screen shot
SQL-port-screen-shot.JPG
0
Daniel_PLDB Expert/ArchitectCommented:
Cut \NORTH40SYSTEMS from server name, leave PDC01.
0
MLV CMAuthor Commented:
I tried that and same results.  AttachSql-screen-shot.jpged is the screenshot of Office tools instructions.
0
MLV CMAuthor Commented:
Is there a way I can see / browse the SQL instances running on the server from a client PC?
0
Daniel_PLDB Expert/ArchitectCommented:
You need to enter just PDC01 into server name, nothing else.
0
MLV CMAuthor Commented:
Thanks, I tried that too and still the same results.  SQL-Capture.JPG
0
Daniel_PLDB Expert/ArchitectCommented:
Uncheck Use IP Address, test, if failed go to the SQL Server and check logs fo any errors.

Look here for example:
https://www.officetools.com/knowledgebase/workstation-installation/

Open in new window

0
MLV CMAuthor Commented:
This error is new.  I was not seeing this Friday when I restored the database.

Service: Instance Found
Error: System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(IConnectionUser user)
   at System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe()
   at System.Data.Linq.SqlClient.SqlProvider.InitializeProviderMode()
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at AutoReminderEngine.Classes.AutoReminder.LoadIntervals()
   at AutoReminderEngine.Classes.AutoEngine.Start()
ClientConnectionId:00000000-0000-0000-0000-000000000000
Error Number:-1,State:0,Class:20
0
Daniel_PLDB Expert/ArchitectCommented:
I'm not sure how this software behaves but on the manual for Workstation component they don't have Show all instances checked. Therefore please uncheck it and try again.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You said you restored the database in a new server. How about the logins? You'll need to migrate or recreate the logins that have access to the database on the old server to the new one.
0
Daniel_PLDB Expert/ArchitectCommented:
They have this in the manual
The “Select Server” window will appear. Click the down arrow in the Server box and select “Your_Server_Name\NORTH40SYSTEMS” from the list.
I'm not sure but they might hardcoded instance name, which means your SQL Server needs to be named instance and it's name has to be NORTH40SYSTEMS. That's crazy, I son't see lot of software like this. If another attempt is not working I suggest to install SQL Server named instance with name: NORTH40SYSTEMS.

@Vitor,
It's not login issue, error states: (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
It sounds crazy but it really seems these gyus hardcoded instance name in the connection string...
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can fool the system by adding an entry in the hosts file: C:\Windows\System32\drivers\etc\hosts

Edit the above file and enter the following line:
PDC01\NORTH40SYSTEMS 192.168.200.1

Then you can use PDC01\NORTH40SYSTEMS in the configuration screen.
0
Daniel_PLDB Expert/ArchitectCommented:
I'm afraid you can't do something like that Vitor, did you try your solution? It won't work, the same is possible in native client alias, but you're only operating on server name level, not instance. What you want to achieve is to create hostname PDC01\NORTH40SYSTEMS. If creators of this software hardcoded instance name they'll probably build something like: PDC01\NORTH40SYSTEMS\NORTH40SYSTEMS if that entry will work in hosts, but it won't.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, I've tried this few times already. Usually during migrations and when we didn't know where the old SQL Server instance has been used so this guarantee us a smooth migration.
Other option is to change on the DNS in the AD.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If creators of this software hardcoded instance name they'll probably build something like: PDC01\NORTH40SYSTEMS\NORTH40SYSTEMS if that entry will work in hosts, but it won't.
If that's true it wouldn't work before with the old configuration when the author had the PDC01\NORTH40SYSTEMS configured.
0
Daniel_PLDB Expert/ArchitectCommented:
Find attached file, maybe I'm missing something.
If that's true it wouldn't work before with the old configuration when the author had the PDC01\NORTH40SYSTEMS configured.
Not exactly, if it was default instance with SQL Server Express provided by OfficeTools installer then it should work fine. But it's not the case here. Author installed new SQL Server and from what I understood server name was adjusted to new hostname leaving instance name untouched.
hosts.jpg
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ah, it might need to have an alias created. I need to review that.
Thanks.
0
MLV CMAuthor Commented:
Here is 1 screen shot of the old sever.  The database was on SQL Express 2012Sql-old-server.JPG
0
MLV CMAuthor Commented:
Here is a second screen shot of the old serverSql-old-server2.JPG
0
MLV CMAuthor Commented:
0
MLV CMAuthor Commented:
sql.log is from the new server which is running SQL Standard 2016
0
Daniel_PLDB Expert/ArchitectCommented:
As I stated before, you need to have named SQL Server instance. What I suggest is to install another instance on that server and remove default later.
Example installation is here, instead New_Instance you should type NORTH40SYSTEMS.
https://4sysops.com/archives/how-to-create-a-new-sql-server-2012-instance/

Open in new window


Regards,
Daniel
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MLV CMAuthor Commented:
ok thanks! This helped a lot.  I created the new instance.  Now getting password error for the sa.  I didn't create the database and there is no record of the sa password.
0
Daniel_PLDB Expert/ArchitectCommented:
Hi,

You need to enable mixed mode authtentication, restart SQL Server. After that enable sa account and set strong password for it.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode#SSMSProcedure

Open in new window


Next is restore or detach/attach database from default instance. Lastly retry Workstation component configfuration.
0
MLV CMAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.