SQL (SQL Server)#
This module allows you to connect to a Microsoft SQL Server. The module’s integrated parser generates dependency information from DDL and DML source stored in text files. In addition, the module can connect to SQL Server 2008, SQL Server 2005, and SQL Server 2000, and produce dependency information from the running server’s data dictionary views and tables.
Connecting to the Database#
Database Permissions Required#
The SQL module does not look at or need access to the actual data in the database.
For SQL 2005 and 2008, the indicated SQL user must have access to the databases and have the View Definition permission on the databases to be included in the LDM. The sysadmin built-in server role provides this access along with additional access that is not needed.
For SQL 2000, the indicated SQL user must have access to the databases to be included in the LDM. The user must also have select access to the tables dbo.sysusers, sysobjects, sysusers, syscolumns, sysdepends, sysobjects, sysforeignkeys, systypes and syscomments. As with SQL 2005 and 2008, the sysadmin built-in server role provides the necessary access along with additional access that is not needed.
Create a New Project#
Start LDM.
Select File –> New Project.
Select Enterprise .NET or All from the list of Profiles
Select SQL from the Module Type drop-down.
Select Database Server Connection under Datasources.
Click on the Add… button to enter your connection information:
ServerInstance - Enter the name of the server and instance for this connection. The server must have TCP/IP protocol enabled. Also, for Sql Server 2005, the SQL Server Browser service must be running.
Port - If you use a non-standard port, enter it here. Otherwise, leave this blank.
Username / Password - To use SQL Server Authentication for this connection, enter that information. If these are blank, the module uses Window’s Authentication using the credentials of the user running LDM to access the database. Window’s Authentication only works with SQL Server
Database Type - Indicates the type of database for this connection. The module uses this information to configure default ports and to select the proper logic for traversing the database’s data dictionary.
You will see an initial DSM come up after the input has been processed.
Configuring SQL Server TCP/IP#
The Lattix SQL module connects to SQL Server databases using TCP/IP. By default, SQL server has this capability disabled. The following describes the steps needed to enable TCP/IP connections.
Enable TCP/IP Connections for the Server#
Click on SQL Server Services in the left pane
Right mouse on SQL Server Browser in the right pane and select Properties from the context menu
Windows Firewall#
Options#
Member Level Processing#
Using LDM’s member level functionality, you can see types, columns in table and views, and table triggers. If member level is turned off, these dependencies are aggregated to their parents.
Do Not Load Members
By default Members are not loaded.
Load Only Specified Members
You can specify the list of elements which are expanded down to their members.
Load All Members
Members are enabled for the entire project.
Dependencies for Members#
Do Not Load Member Dependencies
If this option is turned on, the member are displayed but their dependencies are not turned on. This can keep memory consumption down, even while allowing update report to show exactly what members changed.
Load Member Dependencies
Member level dependencies are shown.
Note that you can also enable member level functionality after a project is loaded:
Right click on a subsystem to bring up a list of menus. You can expand the entire project by right clicking on $root.
Select Expand Members.
If you did not have members turned on, a project update dialog will come up. Click OK to update the project.
Scan system meta-data#
By default, the SQL module does not add elements for built-in database objects to the DSM. Examples of built-in objects include system view and tables.
Store source in model#
By default, the SQL module stores the SQL for views, triggers and stored procedures in the LDM. This allows you to view the source for these objects from within LDM.
Parse SQL Source Code#
By default, the SQL module will parse source code stored in the database for stored procedures, triggers and views. Clearing this option reduces model construction time and reduces the number of dependencies detected by the module.
Process Only These Databases#
This property allows you to specify the databases the modules processes. By default, the module processes them all. If there are many databases on the server, you may wish to specify which ones to process.
Atom Types and Dependency Kinds#
The SQL module generates a number of atom types and dependency kinds. This enables filtering, rule specification and usage display based on the value of atom types and dependency kinds.
The SQL module generates the following types of atoms:
Column
Index
Table
View
View Column
Procedure
Trigger
Queue
Schema
Server
Foreign Key
Type
Database
The SQL module generates the following dependency kinds:
Data
Type
Code
DML
DDL
Sql_Dependencies
Trigger