Here's a SQL that beats the original
Improved Microsoft SQL Server is poised for enterprise tasks| GCN Lab
- By Greg Crowe
- Jan 18, 2006
Reviewer's Choice |
Microsoft SQL Server has long been a popular database program, although it hasn't been widely deployed for large, enterprise-class data stores the way Oracle and IBM DB2 have. SQL Server 2005 Enterprise Edition is meant to change all that, and after testing it in the GCN Lab, we're largely pleased with what we found.
One improvement that users of the 2000 version will notice right away is the Server Management Studio, which replaces the old Enterprise Manager and some auxiliary programs. We found Studio to be easier to use, allowing us to create a new database quickly. Connecting to external databases is much easier in SQL Server 2005 thanks to the integration of the Client Network Utility; administrators don't even have to create an Open Database Connectivity link first.
Also included are SQL Management Objects, which are programming objects that let you automate certain tasks, such as creating new databases or applying scripts. In fact, Microsoft built the Server Management Studio with SMO, so you can get a good idea of what's possible with these tools.
We were glad to see that the new version of SQL Server is fully integrated with Microsoft's new Visual Studio 2005 programming language, a powerful software upgrade in its own right. The tight bond can help you keep better track database projects and develop your own database-driven applications more easily.
SQL 2005's reporting, notification and analysis services, to name a few, are also fully integrated into both the Management Studio and Visual Studio. These services let you more readily perform auxiliary functions such as report generation and user notification.
More than just the front-end consoles and tool sets have been upgraded in SQL Server 2005. Many of the technologies used to store, retrieve and secure the data itself are either improved or totally new.
One new technique is the ability to take a database 'snapshot.' This is a read-only view of the database that takes up much less disk space than a full copy. It's a replication method that adds options to your choice of backup procedures. The main disadvantage is that the view is static and quickly goes out of date when the live database is modified. You should only use snapshots when your users make their changes to the database at specific intervals (i.e. weekly or monthly), after which you could make a new snapshot.
In addition to the password authentication and native encryption that you would expect in a database server, SQL 2005 allows you to get very specific when setting permissions. The levels of permissions are much more granular, and the scope can be defined quite accurately. You can even prevent a single statement in a program module from executing unless certain conditions are met. This keeps a user or type of user from having more permissions than he or she needs, which continues to be a security concern no matter what the application.Real-world performance tests
We found no appreciable difference in the amount of disk space that a SQL 2005 database takes up compared with the same database created in SQL 2000. It is possible, however, that the databases we tested were not large enough to show a sizable difference, and that you might notice an improvement if your databases are large enough.
The same can be said about the reliability and performance of SQL Server 2005 compared to previous versions. At low traffic levels, we noticed little difference between 2005 and 2000. However, administrators of databases that get multiple near-simultaneous requests for data should notice some level of improvement.
The Enterprise Edition of SQL Server 2005 can be installed on any server running Windows Server 2000 with Service Pack 4 or Windows Server 2003 with Service Pack 1. Although Microsoft says that it can function with a minimum of 512MB of memory, we found this can cause slow response times with larger databases. We suggest that your server have, at a minimum, 1GB of memory.
The Enterprise Edition is equipped to utilize an unlimited amount of memory and an unlimited number of processors. While SQL 2005 is optimized for the 64-bit Intel Itanium processor, it also works great with other 64-bit processors, as well as pervasive 32-bit chips. Microsoft claims that there is no limit on the size of the database SQL 2005 can support. While it certainly handled every test database we threw at it, we'd recommend you test Microsoft's claims with some of your very large data sets to make sure.
We found pricing to be a little confusing, but in line with competing products. Whether it's ultimately cheaper to go with a per-processor license ($24,999) or a server (i.e. per-client access) license ($13,969 for 25 client access licenses) will depend on your setup and workload. Keep in mind that the Enterprise Edition is the top-of-the-line version, so if it's too pricey and your needs are more modest, check out other flavors.
SQL Server 2005 is not for the casual database publisher. But if you have large, complicated databases accessed frequently by many users, it might be just what you need.
Greg Crowe is a former GCN staff writer who covered mobile technology.