The Server Explorer
One way to manage database within the IDE is the Server Explorer. In prior versions of VB we had the Data View window. This has evolved into the Server Explorer. We now have access to many of the management functions of each server we have access to. This is dependent upon your privileges on each server. Given the requisite privileges, we can now start and stop services, manage message queues (more on this in a later chapter), and perform other server-related functions. The one item that was left out is access to the IIS management functions. The main use of the Server Explorer from a database programmer's point of view is that it gives us access to SQL Servers on each machine. Through this tool we can create databases, tables, views, stored procedures, triggers, and database diagrams. The tools are fully integrated into the IDE. When you open a table designer, it opens in the workspace. Stored procedure windows are color-coded just like VB code windows.
The SQL Server management tools that are integrated into Visual Studio are intended to manage MSDE (Microsoft Database Engine) databases as well as full SQL Servers. The MSDE databases are functionally equivalent to SQL Server databases except that there is a limit to the number of concurrent logged-in users of about five. After this, performance will degrade. The MSDE is freely distributable and is intended to replace the Jet Database Engine used by Microsoft Access. As such, the intent is for it to be used as a desktop database server. However, you can connect to an MSDE database across a network, provided you have the tools to create the connection.
Most of the other limitations of the MSDE are because the tools provided with the IDE do not give easy access to them. They are still available if you have the right tools. If you have access to a full SQL Server client installation, you can use the Enterprise Manager to manage your MSDE databases.
The Enterprise Manager does not know that it is not a full SQL Server. Some of the things you can do with the Enterprise Manager that you cannot do with the IDE are create logins and users, change database security privileges, customizing the locations of the physical database files, and run the maintenance wizard to create scheduled maintenance tasks such as backups, importing and exporting data, and other tuning and management functions. Since most of these functions call system-stored procedures to work, you can access them that way using the SQL Server query analyzer or with a database project. You could also write some VB utilities to perform these functions. There are also some nice shareware tools like SQLPad (www.sqlpad.com). Another way of managing logins and privileges is with Microsoft Access 2000 or later. If you create an Access Database Project (.adp file), you can have access to the SQL Server Security and Replication services. Since I cannot assume you have access to any of the additional tools beyond those provided by the IDE, we will limit our discussion to those tools.