I sat in a Sybase ASE class last week for five days. Although it didn't cover the more advanced features introduced in the more recent versions of Sybase ASE, the class did touch all the basics of administering Sybase ASE. While I was successful in suppressing any urge to openly compare Sybase ASE with Microsoft SQL Server in the class, I could not help making mental notes on the differences between the two database platforms.
It's always interesting to look at how two DBMS platforms that share the same root went their own different ways in handling the same/similar tasks. And here are some random notes I jotted down while sitting in the class.
I had some limited experience with Sybase about 15 years ago, and had expected to see some major improvements in doing the basic tasks of managing a Sybase ASE instance. So I was surprised that very little had changed and it felt like a throwback to SQL Server 4.21a or 6.5. For those of you who never used SQL Server versions earlier than 7.0, you'd probably have a hard time understanding why having a good database backup file alone is not enough to get the database restored. Instead, you must first create the database in exactly the same layout as when the backup is taken. If you don't know that layout, there is a good chance that you won't get your database back even if you have the backup. As a result, religiously backing up your master database or religiously keeping your database creation scripts up to date become paramount important, much more important than it is with SQL Server. Having seeing a better alternative, this all seemed rather archaic to me.
SQL Server 2005 and later implemented a feature that allows a database to be online to the user as soon as the redo step is finished during the instance startup. A database on a Sybase ASE instance still needs to wait until both the redo phase and the undo phase are completed before it can accept user connections. I guess to help ease the pain, Sybase ASE allows the user to specify the order in which the databases on an instance are recovered. So if database XYZ is the most important, you could configure it to be recovered first before all the other user databases. This is nice and would be good to have on SQL Server. But because the SQL Server databases can come online faster, the feature is not as useful on SQL Server.
Like SQL Server, Sybase ASE also supports autogrowing the database space allocation. And it doesn't encourage anyone to rely on the feature just as it is discouraged on SQL Server. But because knowing exactly how space is allocated for a database is so much more critical on Sybase ASE than it is on SQL Server for recovery purposes, using autogrowth is therefore much more dangerous on Sybase ASE.
One nice thing about Sybase ASE is that all its sp_configure options are exposed in a configuration file which is a plain text file. You can still use sp_configure just like you do on SQL Server, but all the configuration changes are saved to the configuration file. The main motivation for externalizing the sp_configure options to a configuration file is that on Sybase ASE you could easily mis-configure an option causing the Sybase ASE to fail to start up. With the sp_configure options in a file, you can easily correct such a mistake and restart the instance without having to start the instance in some special mode just to correct the mistake. On SQL Server, this feature is less useful because you can hardly mis-configure a sp_configure option and cause the SQL Server instance to fail to start.