Connecting to a SQL Server Repository Database
Microsoft® SQL Server™ 2000 is the DBMS recommended for repository databases. Using a SQL Server database yields maximum performance from the repository engine and provides a layer of security that is otherwise unavailable. If you do not own a licensed copy of SQL Server, you can use the SQL Server Runtime Engine that is freely distributed by Microsoft. The SQL Server Runtime Engine can be used to create or open a SQL Server repository database.
When you use a SQL Server repository database, you must either use (that is, open) an existing repository database, or create an empty database. The repository engine cannot automatically create a SQL Server database for you. To the repository engine, creating a new SQL Server database means populating an empty database with the repository SQL tables it needs to store and manage repository data. If you already have a repository database (that is, a database that contains repository SQL tables), you can connect to it through an open statement.
When you create a new, empty SQL Server database, be sure to specify which users can access the database. You must also create the necessary login and user accounts for people who will be accessing the database, and you must assign the appropriate permissions to these accounts. If you want to grant full permissions to everyone, you can use this SQL command to set database access permissions:
GRANT ALL TO PUBLIC
Creating a New Database
To create a new repository database, use the following syntax. Notice that the first statement creates a repository session. In Microsoft Visual Basic®, be sure to reference Repodbc.dll so that it is available to your program. By default, Repodbc.dll is located in C:\Program Files\Common Files\Microsoft Shared\Repostry.
Use the following code to create a new database in Microsoft Visual C++®:
CoCreateInstance(CLSID_Repository, NULL, CLSCTX_INPROC_SERVER, IID_IREPOSITORY, (LPVOID *) &m_pIRepos)))
m_pIRepos->Create(CCOMVariant(SERVER="MyServer";DATABASE="MyDatabase";UID="MyUserID";PWD="MyPassword"), CCOMVariant(""), CCOMVariant(""), 0, &m_pIRootObj))
Use the following code to create a new database using Visual Basic:
DIM oRepos as New Repository
oRepos.Create "SERVER=MyServer;DATABASE=MyDatabase;UID=MyUserID;PWD=MyPassword;"
Note Invoking the Create method on an existing repository database simply opens it.
Opening an Existing Database
To connect to an existing SQL Server repository database such as msdb, use the SERVER keyword to specify the SQL Server name and the database name. If the database name is not specified, the default database for the user who is opening the database is used. You can also use a data source name (DSN) to connect to a database.
CoCreateInstance(CLSID_Repository, NULL, CLSCTX_INPROC_SERVER, IID_IREPOSITORY, (LPVOID *) &m_pIRepos)
m_pIRepos->Open(CCOMVariant(SERVER="MyServer";DATABASE="MSDB";UID="MyUserID";PWD="MyPassword"), CCOMVariant(""), CCOMVariant(""), 0, &m_pIRootObj)
Administering a SQL Server Database
You can use the utilities and tools that come with SQL Server to administer the repository database (at the database level). For example, if your repository database is damaged due to a power outage or system failure, you should use the recovery tools that are provided with SQL Server to repair the damage. Similarly, if your repository database requires periodic defragmentation, you should use the defragmentation tools that are provided with SQL Server.
Caution SQL Server and its components store private meta data in the msdb database. While you are encouraged to use and add to existing data, be aware that modifying or deleting it can cause unexpected results. If you introduce a modification that breaks the functionality of SQL Server or its components, you must reinstall the software.