Creating and Managing Databases using MSDE Manager
This article will discuss how to create and manage databases on your MSDE Engine (SQL Server) using the Vale Software “MSDE Manager.” Previous articles have discussed how to install MSDE and MSDE Manager. I have also provided instructions on registering your instances of MSDE and SQL Server so you can use MSDE Manager to administer your servers. Please refer to these articles if you require any further information on these processes.
SQL Server Structure – What is a SQL Server Database?
A SQL Server database will consist of at least two files:
- Data file – this stores all database objects such as tables and indexes
- Transaction Log File – records changes made to the data.
When a new database is created all key information such as its name is stored in the Master database in the sysdatabases table. The new database will also contain system objects required for it to function correctly, these are taken from the Model database – when a database if first created it is an exact copy of the model database.
Creating a Database
Creating a database is a relatively straight forward process and can be done using T-SQL or the wizard supplied in MSDE Manager.
MSDE Manager (Wizard)
Start up your working version of MSDE Manager you should be faced with screen similar to the following:
Expand the server that you want to host your new database.
There are several ways to start the wizard, but there is a button the tool bar that's call "New Database" clicking this button will start the wizard.
You will be faced with the “New Database” wizard screen. From the screen you can specify the options and setting for you new database.
Most of the settings can be found on the “General” tab. You can accept most of the default setting when doing this. You will have to specify a name for your database. You should also check that the location of the Data file and the Log files are in drives and folders that you have access too.
Clicking on the "Options" tab allows you to specify the Recovery Model of your database. We will cover what these three options are and what they allow you to do in a later article.
Clicking OK will create a new database. In this case the database is called "GethynTest" and it should appear in our list of database in MSDE Manager.
If the database doesn’t appear in this list right click <Database> and select <Refresh> and the database will appear.
T-SQL (MSDE Manager)
As I mentioned earlier database can be created using T-SQL script. We can use the T-SQL interface in MSDE manager to generate a script to create a new database. Executing the following script under the Master database will create the same database that wizard above.
CREATE DATABASE [GethynTest]
ON (NAME = N'GethynTest_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL$TUTTSWOOK\Data\GethynTest_Data.MDF' , SIZE = 1, FILEGROWTH = 10%)
LOG ON (NAME = N'GethynTest_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL$TUTTSWOOK\Data\GethynTest_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AS
GO
Managing Databases Using MSDE Manager
Managing File Growth
When databases are created they are the size of the “Model” system database which is usually kept relatively small. If you intend the new database to be a large transactional system then you will need to allow it to grow or extend its size yourself. Failing to let your database grow will mean when it hits its max size no further update transactions can take place. You can setup to SQL Server to Grow the files for you when necessary but it is important to remember that this can be detrimental to the database performance especially if the growth size are not all that big and the database has grow regularly. To setup your database to grow automatically:
Right click on your database in MSDE Manager and select <Design Database> this will take you to the Database Properties window...
Expanding Databases
If you wish to expand the size of your database manually then this can be done either using T-SQL or MSDE manager.
You can execute this script in the T-SQL interface and of MSDE Manager and it will expand the size of the database.
ALTER DATABASE "GethynTest"
MODIFY FILE
(Name = GethynTest_Data,
Size = 10MB)
GO
This script expands this size of the data file to 10 megabytes.
To do this MSDE Manager right click on the database and select <Design Database>
Change the <initial size> field to reflect the new size of the data file and click <OK> and the file will be resized. You cannot use this option to shrink the data file.
Shrinking Database
Should you ever want to shrink your database you can do this using MSDE Manager or you can use T-SQL.
Using T-SQL
This example decreases the size of the files in the GethynTest user database to allow 10 percent free space in the files of GethynTest.
DBCC SHRINKDATABASE (GethynTest, 10) GO
Using MSDE Manager
Right click on the database you want shrink and select <Shrink Database>
Enter the % free details and click <OK>
And the database would have been shrunk.
Conclusion
In conclusion this article highlight that in SQL Server and MSDE there are sometimes several ways of achieving the same result.
You can use T-SQL to create and manage databases or you can use the MSDE Manager GUI interface, there are benefits of both but what it really comes down to is personal preference.
Either way MSDE Manager has the functionality and flexibility to allow you to do it your way.
Click here for Product Information on MSDE Manager