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

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

Select allOpen in new window

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

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

Select allOpen 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/

Select allOpen in new window

Regards,
Daniel

I also don't see anything wrong. You have only a single SQL Server instance installed in that machine (PDC01), right?

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

You need to install your SQL Server as named instance, or just reconfigure your Office Tools client to point it to PDC01 SQL Server.

Here is the screen shot
SQL-port-screen-shot.JPG

Cut \NORTH40SYSTEMS from server name, leave PDC01.

I tried that and same results.  Attached is the screenshot of Office tools instructions.

Is there a way I can see / browse the SQL instances running on the server from a client PC?

You need to enter just PDC01 into server name, nothing else.

Thanks, I tried that too and still the same results.  

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/

Select allOpen in new window

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

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.

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.

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

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:

Then you can use PDC01\NORTH40SYSTEMS in the configuration screen.

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.

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.

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.

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

Ah, it might need to have an alias created. I need to review that.
Thanks.

Here is 1 screen shot of the old sever.  The database was on SQL Express 2012

Here is a second screen shot of the old server

sql.log

sql.log is from the new server which is running SQL Standard 2016

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.

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

Select allOpen in new window

Next is restore or detach/attach database from default instance. Lastly retry Workstation component configfuration.

Thanks!