Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Note that:

- the database does not need to be online during the analysis process in the CAST Management Studio.
- Note that you do NOT need to install a compatible client connection layer (for the target RDBMS) on the machine on which you are running the CAST Delivery Manager Tool.

Host nameUsed to configure the host name of your database server. You can also enter an IP address if required.
Instance identificationUsed to configure the database server access mode type (Port or Instance).

Select an appropriate access mode for your database.

PortUsed to configure the port number for accessing your database server. This field will only be displayed if you selected Port from the Instance identification option.

Default RDBMS port settings are as follows:

  • Microsoft SQL-Server > 1433
Instance nameUsed to configure the instance name for accessing your database server. This field will only be displayed if you selected Instance from the Instance identification option.
Credentials
Select this option to enable the User/Password fields below. This is required if your target database server requires an authenticated connection.

Image Modified Notes

  • Do not enable this option if a single sign on system is in operation in your environment.
User name
Used to configure the user name that has sufficient privileges to access the required databases on the selected RDBMS for packaging purposes.

In order to carry out an extraction of the required databases, the person in charge of the extraction (Delivery Manager or DBA) must use a Microsoft SQL Server login (whether using Windows or SQL authentication) that has the following roles and permissions:

Any user with the following permissions and roles

  • "public" Server Role
  • "db-datareader" Database Role on all databases that require extraction (i.e. the login is mapped as a User on the target databases and is given the "db-datareader" Database Role)
  • "View definition" + "Grant" explicit permission on all databases that require extraction (i.e. the explicit permission is given to the User)

An example script to assign the required role and permissions is shown below:

Code Block
languagesql
-- Create an SQL Server login
loginCREATE
CREATE LOGIN <login> WITH PASSWORD = '<password>';
GO
 
-- Create a database user (on all databases that require extraction) for the login created above
-- this will also automatically assign the CONNECT + GRANT explicit 
permissionUse <database>GOCREATE
permission
Use <database>
GO
CREATE USER <user> FOR LOGIN <login>;
GO

-- Assign the db_datareader database role to the user created above for all databases that require
extractionUse <database>GOexec
 extraction
Use <database>
GO
exec sp_addrolemember 'db_datareader', <user>
GO
<user>GO
-- Assign your user the View definition + GRANT explicit permission on all databases that require
extractionUse <database>GOGRANT
 extraction
Use <database>
GO
GRANT View definition TO <user>
<user>GO
GO

The following screenshots show the same changes performed in the MS SQL Server GUI:

  • A login (whether using Windows or SQL authentication) must be defined in the SQL Server. This login requires NO "Server Roles" at all, apart from "public" which is granted automatically when the login is created:

Image Modified

  • Under "User Mapping" (login properties), the login must be assigned the "db_datareader" database role for for all databases that require extraction:

Image Modified

  • Lastly in the Properties of all databases that require extraction, assign your user the "View definition" + "Grant" explicit permission:

Image Modified

Extraction user

When it is not possible to grant a user the "public" Server Role and the "db-datareader" Database Role as outlined above, CAST recommends using a dedicated extraction user with specific privileges in order to access the required data for extraction.

An example script to assign the required role and permissions is shown below:

Code Block
languagesql
-- Create an SQL Server login
loginCREATE
CREATE LOGIN <login> WITH PASSWORD = '<password>';
GO

-- Create a database user (on all databases that require extraction) for the login created above
-- this will also automatically assign the CONNECT + GRANT explicit
permissionUse <database>GOCREATE
 permission
Use <database>
GO
CREATE USER <user> FOR LOGIN <login>;
GO

-- Issue grant on specific tables
tablesGRANT
GRANT SELECT ON master.dbo.sysdatabases TO <user>
<user>GOGRANT
GO
GRANT SELECT ON master.dbo.spt_values TO <user>
GO
<user>GOGRANT
GRANT SELECT ON master.dbo.syscharsets TO 
<user>GOGRANT
<user>
GO
GRANT SELECT ON master.dbo.syscurconfigs TO <user>
GO
<user>GOGRANT
GRANT SELECT ON sys.databases TO <user>
GO
<user>GOGRANT
GRANT SELECT ON sys.schemas TO <user>
<user>GOGRANT
GO
GRANT SELECT ON sys.columns TO <user>
GO
<user>GOGRANT
GRANT SELECT ON sys.types TO <user>
GO
<user>GOGRANT
GRANT SELECT ON sys.foreign_keys TO <user>
<user>GOGRANT
GO
GRANT SELECT ON sys.sysforeignkeys TO <user>
GO
<user>GOGRANT
GRANT SELECT ON sys.tables TO <user>
<user>GOGRANT
GO
GRANT SELECT ON sys.foreign_key_columns TO <user>
GO
<user>GOGRANT
GRANT SELECT ON sys.views TO <user>
GO
<user>GOGRANT
GRANT SELECT ON sys.procedures TO <user>
GO
<user>GOGRANT
GRANT SELECT ON sys.numbered_procedures TO <user>
GO
<user>GOGRANT
GRANT SELECT ON sys.objects TO 
<user>GOGRANT
<user>
GO
GRANT SELECT ON sys.trigger_events TO 
<user>GOGRANT
<user>
GO
GRANT SELECT ON sys.triggers TO <user>
GO
<user>GOGRANT
GRANT SELECT ON dbo.sysobjects TO <user>
GO
<user>GOGRANT
GRANT SELECT ON dbo.sysusers TO <user>
GO
<user>GOGRANT
GRANT SELECT ON dbo.systypes TO 
<user>GOGRANT
<user>
GO
GRANT SELECT ON dbo.sysforeignkeys TO 
<user>GOGRANT
<user>
GO
GRANT SELECT ON dbo.syscomments TO <user>
GO
<user>GO

