Interesting Information on Windows “Blue Screen”

Reasons for Windows Crash

Your Windows system / server can crash due to:
- memory access violation,
- kernel subsystem inconsistencies
- driver operational (e.g. USB, display) inconsistencies
One might argue that Widows might as well (in some cases) isolate the hardware and continue operations, but chances are that it might (generally does) lead to memory space inconsistencies, and hence Windows follows a "fail fast" policy attempting to prevent corruption in RAM from spreading to disk.

The Blue Screen

Could be any reason, but actual function responsible for (managing?) system crash is KeBugCheckEx. It makes use of a "stop code" and four parameters which are interpreted on a per-stop basis. This function then switches resolution to VGA mode with blue background and some comments on what user shall do next. It then calls for KeRegisterBugCheckCallback function allowing drivers opportunity to stop. It then calls KeRegisterCheckBugReasonCallback so drivers can append data to the crash dump.

To summarize:

KeCheckBugEx -> paints the screen blue -> KeRegisterCheckBugCallback -> KeRegisterCheckBugReasonCallback.

In rare occurrences, even the blue screen display stage isn’t reached and server crashes before that.

SQL Server 2012 Removed SOAP/HTTP Endpoint

Since our childhood days (pun intended), we have learnt our ancestors telling us that a Database (or any Data-heavy computing layer) should be hosted independent of others. That is, host Database Servers on one/more servers, and don’t share the same servers for other application/web computing. SQL Server® for some reasons, kept the same standard best practice as a recommendation, but at same time rolled out SOAP/HTTP endpoint in SQL Server 2005. But finally, Microsoft® has finally realized for the issue (or it not being adopted much) and has decided to drop off SOAP/HTTP endpoint in SQL Server 2012 (Denali).

To me, SOAP/HTTP has always been a confusing decision (though now, the response for its rollback is equally confusing). You have a .NET based environment and generally accompanied with .NET / WCF Application servers where you could develop full fledged web services. For pure reporting functions, you had SQL Server Reporting Services. So as a Data Architect, it was always difficult to draw the line on where exactly we go about SOAP/HTTP (and I admit, I recommended my teams to keep presentation or decision logic to be on App/Web layers, and keeping Data masking/handling/filtering/logic etc. at Data layer; recommending a clear demarcation among each layer). Not to mention, clear limitations of SOAP/HTTP were also key contributors to the decision. The only viable use that it offered (in my view) was that you could SP directly wrapped within a webmethod directly – but really how many are such cases where you would want that !?

So, am glad a decision has finally been reached to end this confusion. With SQL Server mainstream (for quite some time now in fact), its pivotal to have clear vision and to have efforts spent on right spots, and getting rid of what’s nothing but dead weight. SQL Server Reporting Services is already a robust piece, but yet a lot lacking if we look at other Reporting Tools available in the market. Those who had the understanding / rule of thumb on keeping application functions segregated, I think they shall continue to remember what they already know, and should try forgetting SOAP/HTTP (from within SQL Server).

SQL Server Connection Protocols

Amidst of all the jazzy terms and technical stuff, there are times when we simply miss out taking a note of building blocks on top of which our IT systems are breathing. One such topic is that of SQL Server Connection Protocols. Am sure most of us remember the four key protocols, but at times must have felt a void around what they really mean or what’s their clear line of segregation. That’s what pushed me to put this note together.

Four Key connection Protocols for SQL Server are/were:

- Shared Memory

- Named Pipes

- TCP/IP

- VIA (to be depreciated in future releases).

Below is a quick write up for more details on each of them.

TCP/IP

By default, the default protocol for SQL Server clients is TCP/IP. If the connection cannot be made using TCP/IP, the other enabled protocols are attempted.
Connection String Format:
Alias Name   <serveralias>
Port              <blank or Pipe name>
Protocol        TCP/IP
Server          <servername or IP or Localhost>\<InstanceName>

Named Pipes

