- Introduction
- Structured Query Language (SQL) Support
- Analysis Services (OLAP)
- General Enhancements
- What Could Have Been Done Better?
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
What Could Have Been Done Better?
While SQL Server is a very rich and full product, it does have a number of limitations—some obvious, some a little less so until you've had a chance to really come to grips with the product. Some of the problems I see in SQL Server 2000 are described in the following sections.
Support Only for Microsoft Windows
Almost all Windows operating systems are supported for SQL Server 2000, the one notable exception being Windows 95. If you have anything other than Microsoft Windows as your OS platform, however, forget all the niceties of SQL Server 2000, and instead look to another RDBMS vendor. It's obvious why Microsoft has taken this course of action, but it would be nice to have the ability to install on other operating systems.
NOTE
Not all editions of SQL Server will install on all versions of Windows; for instance, Enterprise Edition will not install on Windows 2000 Professional.
No Native Load-Balancing Capabilities
While it's possible to achieve a "load-balanced" solution with horizontal data portioning (see the section "Availability Features" in my previous article in this series), it isn't true load balancing. There's no logic that analyses the load on one server and then passes the request to another based on the results of the analysis. This isn't to say that you can't achieve this with SQL Server, but you need to implement your own solution, such as Cisco's Content Services Switch. There is no built-in support for load balancing yet.
No Version Control on Database Objects
As a former developer, I felt one shortcoming of SQL Server 2000 is the fact that there's no versioning support of stored procedures, views, or even any Data Definition Language (DDL). You have to script your database and objects, and then use a product such as Visual SourceSafe (VSS) to version the scripts. Not so good if you have developers who come along and change the structure slightly, without updating the external source control repository.
Poor Performance and Configuration Out of the Box
Most DBAs know that to improve performance of your RDBMS, you should separate your program, data, and transaction log files onto physically separate drives. With SQL Server 2000, you can split the data and program files from each other at installation time, but the transaction log files are installed by default with the data partition! Little wonder that you hear people complain that SQL Server 2000 is slow; they probably haven't effectively separated the data and log files.
To split the data and transaction log files from each other, you need to follow the instructions in Microsoft's article Q244071. This procedure is fairly easy to follow, but be aware that you need to follow it to the letter, or you could easily corrupt your installation of SQL Server, and need to do a full reinstall.
Cannot Read Transaction Log Files
There is no capability with SQL Server's own tools to read the transaction logs. This means that you need to either buy a third-party tool, or take a wild guess and restore the database to a point in time. The ability to read the transaction logs would be really valuable for debugging purposes.
Debatable Granularity Offered with Security Model
While security is fairly strong within SQL Server, it's perhaps not the biggest mitigating factor when considering the reasons for using the product. Yes, you can secure tables, views, and stored procedures; however, when it comes to data you're restricted to column-level (or attribute-level) security. You can't natively implement row-level (or tuple-level) security within SQL Server. This requires writing custom code and having the necessary skill to restrict access based on data requirements. Having said this, though, there are not many times when you want to secure data to a specific user.
Low Number of Security Accreditations
SQL Server 2000 has one security accreditation, with the ability to provide what once was known as C2 auditing. This stacks up against IBM's DB2, which currently doesn't offer any, but falls very short of Oracle 9i's security ratings.
No Support for Java
Microsoft's position on Java is well known. Unfortunately, this has meant that your Java code is not portable from other RDBMSs. Code that has been written for your DB2 or Oracle installation will need to be translated into T-SQL to be used within SQL Server. This makes the migration path that much harder when moving from one RDBMS to another.
In fact, SQL Server 2000 doesn't offer any support for any programming language, except of course SQL. In the next release, codenamed "Yukon," there is talk of giving developers the ability to create stored procedures in VBScript. Currently, to use VBScript in SQL Server 2000, you need to develop DTS packages that interface with the RDBMS.