This whole week I am in Sybase training in NYC. I will write down short posts of mostly differences between Sybase and SQL Server each day and also what I have learned.
First here is the awesome view from the Sybase office in the Grace building in New York City on the 32 floor
Here are the materials for this week of training.
One of the biggest differences between SQL Server and Sybase is of course that Sybase runs on Linus, Unix, Mac and Windows, SQL Server only runs on Windows.
SQL Server has the following system databases
resource(a hidden db,files are mssqlsystemresource.mdf and mssqlsystemresource.ldf))
Sybase has the following system databases
sybsystemdb(stores information about distributed transactions)
sybsystemprocs(Sybase system procedures are stored in the database sybsystemproc)
Certain system databases go on certain database devices, depending on the version these devices change, these devices are sysprocsdev, systemdbdev , master and tempdev
Interestingly enough, Sybase still installs with the sa account with a blank password. After the installation is done you need to change the password(Yeah you better or something like SQL Slammer for Sybase might bite you later on)
I learned about the various files and directories that are needed for a Sybase installation.
When installing Sybase, you install the following things with a typical install
Adaptive Server (this is the data engine)
A custom install would also give you the option of installing the Job Scheduler, the Job Scheduler is probably something like SQL Agent. The instructor said it is heavy weight, it uses a lot of process power.
Sybase uses something called an interface file, this file lists the server and port of every known server
The file looks like this
ServerName master tcp ether ServerName 5001 query tcp ether ServerName 5001
The interface file lives in the $SYBASE directory
SQL Server only has 8K files, Sybase has 2K, 4K, 8K, 16k files.
This sounds great, however the page size is per server and if on one server the page size is 2K and the other server has 4K then you can’t backup and restore the database on the other server, you need to script out the DB and then bcp all the data over, there was also another way mentioned by manipulating files.
Sybase also has no mixed extents, an 8K master database will be 4 times bigger than a 2K master database.
Server + Kernel
This is something like number of open databases,open objects, open indexes, open partitions, number of locks
This is done with the sp_config stored procedure, all these configurations are written in the ServerName.cfg file (in theory you can change it right there but if you add a bad value you might mess up your server and it won’t start).
The sp_monitorconfig will show you the usage statistics. The procedure sp_monitorconfig displays cache usage statistics regarding metadata descriptors for indexes, objects, and databases. sp_monitorconfig also reports statistics on auxiliary scan descriptors used for referential integrity queries, and usage statistics for transaction descriptors and DTX participants.