Hi !

I know that are many people who think a DBA should know only about database. Ok, I’m sorry, this is wrong
A good DBA should know about database, of course! But if you stop on this layer you will be a “dba” and not a “DBA”!
Is highly important, for a DBA, the minimal knowledge about the OS behavior, and all I/O systems, as storage, network and memory.

Today, I will give some basic explanation about RAID levels. Which was also a gap on my knowledge.
First, I need to mention that we have two options of RAID: Hardware RAID and Software RAID.
In summary, the Software RAID is managed by OS, which implies in one more task to compete with the services running on it. So, for SQL Server environments, this is not recommended. Thus, I will focus on Hardware RAID Levels.

But, what are RAID Levels? Basically are the way that disks are grouped together, which is directly related with the Recoverability and performance.

I will talk about the most important for us: RAID 0, RAID 1, RAID 5, RAID 6, RAID 10 and RAID 0+1.

RAID 0: Striping

• Splits the data, so each drive have an equal portion of the data
• If 1TB is need, the capacity can be split in N drives arranged in a RAID 0
• More drives added, the faster it will perform, but a higher risk of a data loss
• Rarely seen in SQL Server implementations

RAID 1: Mirroring

• Keeps the same data on two (or more) HDs at all times.
• Bad cost-benefit
• Performance advantage for read operations because the same data exists on all drives
• Depending of the RAID Controller and amount of cache, there may be a write penalty
  • In most case the penalty is minimal
  • When a drive fails no data is lost
    • The failed drive can be replaced and a new copy of the data will be build
    • During rebuild process, performance will be degraded
    • RAID Controllers can be configured to set lower or higher priority on rebuilds against OS queries
    • SQL Server Logs can be placed on this RAID level

RAID 5: Striping with Parity

• Similar to RAID 0 in that each drive can share an equal part of the data
• Adds a measure of data protection: Parity Data
• Calculating this parity data is processor-intensive
• The minimum number of drivers is three
• Some hardware RAID controllers can add additional drivers on the fly
• It cannot recover from the failure of more than one drive

RAID 6: Striping with Parity

• Similar to RAID 5
• But the failure problem is solved storing more parity data throughout the array

RAID 0+1 and RAID 10 (Nested RAID Leves)

• Store two identical copies of the data on two separate sets of drive
• What is the difference between RAID 0+1 and RAID 10?
  • RAID 0+1: the data is striped and then mirrored
    • Handles only the failure of a single drive in each side
    • Best practice for log files and tempdb
    • Better protection from hardware failure and better write performance
    • “In general RAID 1+0 will provide better throughput for write-intensive applications. The amount of performance gained will vary based on the HW vendor’s RAID implementations. Most common alternative to RAID 1+0 is RAID 5. Generally, RAID 1+0 provides better write performance than any other RAID level providing data protection, including RAID 5.“
  • RAID 10: The data is mirrored and then striped
  • • Handles the failure of multiple drives in the same array, without loss.
    • In write intensive applications such as database log or OLTP environments, is the better choice

So, this is it! I hope it was helpful as an introduction

References:
http://technet.microsoft.com/en-us/library/cc966534.aspx
http://en.wikipedia.org/wiki/RAID
Christian Bolton , Justin Langford , Brent Ozar , James Rowland-Jones, Jonathan Kehayias, Cindy Gross, Steven, “Professional SQL Server 2008 Internals and Troubleshooting”,  ISBN-10: 0470484284 | ISBN-13: 978-0470484289

Here is a very useful query to distinguish the number of hits for each table, based on the index usage. This query can be useful in “housekeeping” situations, or on a performance analysis.

The information takes in considering seeks, scans and lookups and outputs the date of the last operation of each kind.

It’s important to remark that this statistics are cleaned every time the service is restarted!

SELECT Object_name(object_id),
SUM(user_seeks)       user_seeks,
SUM(user_scans)       user_scans,
SUM(user_lookups)     user_lookups,
MAX(last_user_seek)   last_user_seek,
MAX(last_user_scan)   last_user_scan,
MAX(last_user_lookup) last_user_lookup
FROM   sys.dm_db_index_usage_stats
WHERE  database_id = Db_id()
GROUP  BY Object_name(object_id)
ORDER  BY SUM(user_seeks),
SUM(user_scans),
SUM(user_lookups)

Hi guys

The virtual launch sessions of the SQL Server 2012 are already available!

http://www.sqlserverlaunch.com/

I recommend the session about AlwaysOn and ColumnStore Index.

Good learning for you

Challenge:
Migrate a table in a MySQL database to a table in a database in SQL Server.

Problem 1:
A column of type “Text” on the side of MySQL.
Solution 1:
Create a column of type “Text”, in this case isn’t Unicode, in SQL Server table side.

Problem 2:
In the package, I cannot assign values ​​to respective OutputBuffer column of type “Text” (source for SQL Server). The “ReadOnly error”.
Solution 2:
You should assign values ​​to this type of columns as follows (You should change the encoding type!):

OutputBuffer..AddBlobData(Encoding.Default.GetBytes(“<SOME_STRING>”);

Problem 3:
How to read the contents of a “Text” column type (in MySQL side), returning a String?
Solution 3:
The following strategy should be used:

string str_notes = System.Text.Encoding.Unicode.GetString(Row.<COLUMN_NAME>.GetBlobData(0, Convert.ToInt32(Row.<COLUMN_NAME>.Length)));

I hope it helps and save you time

A DBA is focused in maintain a database system running, accessible and recoverable in any moment. Obviously, the data is the motivation of a database system and have to be safe in all aspects. As commonly known, a database administrator is faced with very sensitive data, and is not easy to know who is responsible for what…So what are the borders that distinguish the DBA responsibilities?

In summary, is a DBA onus maintains total attention on the following points:

• Security
  The database server security is not a meaning of anti-hacking system (only). Through the operating system is possible to reach the database server and its resources, and through the database server is possible to reach the operation system. For this reason, is extremely important the teamwork between system administrators and database administrators.
• Isolation
  This point could be a part of security, however is particularly important to isolate the access to each single database in the server, even though it is owned by the same entity.
• Recoverability
  As the data is stored in database system managed files, it is required (most cases mandatory) for the DBA to maintainto maintain a reliable backup strategy implemented, to recover the databases when is needed.
• Confidentiality
  All the applications have to be owned by someone at some point, and only these (or those) people can allow changes in the data and sending chunks of data or even the entire database.

Is normal to a DBA be faced with data corruption. It can be caused by some database engine fail, by a “wicked” DML/DDL execution or even by the application itself. To take care of data, and protect the DBA, some important rules have to be created:

• Data Changes (DML scripts)
  Is mandatory to any data change be authorized by the application’s responsible. A DBA cannot change the data without the corresponding permission. Adding to it, every data change have to be requested by the application owner and the DML scripts sent to the DBA team to be approved and executed without online modifications.
• Structure Changes (DDL scripts)
  A DDL script doesn’t change data directly, however, it can indirectly break the database integrity, corrupt data or carry performance problems in a non-distant future, because of it, a DBA’s verification before a creation or change of any database object is of high importance subject and must be followed carefully by the DBA.

The objective of this article was to demonstrate some ideas, based on experience, of possible points of protection, because as we know, the DBA is always the first in the line of fire…

Developing each of these point, will help to improve the QoS and protect the DBA team of possible mistakes.

If you have more ideias, critics or suggestions, do not hesitate to contact me!