Fault symptom: When adding the SQL Server host to BC manager, get the error: "Failed: The database is not online or does not exist."
1). BC manager:V100R005C00SPC100
2). 5300V3: V300R003C00SPC100
3). SQL Server: 2008R2 and 2012
Alert message shown in BC manager after create Microsoft SQL Server host.
1. Open “services.msc” in Windows command line and check if SQL server service is running. If not, SQL server service abnormal should be a possibility which cause BC manager can't find SQL server database instance.
2. Enter SQL server enterprise manager, choose the corresponding database instance and enter database list. Check if database is online, if not, we need to online database instance and try again.
3. But the problem still exist, so we need to collect BC manager log and analyze it. Open “services.msc” in Windows command line, find and stop "RdAgent". Then, find RD Agent installation directory(like: C:\AgentV1R5), collect the whole "log" directory.
4. Open "slqserverinfo.log", then we can find the detailed information when BC manager try to add the SQL server instance.
Here is the error message found in the log:
di 14-06-2 12:45:44 "OSQLEXE path is C:\Program Files\Microsoft SQL Server\110\Tools\Binn\\osql."
di 14-06-2 12:45:44 "Get sqlserver database version[ Microsoft SQL Server 2012 - 11.0.5058.0 (X64) ] successful."
di 14-06-2 12:45:44 "Query instance MSSQLSERVER DbNum 5."
di 14-06-2 12:45:45 "Query db[master] of MSSQLSERVER instance status:ONLINE, RetStatus."
di 14-06-2 12:45:45 "Query Db infos:MSSQLSERVER;master;0;master;0;0;;."
di 14-06-2 12:45:46 "Query db[tempdb] of MSSQLSERVER instance status:ONLINE, RetStatus."
di 14-06-2 12:45:46 "Query Db infos:MSSQLSERVER;tempdb;0;tempdb;0;0;;."
di 14-06-2 12:45:47 "Get database model of MSSQLSERVER instance status not ONLINE, RetStatus."
di 14-06-2 12:45:47 "sqlinfo:Null."
di 14-06-2 12:45:47 "SQLSERVERDBINFO:1> 2> No permission to access database 'model'."
di 14-06-2 12:45:47 "SQLSERVERDBINFO: name "
di 14-06-2 12:45:47 "SQLSERVERDBINFO: db_size "
di 14-06-2 12:45:47 "SQLSERVERDBINFO: owner "
Please note the bold words. When the BC manager add SQL server hosts, it should inquiry the database path first, as the log "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\\osql".
Second, it should inquiry database instance status. In this case, we can see two database instance "master" and "tempdb", they're both online.
But, later, when BC manager try to get database model, it returns "status not ONLINE" and "No permission to access database 'model'"
So, we can see the reason should be BC manager has no permission to access database.
5. The procedure to check the permission of BC manager in SQL server as below:
1). Open SQL server manager;
2). Right click the database which can’t add to BC manager;
3). Check the Permissions, whether guest account has the connect permission to the database;
4). If not, click “Search”, input “Guest”, then enable “Grant” option for connect operation, as below:
By referring the procedure, the we fixed 2 in 3 servers. Another one server still can't be added to BC manager.
6. Collect the BC manager log again and analyze the "sqlserverinfo.log".
Here is the error message in the log:
wo 22-06-2 14:48:57 "OSQLEXE path is C:\Program Files\Microsoft SQL Server\120\Tools\Binn\\osql."
wo 22-06-2 14:48:57 "Do not connect the stand alone instance[MSSQLSERVER]."
"C:\Program Files\Microsoft SQL Server\120\Tools\Binn\\osql" -b -E -i"C:\Huawei\tmp\OsqlExit3.txt"
wo 22-06-2 14:48:58 "Get sqlserver database version failed."
wo 22-06-2 14:48:58 "Execute sqlserverinfo.bat end, exit 0."
This time we can see BC manager can't connect database install at the SQL path.
7. Next step, we need to check if the path is wrong. BC manager get the SQL server installation path by searching Regedit, so we need verify in Regedit.
1). Open “regedit”
2). Find: "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server", check directory, like below:
3). Check the path key in each directory like below:
8. In the case, the customer told us, there's two path key found in Regedit, "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\\osql." and "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\\osql." So, it represent that there must be two version of SQL Server in this server. The customer confirmed that there's SQL server 2014 and SQL Server 2008.
According to compatibility list, SQL Server 2014 is not supported by BC manager yet.
1. When BC manager add SQL server database, it should search SQL server installation path in Windows Regedit.
In current BC manager version, it should choose the installation path of the latest version if there's more than one version of SQL server in a host(Server).
So, if there's more than one version of SQL server in a host, the earlier version of SQL server instance can't be found.
2. SQL server 2014 is not support by BC manager right now.
3. After found the SQL server installation path, BC manager will try inquiry database instance status. If the database instance is offline status or SQL server service status is abnormal, the inquiry operation should failed.
4. Next step, BC manager will use Guest account to connect database instance and get database instance information, like size, name, and so on. If the Guest account has no permission to access database, the inquiry will failed.
1. Check if current SQL server version in BC manager support list.
2. Check if SQL server service status is "Running" in Windows "Services".
3. Check if SQL server instance status is online in "SQL Server Enterprise Manager".
4. Check if Guest account in SQL server has connect permission.
5. Check if there's more than one version of SQL server installed on the server in Windows Regedit.
6. Check BC manager log for more clues to investigate the issue.