Preparing SQL Server to store XML columns for the BIS workspace (Bathymetry Solution)

ArcSDE XML columns must be enabled on the BIS geodatabase for it to work properly in an enterprise environment.

Steps:

1. Start SQL Server Management Studio.
2. In Object Explorer, right-click the database server and click New Query.

   The SQL Query Editor window appears.

   USE BISDB
   GO
   

3. Execute the following query to verify the full-text search engine is properly installed.

   SELECT fulltextserviceproperty('IsFulltextInstalled');
   GO
   

   If the value 1 is returned, the full-text search engine has been installed properly. If another value is returned, you must install or reinstall Full-Text Search; you may need to reinstall SQL Server. You cannot successfully create a full-text catalog or an ArcSDE XML column if this query does not return the value 1.

4. Check whether the database has been enabled for full-text searching.

   SELECT DATABASEPROPERTY('bisdb', 'IsFulltextEnabled');
   GO
   

   If the value 1 is returned, the database is already full-text enabled, and you can continue to step 5.

   If another value is returned, the database must be enabled for full-text searching using SQL Server Management Studio before you can continue. If this is the case, perform the following substeps.

   1. In Object Explorer, click the plus sign (+) next to the appropriate SQL Server instance; then click the plus sign (+) next to Databases.
   2. Right-click the database in which the XML column will be created and click Properties.
   3. Click Files under Select a Page on the left of the Database Properties dialog box.
   4. Check the Use full-text indexing check box in the page on the right if it is not already checked.
   5. Click OK on the Database Properties dialog box.
5. Create a full text catalog by performing the following in the SQL Query Editor window.
   1. Select the database in which the XML column will be created using the drop-down list on the SQL Editor toolbar or by executing the USE command.

      USE BISDB;
      GO
      

   2. Create a full-text catalog in the database using the CREATE FULLTEXT CATALOG statement.

      CREATE FULLTEXT CATALOG SDE_DEFAULT_CAT AS DEFAULT;
      GO
      

   3. If you provide a name other than SDE_DEFAULT_CAT, you must modify the value of the XML_IDX_FULLTEXT_CAT parameter for the DEFAULTS keyword in the SDE_dbtune table.

      See Altering the contents of the DBTUNE table for information on changing the values of parameters in the SDE_dbtune table.

   4. You can optionally set other properties of the full-text catalog such as providing a location on disk where the files will be stored and setting the catalog as the default full-text catalog for the database.
6. Grant permission on the full-text catalog.

   Grant REFERENCES permission on this full-text catalog to the user who will own the XML column, or grant this permission to the role to which the user belongs.

   GRANT REFERENCES ON FULLTEXT CATALOG :: SDE_DEFAULT_CAT TO BIS;
   

7. Modify the value of the XML_IDX_FULLTEXT_CAT parameter to SDE_DEFAULT_CAT for the DEFAULTS keyword in the SDE_dbtune table.

   sdedbtune -o alter -k DEFAULTS -P XML_IDX_FULLTEXT_CAT -v SDE_DEFAULT_CAT -u sde -p sde -D bisdb -i sde:sqlserver:<server> -N
   

8. Modify the value of the XML_COLUMN_STORAGE parameter to DB_XML for the DEFAULTS keyword in the SDE_dbtune table.

   sdedbtune -o alter -k DEFAULTS -P XML_COLUMN_STORAGE -v DB_XML -u sde -p sde -D bisdb -i sde:sqlserver:<server> -N
   

9. Verify the XML parameters for the DEFAULTS keyword in the SDE_dbtune table.

   USE BISDB
   GO
   SELECT * FROM sde.dbtune ORDER BY 1,2;
   GO
   SELECT * FROM sde.dbtune 
   WHERE keyword = 'DEFAULTS' 
   AND parameter_name LIKE 'XML%' 
   ORDER BY 1,2;
   GO
   SELECT * FROM sde.dbtune 
   WHERE keyword = 'DEFAULTS' 
   AND parameter_name = 'XML_IDX_FULLTEXT_CAT' 
   ORDER BY 1,2;
   GO