How to configure the Microsoft MSDE
Introduction
This article describes how to configure the Microsoft MSDE database engine. Below you will find an overview of the topics.
* What is MSDE?
* Limitations
* What you get
* How to install and configure the MSDE
* Arguments to the setup.exe file
* Using the “setup.ini” file
* Post reconfiguration
* Fire it up!
* How do I determine what version I am running?
* Create and delete a database
* How do I list all the databases on the system?
* How do I list all tables in a database?
* Add a new user with SQL authentication
* Add a user to db_owner role
* Exit osql
What is MSDE?
MSDE is a limited version of the Microsoft SQL Server. In short, it is the Microsoft SQL Server 2000 database engine without any of the fancy UI tools, and with some limitations in the database size and the number of connections. The MSDE database is free, and can be distributed embedded in your own applications or as a small stand alone SQL server. It is ideal for small websites and small businesses with less than 25 simultaneous users. The database is limited to 2 GB of data storage space, but you can easily upgrade it to a full Microsoft SQL Server without any limitations. Among the choices are, a standard edition or an enterprise edition. An MSDE database is a good and affordable starting point for any business, organization or even home solutions. The database does not have to be installed on the hard drive on the deployment computer, but it could be running from a CD-ROM.
Limitations
There are some limitations to a MSDE database. You don’t get:
* Failover clustering.
* Log shipping.
* Parallel DBCC.
* Parallel create index.
* Enhanced read ahead and scan.
* Indexed views.
* Federated database server.
* System area network support (SAN).
* Graphical DBA, and developer utilities and wizards.
* Full-text search.
* Analysis services.
* OLAP partitions.
* Partition wizard.
* Linked OLAP cubes.
* ROLAP dimension support.
* HTTP Internet support.
* Custom rollups.
* Calculated Cells.
* Write back to dimensions.
* Very large dimension support.
* Actions.
* Real-time OLAP.
* Distributed partition cubes.
* Data mining.
* English query.
What you get
This is what you get:
* Maximum number of processors: 2 in NT/Win2000/XP/2003, 1 in Win98/ME.
* Maximum physical memory: 2 GB.
* Maximum user connections: 5 before the work load governor will limit the performance.
* Maximum data storage: 2 GB.
* Desktop embedded or network access.
* Leading SQL database performance.
* It’s free. =:-)
How to install and configure the MSDE
The MSDE can be downloaded from the Microsoft web site for free. Download the archive and uncompress it to a folder on your hard drive. There are four different ways to run the MSDE depending on how you want to access the database. They are all listed below.
You can configure the MSDE in three different ways:
* Arguments to the setup.exe file.
* Using the “setup.ini” file.
* Post reconfiguration.
The four different running modes are:
* Windows authentication without network access.
* Windows authentication with network access.
* Mixed Windows and SQL mode authentication with network access. (required for intraNET)
* Mixed Windows and SQL mode authentication without network access.
Before you start the installation of the MSDE, disable Norton Antivirus and the Internet Security Pack. You will enable it after the installation has completed. Norton Antivirus will not let you install the MSDE successfully, when enabled.
In all of our examples, we will be using “password” for the system administrator.
Arguments to the setup.exe file
You can run configure the MSDE by giving arguments to the setup.exe file on a command prompt.
Mixed Windows and SQL mode authentication with network access.
Example 2 (Java and Win access over a network):
In this example, we will be using the mixed mode authentication, because we want to access the database from both Windows applications and Java applications over a network. The username and password will be sent over the network in plain text.
C:\...MSDE\>Setup.exe DISABLENETWORKPROTOCOLS=0
SAPWD="password" SECURITYMODE=SQL
Using the “setup.ini” file
You will find a file called “setup.ini” in the MSDE folder, if you want to use a pre configured setup file. Please remember that anyone with read access to this file can see the system administrator password if this is an issue. The file looks like this:
[Options]
Here you will enter the configuration arguments.
Mixed Windows and SQL mode authentication with network access.
Example (Java and Win access over a network):
In this example, we will be using mixed mode authentication, because we want to access the database from both Windows applications and Java applications over a network. The username and password will be sent over the network in plain text.
[Options]
DISABLENETWORKPROTOCOLS=0
SAPWD="password"
SECURITYMODE=SQL
To install MSDE, run the “setup.exe” install application.
Post reconfiguration
Do you want to change the system administrator (sa user) password?
Login to the MSDE using the osql tool:
Using SQL authentication:
C:\> osql –U sa –P password –S localhost
1> use master
2> go
1> exec sp_password @old = null, @new = ‘newpassword’, @loginame = 'sa'
2> go
Password changed.
1>quit
Fire it up!
Let’s start the MSDE. Open the Control Panel, the Administrative Tools and the “Services” utility. Find the instance “MSSQLSERVER” in the list. Right click it, and choose “Start”. Startup type should be “Automatic”.
How do I determine what version I am running?
OK, the MSDE should now be up and running. Let’s test it. Run the osql tool.
C:\> osql –E –S localhost
1: select @@VERSION
2: go
3: quit
8.00.194 - MSDE 2000
8.00.384 - MSDE 2000 SP1
8.00.534 - MSDE 2000 SP2
8.00.760 - MSDE 2000 SP3
Create and delete a database
By default, the MSDE has four databases installed. These databases are:
* Master – Contains all of the system meta-data, like login accounts, settings, and the location of the database files etc. Files: master.mdf, mastlog.ldf.
* Tempdb – Contains work tables and temporary storage. All the data in this database will be deleted on disconnect. Files: tempdb.mdf, templog.ldf.
* Model – This is a model of all databases on the system. It acts as a template for new databases. Files: model.mdl, modellog.ldf.
* Msdb – Used for the SQL Server Agent to schedule jobs. Files: msdbdata.mdf, msdblog.ldf.
You should not use any of these databases for any application purposes. They are holy land as far as the applications know it. They are out of reach, simple as that. Create a new database to store your application data.
Let us create a database with the name “intradb”. First of all, you must login as a system administrator (sa) or as a user with dbcreator privileges. Open the osql administration utility and login as system administrator.
C:\> osql –U sa –P password –S localhost
Or
C:\> osql –E –S localhost
Creating an intraNET database ('intradb'):
1> use master
2:> go
1> CREATE DATABASE intradb
2> go
The CREATE DATABASE process is allocating 0.63 MB on disk 'intradb'.
The CREATE DATABASE process is allocating 0.49 MB on disk 'intradb_log'.
Delete the intraNET database ('intradb')
1> use master
2:> go
1> DROP DATABASE intradb
2> go
Deleting database file 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\Books_log.LDF'.
Deleting database file 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\Books.mdf'.
Creating a database specifying the filename and the size
1> use master
2> go
1> CREATE DATABASE intradb
2> ON
3> ( NAME = books_dat,
4> FILENAME = 'D:\Database\intradb.mdf',
5> SIZE = 10,
6> MAXSIZE = 100,
7> FILEGROWTH = 5 )
8> go
The CREATE DATABASE process is allocating 10.00 MB on disk 'intradb_dat'.
The CREATE DATABASE process is allocating 2.50 MB on disk 'intradb_log'.
* NAME = Logical name used to specify the database in TSQL statements.
* FILENAME = Physical location of the database on the hard drive.
* SIZE = Initial file size.
* MAXSIZE = The database maximum size.
* FILEGROWTH = Increments of the database file. The DB file will grow with this size in MBs.
Creating a database specifying the filename, size, and logfile
1> use master
2> go
1> CREATE DATABASE Books
2> ON
3> ( NAME = intradb_dat,
4> FILENAME = 'D:\Database\intradb.mdf',
5> SIZE = 10,
6> MAXSIZE = 100,
7> FILEGROWTH = 5 )
8> LOG ON
9> ( NAME = 'intradb_log',
10> FILENAME = 'D:\Database\intradblog.ldf',
11> SIZE = 5,
12> MAXSIZE = 50,
13> FILEGROWTH = 2 )
14> go
The CREATE DATABASE process is allocating 10.00 MB on disk 'intradb_dat'.
The CREATE DATABASE process is allocating 5.00 MB on disk 'intradb_log'.
* NAME = Logical name used to specify the log and the database in TSQL statements.
* FILENAME = Physical location of the log file and the database on the hard drive.
* SIZE = Initial file size.
* MAXSIZE = The log and the database maximum size.
* FILEGROWTH = Increments of the log and the database file. The DB file will grow with this size in MBs.
How do I list all the databases on the system?
If you want to list all the databases in the instance, please open the osql utility and do the following query:
C:\ >osql -E -S localhost
1> use master
2> go
1> select name from sysdatabases
2> go
name
------------------------------------
master
tempdb
model
msdb
test
intradb
(6 rows affected)
How do I list all tables in a database?
If you want to list all tables in an instance, please open the osql utility and do the following query:
C:\ >osql -E -S localhost
1> use intradb
2> go
1> select name from sysobjects where type = 'U'
In this example, we list all tables in the intradb database. Type ‘U’ means “User Table”. Please try to change type to “S” if you want to list out all System tables.
C:\ >osql -E -S localhost
1> use intradb
2> go
1> select name from sysobjects where type = 'S'
Add a new user with SQL authentication
This will add a new SQL login user ‘intranet’ with the password ‘pass45’ and set the default database to ‘intradb’. After creating the user, remember to grant access to the database and the tables for the user.
C:\ >osql -E -S localhost
1> use master
2> go
1> EXEC sp_addlogin 'intranet', 'pass45', 'intradb'
2> go
New login created.
Grant access to the database:
C:\ >osql -E -S localhost
1> use intradb
2> go
1> EXEC sp_grantdbaccess 'intranet'
2> go
Granted database access to 'intranet'.
1> grant all on table_name to intranet
2> go
Add a user to db_owner role
Adding a user to a db_owner role will grant the user all of the permissions to that database.
To add a user into a role using the system stored procedures, use the sp_addrolemember stored procedure, passing the database role and the user name.
C:\ >osql -E -S localhost
1> use intradb
2> go
1> EXEC sp_addrolemember 'intranet', 'db_owner'
2> go
'intranet' added to role 'db_owner'.
Exit osql
1> quit