--Issue grant select to extraction user on databases that require
extractionuse <database>GOGRANT
 extraction
use <database>
GO
GRANT SELECT TO <user>
<user>GO
GO
PasswordUsed to configure the password that corresponds to your User name configured above.
Remember passwordThis option enables you to force the CAST Delivery Manager Tool to save the database access credentials you have entered above.

Choosing an option or not has no impact on the extraction (i.e. the CAST Delivery Manager Tool can still access the required resources). However, if you are creating subsequent Versions of the same server, you can choose to store the password in which case you will not need to re-enter it.

There are two save options:

  • Local > The credentials are saved in the user's local workspace on the current machine. Choose this option if you do not want the password to be available to other Delivery Managers.
  • Server > The credentials are saved locally (as above) and are also synchronized back to the CAST AIC Portal (i.e. the Source Code Delivery Folder). Choose this option if you want the password to be available to other Delivery Managers.
Databases to extract
This section enables you to limit the packaging to certain databases on the target RDBMS:

Image Modified

Image Removed
Image Added
Use this option to browse the live server (using the credentials you supplied above) and display a list of the databases that are available. Place a check mark next to the database(s) that you want to package:

Image Modified

Click OK when you have selected the items you require.

Image Removed
Image Added
Use this option if you know the name of the database you want to package. A dialog box will be displayed enabling you to enter the name of the database. You can only enter one database name at a time using this option - to enter multiple databases, either repeat this action, or use the option above.
Image Modified
Use this button to edit an existing schema - a dialog box will appear in which you can modify the existing database name.
Image Modified
Use this button to remove an existing database from the list.
JVM Memory SizeThe CAST extractors are Java based and require the use of the JRE JVM (Java Virtual Machine). This option allows you to configure the Maximum Java Heap Size of your JVM for use during the database/schema browse and extraction processes. By default 1GB of memory is allocated and in most scenarios this value can be left as it is.

You may need to change the amount of memory allocated to the JVM if you are getting out of memory exceptions for the Java Heap Space when you either browse to select a database/schema or when you use the Package action to invoke the extraction - this can occur more specifically when attempting to extract a large single database/schema or multiple databases/schemas. Please increase the memory allocation until you no longer receive errors, however, please bear the following in mind:

  • 32bit JRE: If you are running the CAST Delivery Manager Tool via the CAST AIC Portal and have installed a 32bit JRE for this purpose, or when the CAST Delivery Manager Tool is launched from the CAST Management Studio (when it will use a 32bit JRE that is provided with the CAST installation), CAST recommends that you do not increase the JVM Memory Size value to above 2GB, otherwise you risk running out of system memory.
  • If your database/schema extraction consistently runs out of memory despite having allocated the maximum possible with a 32bit JRE, you may need to ensure you are using a 64bit JRE to take advantage of increased memory availability. To do so, you must launch the CAST Delivery Manager Tool from the CAST AIC Portal, rather than directly from the CAST Management Studio (when it will use a 32bit JRE that is provided with the CAST installation). And before you do so, you must manually modify the shortcut to the CAST Delivery Manager Tool (available in the Windows Start menu) to replace the existing path to the 32bit JRE (C:\Windows\SysWOW64\javaws.exe) with your 64bit JRE path ("C:\Program Files\java\jre1.7.0_51\bin\javaws.exe" for example). You can add the -Xmx parameter (maximum memory allocation), that will allow the process to use the memory defined. The shortcut should now look similar to this:

    "C:\Program Files\java\jre1.7.0_51\bin\javaws.exe" -Xmx4096M -localfile
    "C:\Users\<user_name>\AppData\LocalLow\Sun\Java\Deployment\cache\6.0\6\1c274146-29c73860"
Anonymize log file
Use this option to hide sensitive information in the log file. When activated, the log file will contain stars in place of the host name/server name, schema name, username and password (i.e. server connection information):

Image Modified

Note that this option is not activated by default because anonymizing connection information can make it more difficult to troubleshoot errors or issues during the extraction phase.

Anchor
b
b
Reuse existing CAST delivery output

If you have delivery output (i.e. a uaxdirectory file) from previous extractions carried out with a standalone CAST SQL Extractor, then you can use this option to package this output for subsequent analysis in the CAST Management Studio. Note that this option requires a .uaxdirectory file.

The configuration is as follows:

Server Version FileUse this option to select the .uaxdirectory file.
Schemas to extract
See identical option above - this functions in exactly the same way, however, for the Image Modified option, no connection to a database is made. Instead, the contents of the .uaxdirectory are explored and the schemas that were previously extracted will be offered.

Note that if you want to select all schemas in the selected .uaxdirectory file then you need to specifically choose them using this option.

 

CAST Website