Both on Windows and POSIX systems, named-pipes provide a way for inter-process communication to occur among processes running on the same machine. What named pipes give you is a way to send your data without having the performance penalty of involving the network stack.
By setting up additional named pipes, you can have multiple DB servers running, each with its own request listeners. The advantage of named pipes is that it is usually much faster, and frees up network stack resources.
– BTW, in the Windows world, you can also have named pipes to remote machines — but in that case, the named pipe is transported over TCP/IP, so you will lose performance. Use named pipes for local machine communication.
For named pipes, network communications are typically more interactive. A peer does not send data until another peer asks for it using a read command. A network read typically involves a series of peek named pipes messages before it starts to read the data. These can be very costly in a slow network and cause excessive network traffic, which in turn affects other network clients.
For TCP/IP Sockets, data transmissions are more streamlined and have less overhead. Data transmissions can also take advantage of TCP/IP Sockets performance enhancement mechanisms such as windowing, delayed acknowledgements, and so on. This can be very helpful in a slow network. Depending on the type of applications, such performance differences can be significant. TCP/IP Sockets also support a backlog queue. This can provide a limited smoothing effect compared to named pipes that could lead to pipe-busy errors when you are trying to connect to SQL Server.
It is also important to clarify if you are talking about local pipes or network pipes. If the server application is running locally on the computer that is running an instance of SQL Server, the local Named Pipes protocol is an option. Local named pipes runs in kernel mode and is very fast.
Default Pipe for a named Instance: \\<computer_name>\pipe\MSSQL$<instance_name>\sql\query
Default Pipe for a default Instance: \\.\pipe\sql\query
Connection String Format:
Alias Name  <serveralias>
Pipe Name   <blank or Pipe name>
Protocol    Named Pipes
Server      <servername or IP or Localhost>

Shared Memory

When you connect to the Database Engine from a local client (the client application and SQL Server are on the same computer), the connection uses shared memory protocol by default. Formats: “”, “.”, “\”, “(local)\”. “localhost”, “.\namedInstance”

For connecting locally but using a different protocol, use:
“servername.domain.com”, “tcp:*”, “np:\\<computer_name>\pipe\<pipename>” etc.

