SQL Server & MSDE
While this article is geared mainly toward MSDE users, all of the tools described within can, as far as we know, work against regular editions of SQL Server. Some can even work against Access, Oracle, and other database platforms.
MSDE does not include graphical management tools like Enterprise Manager, Query Analyzer, and Profiler. So, you may have a hard time even verifying that the MSDE install went fine, never mind creating databases, objects and users. There are several ways to manage MSDE installations.
Some of the tools listed below are free, others have a range of pricetags. We will try to update the links to indicate which ones are free.
(Please let us know if we missed your favorite product, or if you have a favorable or non-favorable review of any of the tools listed below.)
Existing Client Tools
If you have an existing machine with SQL Server client tools installed, you can manage the MSDE instance through these... as long as you have a valid and legal Client Access License (CAL). You can take the SQL Server install CD, run it on the client(s), and choose "Install client tools only" during setup. This will install Query Analyzer, Enterprise Manager, and other useful tools—but will not install the database engine.
This has been a point of concern in the past, because SQL Server licensing is not cheap. However, Microsoft decided to lower the price of Developer Edition to $49, effective August 2003. See their &quot;how to buy&quot; p... for details. You can install a licensed developer edition on one client, and from there manage your *development* SQL Servers using those client tools. Note that you cannot use developer edition to manage any SQL Server databases in production, including MSDE.
Other Microsoft Tools
The SQL Server Web Data Administrator installs a web-based GUI that you can use to manage your database(s) from anywhere, using only a web browser.
The Visual Studio.NET IDE has a Server Explorer, which allows you to add a SQL Server connection -- through which you can create stored procedures, manage tables, and view data. Not all of the administrative tasks you would want to perform against your database, but you can accomplish most development issues.
Similarly, ASP.NET Web Matrix allows partial control over SQL Server and MSDE data sources.
Access 2000 and 2002 will allow you to perform rudimentary data manipulation tasks using pass-through queries.
For complete control over MSDE, you can also write your own applications that manage MSDE objects and data, using SQL-DMO.
You can also use ADOX ... see 4GuysFromRolla and the core reference for ADOX [MSDN Online].
Some articles on helping you build your own object/data tools: 15 Seconds and 4guysFromRolla.
OSQL command-line utility
To apply scripts (such as CREATE DATABASE, ALTER TABLE, EXEC sp_adduser, etc) you can use the command-line utility OSQL.exe, which comes with MSDE. To use, simply open a command prompt, and issue something like this:
For an article more deeply describing the use of OSQL, see KB #325003.
For a Microsoft example demonstrating how to backup an MSDE database using OSQL, see KB #241397.
If you're not familiar with using OSQL, or you are not familiar with generating .sql scripts, then you should have a copy of Books Online handy. See Article #2229 for information on obtaining the most recent version of Books Online.
3rd Party Tools
Some of these applications are web-based, and some are windows-based. All claim to be able to manage at least some aspect of SQL Server installations (and, by extension, MSDE) -- be it schema and objects or merely data management. I have not had a chance to review the majority of these tools.
Apex SQL Edit
Aqua Data Studio 4.0
ASP.Net Enterprise Manager
DbaMgr (Andrea Montanari)
ExtraSQL / MSDE Plus
Layton Database Manager
MSDE Management Tools (SQL Server 2000 Resource Kit)
MSDE Manager (Vale Software)
MSDE Manager (White Bear Consulting)
MS SQL Manager
Shusheng SQL Tool
Speed SQL IDE
SQL Anywhere Studio
SQL Tools Explorer
TeraTrax Database Manager
Toad for SQL Server
For a largish article on MSDE, see www.betav.com.
Related ArticlesAre there tools available for auditing changes to SQL Server data?
Can I create an index on a BIT column?
Can I have optional parameters to my stored procedures?
Can I implement an input mask in SQL Server?
Can I make SQL Server format dates and times for me?
Can I start IDENTITY values at a new seed?
Can SQL Server tell me which row was inserted most recently?
How can I learn more about undocumented SQL Server stored procedures?
How can I make my SQL queries case sensitive?
How do I audit changes to SQL Server data?
How do I connect to a non-default instance of SQL Server?
How do I connect to SQL Server on a port other than 1433?
How do I create a cross-tab (or "pivot") query?
How do I determine if a table exists in a SQL Server database?
How do I drop a SQL Server database?
How do I find all the available SQL Servers on my network?
How do I get a list of SQL Server tables and their row counts?
How do I get rid of Named Pipes / DBNMPNTW errors?
How do I get the correct date/time from the msdb.sysjob* tables?
How do I get the nth row in a SQL Server table?
How do I get the result of dynamic SQL into a variable?
How do I handle REPLACE() within an NTEXT column in SQL Server?
How do I hide system tables in SQL Server's Enterprise Manager?
How do I know which version of SQL Server I'm running?
How do I limit the number of rows returned in my resultset?
How do I load text or csv file data into SQL Server?
How do I manage changes in SQL Server objects?
How do I monitor SQL Server performance?
How do I prevent linked server errors?
How do I reclaim space in SQL Server?
How do I recover data from SQL Server's log files?
How do I search for special characters (e.g. %) in SQL Server?
How do I start SQL Server Agent from ASP?
How do I time my T-SQL code?
How do I upsize from Access to SQL Server?
How do I upsize my MSDE database to full-blown SQL Server 2000?
How do I use a variable in a TOP clause in SQL Server?
How do I use GETDATE() within a User-Defined Function (UDF)?
How should I store an IP address in SQL Server?
Schema: How do I find all the foreign keys in a database?
What are reserved Access, ODBC and SQL Server keywords?
What are the capacities of Access, SQL Server, and MSDE?
What are the main differences between Access and SQL Server?
What do I need to know about SQL Server 2000 SP4?
Where else can I learn about SQL Server?
Where is SP4 for SQL Server 2000?
Why am I having problems with SQL Server 2000 SP3 / SP3a?
Why can't I install SQL Server on Windows Server 2003?
Why can't I install SQL Server on Windows XP?
Why can't I use LIKE '%datepart%' queries?
Why do I get "Login failed for user '\'."?
Why do I get 'object could not be found' or 'invalid object name'?
Why do I get errors about master..spt_values?
Why do I get script errors in Enterprise Manager's 'taskpad' view?
Why do I get SQLSetConnectAttr Failed errors?
Why do I have problems with views after altering the base table?
Why does EM crash when I get an error in a stored procedure?
Why does Enterprise Manager return 'Invalid cursor state'?
Why does my DELETE query not work?
Why does sp_spaceused return inaccurate values?
Why is Enterprise Manager slow at expanding my database list?
Why is my app slow after upgrading from SQL Server 7 to 2000?
Why is tempdb full, and how can I prevent this from happening?
Why should I consider using an auxiliary calendar table?
Why should I consider using an auxiliary numbers table?