From Rocha Beta 2 Build 11550 the KBs can be stored in SQL Server DB.
This article is not a comprehensive guide to use KBs stored in SQL Server instance but it’s just a list of tips that can be useful for GX Developers.
Whatever editions up from SQL Server 2005 (inlcuding Express EditiMicrosoft SQL Server 2005 (express or other 2005 editions). That is the KB can be stored in a local SQL Server instance (express) or in a centralized instance (SQL installed in a DB Server).
SQL Server Express edition can be download from: http://msdn2.microsoft.com/en-us/express/bb410791.aspx
SQL Server Express with advanced features (e.g. Management Studio) edition, but still free, can be download from: http://msdn2.microsoft.com/en-us/express/bb410792.aspx.
Each KB is composed by files and the DB. Basically a SQL DB is a MDF (data) file and a LDF (log) file which are “attached” to a SQL Server instance; then the DB is a couple of files too.
When a KB is created a folder name is selected and the files, including the DB files, are created in that folder. However, the DB component (i.e. MDF/LDF) can be stored in a different folder (i.e. SQL Server Instance default DB location) no matter if a SQL Express Edition or Server edition is being used.
As mentioned above the KB is a group of files (“data” and DB) in a folder, in fact DB files can be located in the KB folder or it can be located in another folder. Then, to copy/send a KB to another person or to move from a folder to another it’s not enough to copy/send/move the files stored in the KB folder as used to be in previous versions/builds.
Once the files (data and MDF/LDF) were copied/send/moved, two further steps are needed in order to open the KB:
1. Attach DB to SQL Server instance.
The DB (MDF/LDF) was copied; however it must be attached to the SQL Server Instance to be used as explained here
2. Re-configure connection information
When a KB is opened the corresponding DB is tend to be opened too, so GX needs the right connection information.
This information is prompted when the KB is converted/created and is stored in the text file named 'knowledgebase.connection' with the following format:
<ConnectionInformation>
<DBName>GX_KB_Test</DBName>
<IntegratedSecurity>True</IntegratedSecurity>
<ServerInstance>SERVERNAME\SQLEXPRESS</ServerInstance>
<CreateDbInKbFolder>False</CreateDbInKbFolder>
<Directory>C:\Models\GX_KB_Test</Directory>
</ConnectionInformation>
Note: if “Integrated security” (i.e. Trusted connection) is set to “false” the username and password provided are encrypted and stored in an additional file in the KB directory (e.g. c:\kb\invoice\NetworkDomainName\WindowsUserName\knowledgebase.connection).
As of GeneXus 18 Upgrade 6, you can add the 'DetachOnClose' property with True value in the 'knowledgebase.connection' file, so that every time the KB is closed, a detach from the SQL Server is performed. The default value is false.
<ConnectionInformation>
<DBName>GX_KB_Test</DBName>
<IntegratedSecurity>True</IntegratedSecurity>
<ServerInstance>SERVERNAME\SQLEXPRESS</ServerInstance>
<CreateDbInKbFolder>False</CreateDbInKbFolder>
<DetachOnClose>True</DetachOnClose>
<Directory>C:\Models\GX_KB_Test</Directory>
<DataFile>GX_KB_Test.mdf</Data File>
<LogFile>GX_KB_Test_log.ldf</Log File>
<HostName>HOSTNAME</HostName>
</ConnectionInformation>
Make sure to change connection parameters according to your local environment.
A kb can be removed interactively executing the following steps:
1. Perform a “Database detach” using MS SQL Server Management Studio. Further info: http://msdn2.microsoft.com/en-us/library/ms191491.aspx.
2. Remove KB folder.
Note: In order to completely remove the KB, verify the MDF file, if it wasn’t created in KB folder, was removed when the “Database detach” operation was executed.
A kb can be removed by “command line” using a script as the following:
set _DBNAME=Invoices
sqlcmd -S .\SQLEXPRESS -E -Q "ALTER DATABASE %_DBNAME% SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
sqlcmd -S .\SQLEXPRESS -E -Q "Drop database %_DBNAME%"
This script removes the “Invoices” DB (i.e. KB) from the local instance of SQLEXPRESS.
Note: In order to completely remove the KB, don’t forget to remove KB folder too.
A Backup can be executed using the SQLCMD utility; follow these steps:
1. Run “sqlcmd” connecting to the desired SQL Server. Type the following:
BACKUP DATABASE DatabaseName TO DISK = 'C:\MyPath\databasename.bak'
where:
- DatabaseName: references the SQL Server Database name.
- databasename.bak: references the backup.
- C:\MyPath\: references the desired location for the backup.
type GO and press Enter to execute the backup command. You should get something similar to:
BACKUP DATABASE successfully processed X pages in Y seconds.
A new database can be created using the associated mdf and ldf files; follow these steps:
1. Run “sqlcmd” connecting to the desired SQL Server. Type the following:
CREATE DATABASE GX_KB_KBName
ON (FILENAME = 'C:\MyKBPath\GX_KB_KBName.mdf'),
(FILENAME = 'C:\MyKBPath\GX_KB_KBName_log.LDF')
FOR ATTACH;
where:
- GX_KB_KBName: references the Knowledge Base Name.
- C:\MyKBPath\: references the KB desired location.
- GX_KB_KBName.mdf: references the mdf file.
- GX_KB_KBName_log.ldf: references the log file.
type GO and press Enter to execute the restore command.
A kb can be restored using the SQLCMD utility following these steps:
1. Run “sqlcmd” connecting to the desired SQL Server. Type the following:
RESTORE DATABASE GX_KB_KBName
FROM DISK = 'C:\Temp\GX_KB_KBName.bak'
WITH MOVE 'GX_KB_KBName' TO 'C:\MyKBPath\GX_KB_KBName.mdf',
MOVE 'GX_KB_KBName_Log' TO 'C:\MyKBPath\GX_KB_KBName_Log.ldf'
where:
- GX_KB_KBName: references the Knowledge Base Name.
- GX_KB_KBName.bak: references the backup.
- C:\MyKBPath\: references the KB desired location.
- GX_KB_KBName.mdf: references the mdf file.
- GX_KB_KBName_Log.ldf: references the log file.
type GO and press Enter to execute the restore command. You should get something similar to:
RESTORE DATABASE successfully processed X pages in Y seconds.
If you don't know the internal parameters of the bak file to execute the Restore command, use the following one:
RESTORE FILELISTONLY FROM DISK = 'C:\Temp\GX_KB_KBName.bak'
Some useful fields are:
Run “sqlcmd” connecting to the desired SQL Server. Type the following:
EXEC sp_attach_db @dbname = N'GX_KB_SampleKB',
@filename1 = N'C:\Models\SampleKB\GX_KB_SampleKB.mdf',
@filename2 = N'C:\Models\SampleKB\GX_KB_SampleKB_log.LDF'
You should get something similar to:
Query executed successfully
You can use the Create User command and sp_addrolemember Store Procedure combined to assign roles to an existing database; follow these steps:
1. Run “sqlcmd” connecting to the desired SQL Server. Type the following:
USE [DatabaseName]
GO
CREATE USER [IIS AppPool\Classic .NET AppPool] FOR LOGIN [IIS AppPool\Classic .NET AppPool]
GO
USE [DatabaseName]
GO
EXEC sp_addrolemember N'db_datareader', N'IIS AppPool\Classic .NET AppPool'
GO
USE [DatabaseName]
GO
EXEC sp_addrolemember N'db_datawriter', N'IIS AppPool\Classic .NET AppPool'
GO
where:
- DatabaseName: references the Database Name.
- IIS AppPool\Classic .NET AppPool: specifies the desired user.
No, it is not possible to open a KB (MDF file) previuosly created with SQL Server 2008 with SQL Sever 2005. If you try to open it, the following error appears:
The database '<kb name>' cannot be opened because it is version 643. This server supports version 612 and earlier. A downgrade path is not supported.
Could not open new database '<kb name>'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)
Solucion: Select an instance of SQL Server version 2008 in order to open that KB. Remember that you can chose a sever from the network.
Tip: If in anycase you need to open that MDF with SQL Server 2005, you can make use of the GeneXus Server capabilities in order to "copy" the KB from SQL server 2008 to the previous version. Using the "Create KB From Server" option, GeneXus will copy the KB content from the server (which could be SQL 2008) to the client (which could be SQL 2005).
Read this: http://ealmeida.blogspot.com/2008/10/como-defragmentar-indices-en-genexus-x.html
This video shows how to limit the memory used by SQL Server
Data Base “log” files (.LDF) are used by SQL Server to manage the Transactional Integrity. By shrinking it its size can be reduced as shown in this video. Reducing LDF file size performance does not decrease and disk space occupied by the KB is less. In short
- Make sure the backup type is Full and select a destination. Click OK to proceed.
- Once the backup has completed,
stop the SQL server service, then right click on the database and select Tasks> Shrink > Shrink Files.
- Select the log file to shrink.
In order to get connected with a network instance of SQL Server, the TCP/IP protocol on it have to be enabled. To do that, run SQl Server Configuration Manager -> SQL Sever Network Configuration -> Protocols for MSSQLSERVER, select TCP/ip and change the status from 'Disable' to 'Enable'.
Check the Windows user and SQL Server user being used permissions (i.e. access to MDF file location is permitted, etc).
A possible cause can be the user used to access the MS Sqlserver Management Studio has not enough privileges. To use “Windows authentication” instead of “SQL Server Authentication” could help.
The problem could be the DB (MDF) is not attached to the SQL Server Instance being used. Try attaching it using SQL Server Management Studio.
Complete message:
DatabaseMissing (Artech.Core.UI)
------------------------------
Program Location:
en Artech.Core.UI.Managers.KnowledgeBaseManager.EnsureConnection(String kbPath)
en Artech.Core.UI.Managers.KnowledgeBaseManager.Open(String uri)
en Artech.Core.UI.CoreBase.HandleOpenKB(CommandData commandData)
en Artech.Architecture.UI.Framework.Helper.CommandDelegator.Exec(CommandKey cmdKey, CommandData commandData)
en Artech.Architecture.UI.Framework.Helper.CommandTargetChain.Exec(CommandKey cmdKey, CommandData commandData)
en Artech.Architecture.UI.Framework.Packages.AbstractPackageUI.Exec(CommandKey cmdKey, CommandData commandData)
en Artech.FrameworkDE.Commands.CommandManager.command_ExecuteAction(Object sender, CommandEventArgs e)
Using “osql” command the most common task can be executed:
Server
usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-X[1] disable commands [and exit with warning]]
[-O use Old ISQL behavior disables the following]
batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]
Check the <ServerInstance>Server\Instance</ServerInstance> value at knowledgebase.connection file.
The complete message is: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)"
Value cannot be null: Parameter name: exceptionToHandle (Microsoft.Practices.EnterpriseLibrary.ExceptionHandling)
Check if the SQL Server Instance being used is up, running and the associated DB (MDB) is attached. The parameter /NoLastKB parameter can be used to avoid opening the last opened KB and to try with an specific SQL Instance (i.e. Express edition was stopped and the last opened KB was created there).
The complete message is:
Parameter name: exceptionToHandle (Microsoft.Practices.EnterpriseLibrary.ExceptionHandling)
------------------------------
Program Location:
at Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.ExceptionPolicy.HandleException(Exception exceptionToHandle, String policyName)
at Artech.Common.Exceptions.ExceptionManager.HandleException(Exception ex)
at Artech.Core.UI.Managers.KnowledgeBaseManager.EnsureConnection(String kbPath, Boolean attachDB, String dbname)
at Artech.Core.UI.Managers.KnowledgeBaseManager.Open(String uri)
at Artech.Core.UI.CoreBase.HandleOpenKB(CommandData commandData)
at Artech.Architecture.UI.Framework.Helper.CommandDelegator.Exec(CommandKey cmdKey, CommandData commandData)
at Artech.Architecture.UI.Framework.Helper.CommandTargetChain.Exec(CommandKey cmdKey, CommandData commandData)
at Artech.Architecture.UI.Framework.Packages.AbstractPackageUI.Exec(CommandKey cmdKey, CommandData commandData)
at Artech.FrameworkDE.Commands.CommandManager.command_ExecuteAction(Object sender, CommandEventArgs e)
When executing an operation, especially long operations like “rebuild all”, the following message appear: "error: System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable".
Check the “event viewer” for further information, including information messages.
e.g. the file growth, MDF or LDF, is limited avoiding to continue the process so event viewer show this information message: “Autogrow of file 'tempdev' in database 'tempdb' was cancelled by user or timed out after 562 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.”
tip: the FILEGROWTH parameters can be modified using the SQL Enterprise Manager
Database 'GX_KB_DatabaseName' already exists on 'Server'. Are you sure you want to create the Knowledge Base there?
The database called 'GX_KB_DatabaseName' already exists, continuing this acction will delete all data in the the previous database and create an empty one for this KB. Warning, after continuing with this action both KBs will share the same MDF (if no "Database Detach" is made), so changes made in one KB are made for both.