Named Pipes is a protocol developed for local area networks. A part of memory is used by one process to pass information to another process, so that the output of one is the input of the other. The second process can be local (on the same computer as the first.

VIA

Virtual Interface Adapter (VIA) protocol works with VIA hardware. The VIA protocol is deprecated. This feature will be removed in a future version.
Connection String Format:
Alias Name    <serveralias>
Port        0:1433
Protocol    VIA
Server      <servername >\<InstanceName>

SQL Server 2012 Licensing

The bull is out now.

If you arent yet aware, read the Official Declaration is here. A lot has already been written and speculated so far. I wouldn’t add to it any further.

Key points (read, my interpretations) from Licensing Guide are below.

a. Computing power based licensing now costed on basis of cores, instead of processors. Its to make costing simplified for phyiscal and cloud based setups. (earlier it literally were like two seperate lines, one for physical setup, and other for virtualized setups). And the bummer now. You will have to buy 4-core license, at the least! It only means that you will continue to cost almost same as if using SQLServer2008 when you are using quad-core processors. If you are using more cores, then you will have to pay more.
b. Workgroup, Small Business, Datacenter editions retired. Glad to hear that to be honest, the edition listing was literally too long earlier. You now have Enterprise (all inclusive), Business Intellignce (Std + Corporate BI features), Standard (basic Database Engine + Standard BI), Developer, Express, Compact, and Web. I personally still feel that Express/Compact could have been merged into one, but still, at least some clarity has been looped in. Yet though, key point am not able to get my around is that why BI edition is available in Server+CAL licensing only. If you want to go for Core licensing for BI (for cost benefits), go for EE.
c. Virtualized environments can be licensed in two modes: Individual VMs, or maximum virtualization (in case of highly virtualized platforms).
d. VM based licensing follows standard stand-alone server licensing as in point#b. For intra-serverfarm/host provider/cloud movement for a VM, you will have to procure "Software Assurance" along with core licenses.
e. For Maximum virtualization, one shall procure SQL Server Core licenses + Software Assurance for whole server farm.
f. Current clients for EE can upgrade to SQL2012 at no additional cost, with following caveat:
    – it shall be done by 30/06/2012
    – if you are running SQL2008R2 with >20 cores, contact your MS representative for transitioning licensing mode
g. For moving current processor license to core licensing
    – SE/EE per processor under Software Assurance (SA) will be exchanged with 4core licenses per processor, and
    – DataCenter Edition per processor under SA will be exchanged with 8core EE licenses per processor.

MySQL– Backups

Different types of Backups for MySQL databases

Logical Backup

It is done by dumping the contents of database into text files containing SQL statements which can be used to rebuild the database. They can be used even to build it on a host with different architecture and with different engine. But they are generally slower than the raw kind, for both backup and recovery operations. And they can be larger than the actual data too. It can be performed using the mysqldump utility.

Raw (Binary) Backup

It involves only file copy operations, so can be very fast to perform backup or recoveries. The engine type of tables cannot be changed in this method, as it preserves the actual data format. Depending on engine type, it can be used to take Cold, Hot or Warm backups. Various tools are available for this operation, which will be covered in a slide ahead.

Snapshot Based Backup

This kind of backup uses external utilities to take file system snapshot of MySQL. For InnoDB, a hot backup can be performed using this, and for other engines, a warm backup.

Replication-Based Backup

A copy of the Primary installation can be maintained on a separate server using a Replication setup. Using this method, an exact replica of the databases can be maintained separately, and can be used for backup purposes. But it is comparatively an expensive option.

Incremental Backup

The binary logs, which contain a record of all changes done on the database can be backed up for the  purpose of doing point-in-time-recovery. Using tools like mysqlbinlog, databases can be recovered from Binary logs till a certain point in time, or certain log position also. Binary logging should be enabled on MySQL servers

Backup Tools

Though backups can be taken without the use of any tool, there are some tools designed specifically to make the operation easier. Some of them have been described here

mysqlhotcopy

Provided with the MySQL distribution, this is useful to perform raw backup of MyISAM type tables only. Contrary to its name, it is not exactly a “hot” backup, as database is not fully available during the operation, but is available only for read. It is locked and cannot be altered. So, it is also called a “warm” backup.

mysqldump

It is provided with the MySQL distribution. It helps in taking logical backups for any kind of engine. Our  current masters are designed to automatically enable this backup in cron at the time of installation.

MySQL Administrator

It is available as a separate GUI download from MySQL, and can be used to perform logical backups. It can also be used with any database engine, and has some tracking capabilities also.

InnoDB Hot Backup (ibbackup)

It is a commercial product available from InnoDB. It performs raw backups of MySQL databases using InnoDB engine only. It is in fact a real “hot backup” as databases are completely available for read and update during the operation.

Third Party Tools

Among the many commercially available third party tools available, there is one called Zmanda Recovery Manager, that can perform both logical and raw backups for all engine types. It can do both warm and hot backups, and can drive Replication and Snapshot based backups too. It takes consistent backups, and has extensive reporting and Tracking capabilities

MySQL– Performance Features

Some of the features provided by MySQL that help to make it a high performing and fast responsive server 

• Flexibility to choose the most appropriate Storage Engine as per performance requirements. A table’s Storage Engine can be changed later also, using the ALTER TABLE syntax. 
• The feature EXPLAIN PLAN can be used to find out the actual path used by a query to fetch data from tables, so that query optimization can be performed. 
  Flexibility to choose the most appropriate data type as per performance and storage requirements. For example, a number type of data can be represented by INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT, FLOAT or DOUBLE, depending on its characteristics and the range of values it can have, and consequently, the storage requirement for each is also different (1byte, 2bytes, 3bytes, 4bytes etc) 
• Table maintenance utilities like mysqlcheck can be used to perform activities like checking, repairing, analyzing and optimizing tables to help improve performance. There are commands available too, like Analyze table, or Optimize table, that can be used to update key value distribution or reclaim unused space.  
• For string data types, it is also possible to index on a column prefix rather than the entire width of it, which means that it is possible to create an index on a specified width of a column. For example, if a name column is of 255 characters, and using a query, we find that the first 10 characters of each row are sufficient to obtain distinct values for most of them, then an index can be created using only the first 10 characters of each row. This will not only allow more values to be cached in memory due to its small size, but also can improve index performance dramatically.  
• The Leftmost Index prefixing feature can be used to avoid unnecessary indexes on tables. For example, if a composite index is created on columns A and B of a table (in the same sequence), then if a query requires an index on column A, it can use the same composite index without the need for a separate index.  However, if the sequence had been different in composite index, or had the other query needed an index on B, then we would require a separate index.  
• MySQL also provides Engine specific optimizations. For example, the following optimizations are possible on MyISAM tables
• Tables can be defined to have fixed-length or dynamic-length row format. Fixed-length allows data to be stored at a multiple of the row size resulting in faster access, whereas dynamic-row columns occupy smaller space, but can cause fragmentation.
• Read-only tables can be compressed to a very small size using myisampack utility. The compression is done in an optimized form that allows quick retrievals.
• It is also possible to split a Dynamic-row table into two separate tables (one fixed-length and the other dynamic length) keeping the Primary Key as common to both, so as to gain the advantages of  both types. This is usually considered when most queries access the fixed length columns of a dynamic row table.
• To distribute disk activity, symlinking can be used to move MyISAM tables to different disks.
• The MySQL server commands like STATUS can be used to obtain a snapshot report of current server status, showing its complete details, like current load, slow queries, open tables etc.
• The server parameters that define various cache and buffer sizes can also be tuned as per performance requirements  

MySQL– User Security

MySQL security is based on the Access Control List for all connections, queries and other operations that a user performs. Some of the features that MySQL provides as security measures
* Every user created in MySQL is characterized by its Username, Password and the value of Host from which it would establish connection. Though wildcards are allowed, it can be used to restrict access to specific users from specific hosts, thus disallowing unauthorized access.
* MySQL encrypts passwords using its own algorithm. It is a one-way operation, and no means are provided to decrypt an encrypted password
* MySQL supports SSL encrypted connection between a server and a client
* MySQL provides a very flexible Privilege system, using which each user can be granted only specific privileges that are required for its operation, thus limiting each user’s access to data to within its scope. These are implemented using the GRANT and REVOKE commands. Privileges can be granted at the Global level, database level, table level, column level or even at the routine level (Functions & Procedures)
* MySQL provides a script for secure installation (in Linux), which provides options for setting up of root password, removing anonymous accounts and test database, and removing root accounts accessible from outside
* Certain parameters provided by MySQL can be used to put some level of restrictions on Client connections. For example,
    max_user_connections -> max no. of simultaneous connections by a user

User security consists of 5 access levels that incrementally refine the privileges on user account.
* user        ->    level 1
* db           ->    level 2
* tables    ->    level 3
* columns    ->    level 4
* procs     ->    level 5           
Privileges can be granted at these levels to control the level of access to databases for users.

For example, a global access privilege can allow a user to create users, lock table, view processes or even shutdown a MySQL server, whereas, if that user’s privileges are restricted to selection of records from tables of a particular database, the user can do no more than run select queries on those particular tables from those particular databases.

MySQL–InnoDB Storage Engine

• Developed by Innobase, which is a subsidiary of Oracle.

• Each table is represented by a .frm (containing table structure) file inside database directory.

• The InnoDB tablespace, which is a set of one or more files, is used for storing table contents. It is usually a shared tablespace for InnoDB tables common for all databases, but can also be configured to have each table with own tablespace. The shared tablespace is created by default, in the data directory (eg by the name ibdata1). It is defined in the [mysqld] section of the file /etc/my.cnf using parameter innodb_data_file_path

e.g. innodb_data_file_path = ibdata1:10M:autoextend

where filename is ibdata1, its size being 10M and in autoextend ON mode.

• There are InnoDB log files that record transaction activity, located in the logs directory with default names as ib_logfile0, ib_logfile1 etc. Some innodb related parameters are

innodb_data_home_dir = /data/mysql/5.0.75/data

innodb_log_group_home_dir = /data/mysql/5.0.75/logs

innodb_buffer_pool_size = 16M

innodb_log_file_size = 5M

innodb_log_buffer_size = 8M

• Supports transactions with COMMIT, SAVEPOINT and ROLLBACK.

• InnoDB also maintains a Buffer Pool and Log Buffer which help to improve performance and enable crash recovery of tables

 

PROS

• ACID compliant

• Support for crash recovery

• Row level locking

• Support for foreign keys

• B+Tree and Hash Indexes support

• Adcanced memory cache mechanism

CONS

• No Full Text index support

• Requires more disk and memory resources

Why run 64-bit Windows?

Am sure by now majority of the platforms are and considering moving on to 64-bit platforms. If you aren’t yet sure of why’s – boy, you really are planning for a retirement, aren’t you !? No, its not mandated, but if scalability, vendor supportability and performance bothers you, then better think about it.

Now, there’s no point in re-writing what more experienced people have already summarized in way more details. But, here’s the reference for your considerations:

- Why run 64-bit Windows?

- Why run 64-bit Linux?

Now, amidst x86 and 64-bit decision, Itanium yet remains a question mark? HP and Intel have committed that they will continue to develop on it. But they dont seem to be showing anything promising beyond next planned version. Couple of Database Vendors (Oracle, Microsoft) have called out for Itanium phase-out already. Though others (IBM DB2, PostgreSQL, Sybase) are absorbing this as an opportunity to increase their footprint. If you ask me, I never liked IA platform much. Not that its bad anyhow, but it broke the most crucial backward compatibility. That’s a big no-no when you talk of Enterprise platforms. And that proved to be one reason that Industry was slow to get along with this Intel-HP development. Specifically, given today’s situation, it would be wise to look at what your data-vendor is supporting and what goes well within your existing/planned server farm.

MySQL–MyISAM Storage Engine

MyISAM is the default in MySQL. Each table in it is represented by three files:

• format file – which stores the table definition (eg mytable.frm)

• data file – which stores the table row contentcs (eg mytable.MYD)

• index file – which stores the indexes of that table (eg mytable.MYI )

It has the capability to store rows in three formats

• Fixed Row format – Constant row size for all rows.

• Dynamic Row format – Rows take varying amounts of space

• Compressed format – Tables are packed and made read-only. Optimized for quick retrieval.

PROS

• Low Storage Costs

• Support for BTree, Fulltext Indexes

• Very fast insert and query performance

CONS

• Table level locking

• No support for foreign key constraints

• Does not support transactions

 

Further Reference: http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html