THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Sybase ASE

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.


Published Friday, December 10, 2010 10:39 PM by Linchi Shea
Filed under:



Ben Thul said:

December 11, 2010 12:42 AM

Linchi Shea said:


you mentioned file level mirroring. I'm not sure it's something with real value since it's much simpler and more robust to delegate that to the hardware.

December 11, 2010 2:59 PM

Ben Thul said:


Perhaps.  The coolest part about that feature for me was the ability to move it to a new disk with zero downtime.  We moved a database from one SAN to another and the database didn't even hiccup.  Instead of the usual "under cover of darkness", we were able to do it during the day.  That, to me, is worth something.

December 13, 2010 1:09 PM

sybanva said:

There is good blog for sybase & Online training is also available for Sybase ASE n Replication Server,

December 22, 2010 7:54 AM

David BAFFALEUF said:

I agree that Adaptive Server has made little improvements in 10 years compared to SQL Server. Still there are some major features in ASE I would like to see in SQL, like Archive Database Access for example, or the new shared disk cluster feature. On the other hand, I would like to have a consistent DBCC CHECKDB on ASE, I still get some spurious allocations errors every monday because the database is not in single user mode when checking the allocation map.

It is unfortunate that Sybase seems to neglect its RDBMS and focus more on mobile applications, ASE is a damn good storage engine, with a very smart volcano-based query opt, and is shipped for almost every enterprise-class operating system. For ten years I've seen it outperforming Oracle on pure OLTP queries. I think it will still be around for a while...

Thanks for your post !

January 21, 2011 12:16 PM
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement