SQL server Cluster do and don´ts

It worth reading, but still this is not what i´m looking for

http://support.microsoft.com/kb/254321/en-us

Happy New Year!

I know I know… this year this blog had few updates. Indeed.  Still, I might put on the table a lot of arguments. At my previous company I actually had a bit more of time to write down my ideas and discovery, here at my new job I have lesser hours to work on it. Besides, with a few family issues, it was a bit more difficult to get focused on it.

But here comes 2011, and I have big plans, get back to teaching classes, taking control of my ADHD, and writing new articles on my Blog.

For all of you I convey my best wishes of a brilliant and sucessful new year!

Cheers!

Detecting High CPU Usage

SELECT TOP 50
        qs.total_worker_time/qs.execution_count as [Avg CPU Time],
        SUBSTRING(qt.text,qs.statement_start_offset/2,
            (case when qs.statement_end_offset = -1
            then len(convert(nvarchar(max), qt.text)) * 2
            else qs.statement_end_offset end -qs.statement_start_offset)/2)
        as query_text,
        qt.dbid, dbname=db_name(qt.dbid),
        qt.objectid
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY
        [Avg CPU Time] DESC

Moving System databases walkthrough

Moving System Databases is a delicate process, get rid of everyone around you, specially your manager. :)

The Basics

1. First verify where are the files, and to where you got to move them.

2. Make sure to create on the destination the proper folders for sql server data.

3. Ensure that the service account has ntfs permissions over those folders. Permissions are always a problem, if you forget it, sql server cannot put the databases online once it has no access to the files… remember that.

First the easy part

Moving the tempdb, Model, and Msdb

--use master
--Select name, filename from sysaltfiles where name not like 'mast%'  -- sysaltfiles shows all the data files for all the databases… very handy

USE MASTER

ALTER DATABASE tempdb modify file (NAME='tempdev', FILENAME='newlocation\Data\tempdb.mdf')
ALTER DATABASE tempdb modify file (NAME='templog', FILENAME='newlocation\Data\templog.ldf')

ALTER DATABASE model modify file (NAME='modeldev', FILENAME='newlocation\data\model.mdf')
ALTER DATABASE model modify file (NAME='modellog', FILENAME='newlocation\data\modellog.ldf')

ALTER DATABASE MSDB modify file (NAME='MSDBData', FILENAME='newlocation\data\msdbdata.mdf')
ALTER DATABASE MSDB modify file(NAME='MSDBLog', FILENAME='newlocation\data\msdblog.ldf')

 

Stop the sql server

Move the actual datafiles to the new locations, they won´t move for themselves.

Say your prayers and start the Sql server

 

--- Uhhhh, SQL Server started, and all databases are online…good for you. now comes the funny part. Move the master

-- Move the master, by changing the master location on configuration manager, if in cluster do that on both nodes

look for service properties, advanced tab, startup parameters property

-dnewlocation\Data\master.mdf;-enewlocation\log\ERRORLOG;-lnewlocation\Data\mastlog.ldf

the –d parameter is the location of master.mdf file

the –l parameter is the location of master log file

the –e parameter is the location of the errorlog (very very important)

Newlocation is the PATH where the files will be located after the moving, please don´t write newlocation it won´t work… you got to write the full path where the file will be, and for god sake, make sure that the path exists and is spelled correctly

Remember that all parameters are on the same line and the parameter and the value are all toghether (no blanks). if you write it wrong sql server won´t come online. Hopefully you can change it after

If you are working in clustered environment, you got to change the startup parameter on all nodes where the instance belongs to.

Now is time to change the resource database of location, only applicable on SQL Server 2005. On SQL 2008 the system resrouce database is located on sqlroot\binn and you cannot change it.

What should i do them?? Nothing, leave it on the installation drive…

1. start-up the instance in master recovery mode,  on command prompt fun the following

net start MSSQLSERVER /f /T3608 

or

net start MSSQL$INSTANCENAME /f /T3608

And then connect using the sqlcmd.exe,  and run the following to move the system resource db

ALTER DATABASE mssqlsystemresource
    MODIFY FILE (NAME=data, FILENAME= 'V:\MSSQL2000\MSSQL$SQLC\Data\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
    MODIFY FILE (NAME=log, FILENAME= 'V:\MSSQL2000\MSSQL$SQLC\Data\mssqlsystemresource.ldf');
GO

-- Stop the instance, say your prayers  and start it again.

Sometimes, depending on the operation you gonna do you still have to change the path where sql server agent put its logs

USE msdb
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'V:\MSSQL2000\MSSQL$SQLC\LOG\SQLAGENT.OUT'   -- the new path obviously
GO

Now, you test if everything is running fine and you finished this delicate operation. Remember to failover the cluster group where sql server is running in order to make sure that everything is fine ON BOTH NODES. Collect some test evidence that you tested to make that everything was running ok, and you have to prove it.

IF YOU ARE NOT SURE OF THESE PROCEDURES. DON´T EXECUTE THEM IN PRODUCTION, create a test environment, simple sql server install on a VM or even in your own computer, and test the procedures.

Check the MSDN article with the procecedures above. http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx

This documentation is provided as is, and you know what it means. :)

Have fun!!!

Transparent Data Encryption on SQL 2008 (TDE)

Actually over the years sql server has improved a lot in terms of security. In the beginning, a long time ago, the industry didn´t have much concerns about security, even because this kind of implementation requires cost of development, implies in a more rigid development policies (to develop minimizing the surface area of attack), and the types of attack were not as severe as they are today (and today they imply in loosing money).

Over the last two versions of SQL Server (2005 and 2005) Microsoft made a big effor in making the product more secure. The reasons are simple. If they want SQL Server grow in more mission critical applications, the system must be protected from attacks, and more critical the application and the data, SQL Server must be more secure in terms of data access.

That´s why the term “secure by default” became a clichè in many Microsoft presentations regarding SQL Server.

One of the features on SQL 2008 related to security that at the same time is simple to implement and important for highly confidential database is Transparent Data Encryption. It covers two different aspects.

One, if you manage to detach a SQL Server database you might virtually attach it back in any other SQL Server instance. In this case someone with access to the system can steal the information and bypass security and auditing by simply creating a copy of the database. Which is a problem in terms of confidentiality.

Other is less common but the data inside the database could be exposed by some disk editing tool, which can check the database file internally, once the database file has no level of encryption it could be a risk, someone sniffing your disk searching for some information.

Transparent Data Encryption addressses these problems by creating all data inside the database encrypted. The data inside the database file is coded, and at the same time you cannot attach the database on other server, without having installed the key and certificatesd to decrypt the database

The process consists in creating a Master key, and a certificate. Later using this certificate to encrypt the database.  The master key, should have a password which must be strong in order to be more difficult to break by brute force, for example:

 

/*
Script enable Transparent Data Encryption on AdventureWorks database
*/
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Fum0V0rt3m0@$ql';
go
CREATE CERTIFICATE CertDecriptacao_advwrks WITH SUBJECT = 'Certificvado de decriptação para AdventureWorks'
go
USE AdventureWorks
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE CertDecriptacao_advwrks
GO
ALTER DATABASE AdventureWorks
SET ENCRYPTION ON
GO

A good way to work with it is to make some tests in an isolated environment first and them pass it to production in specific databases. It is pretty clear that the encryption process impacts on the overall performance by creating some cpu overhead.

So … have fun!!

4NF 5NF and 6NF

Follows a post i wrote on Brazilian Technet, later i will translate it to english.

 

4FN Quarta forma normal
“Uma tabela está na 4FN, se e somente se, estiver na 3FN e não existirem dependências multivaloradas”
Pode-se ver como a transformação de algo assim
Supondo que cada paciente pode ter mais de um plano de saúde, e pode fazer vários exames
Paciente   Plano      Exame
Carlos      SuaVida       Hemograma
Carlos      SL               Hemograma
Carlos      SuaVida       Urina
Carlos      SL               Hemograma

A idéia da 4FN seria dividir a informação em duas tabelas distintas
Uma com a Pedido do exame
e outra com a infromação de quais os planos q o paciente suporta, isso evitaria a redundência dessa informação em cada solicitação.
Paciente    Exame
Carlos       Hemograma
Carlos       Urina
Paciente PLano
Carlos    SuaVIda
Carlos    SL

A quinta forma é chamada de Forma de Projeção e União (Project/Join Normal Form)
Considere o exemplo
Vendedor   Marca       Tipo de Produto
Carlos        MArba      Aspirador de pó
Carlos        MArba      Torradeira
João          Tabajara   Geladeira
João          Tabajara   Torradeira
Arthur        Capivara   Aspirador de Pó
Arthur        Capivara   Tanquinho
Arthur        Capivara   Geladeira

Agora, suponha a seguinte regra: um vendedor tem um determinado conjunto de marcas e certo tipo de produtos na sua carteira de vendas.
Então poderiamos ter a tabela dividida da seguinte forma
Vendedor   Tipo de Produto
Carlos        Aspirador de pó
Carlos        Torradeira
João           Geladeira
João           Aspirador de Pó
Arthur        Tanquinho
Arthur        Geladeira

Numa outra tabela as marcas q o vendedor vende

Vendedor    Marca
Carlos        Marba
Joao           Tabajara
Arthur         Capivara

E logicamente numa outra tabela a lista de Produtos e a qual marca ele pertence

Produto               MArca
Aspirador de Pó   Marba
Aspirador de Pó   Tabajara
Torradeira           Marba
Torradeira           Tabajara
Geladeira            Tabajara
Geladeira            Capivara
Tanquinho           Capivara

A 6FN ela diz que os bancos de dados são uma extensão da álgebra relacional, existe um livro de Crhistopher J. Date e outros sobre Bancos de Dados temporais. Esse estudo pelo que eu vi é bem teórico, tem alguma aplicações práticas em cenários de DataWarehouse, existe alguma literatura do Date, e de um outro autor chamado Zimanyi, Agregações Temporais.
Espero que ajude, em poucas linhas é complicado definir, recomendaria procurar alguma literatura especializada em modelagem de dados. Outra coisa, lembre das implicações práticas. A normalização excessiva provoca um aumento no número de tabelas uma vez que se cria conjuntos não redundantes de dados, porém mais conjuntos. Em alguns cenários como DataWarehouses, as regras de normalização são desrespeitadas, em função da modelagem de DW trabalhar com preceitos de modelagem dimensional, uma vez que a informação é entendida como um conjunto de fatos de negócio rodeados por dimensões desses fatos.

Android

Apart from the Sql server usual topics, i´d like to register that i´m really impressed with the development of android plattform. you can find application for every task you want on the market, and they have a live community writing apps and earning money with that. It´s amazing the number of phones being shipped with the android os inside.. and what makes me more marvelled is how easy the operating system is to deal with.

I would say that even for a larger device, with this interface and plattform, android can grow to bigger devices in a near future, like ebook readers (let´s say nook from B&N) through tablet devices.

Android can represent the achievement that linux could not offer to the end user. Instead of fighting against Microsoft like linux and trying to create something better, Android is making it´s own way, which is awesome.

Microsoft a few years ago, was on the top about operating system technology, but somehow lost the track. I´m not an IPhone user neither an apple fan boy, but i have to agree that usability of the Iphone made everyone think about moving applications and making it more usable on the phone, and even more.. making its own phones with a better interface. Android could achieve that by creating an operating system open to multiple hardware platforms and using open standards all over it.

Google´s next step is Chrome OS, which can´t cause a huge impact on Windows empire… but again the innovation is on their side.More and more, the applications will become web based… and being such way, doesn´t matter if you are running Windows, Linux, Android, Leopard, Snow Leopard, Mountain Lion or anything… what do matter is what services can i bring to my browser, because every application would be a webpage.

More and more the strategies of those big companies would involve services, like google … like OVI from Nokia, like buying content with Itunes and so many others.

Big computing clouds will surround us, with content stored somewhere, published by someone, hosted by some machine in some datacenter around the world… but still it doesn´t matter, what matter is the content you receive, the physical location is not relevant because all the intelligence is stored somewhere, and your browser has just to render it before your eyes.

What we do, regarding SQL Server it is an important part of this future, because no matter what you do, the data must be stored somewhere, and must be accesible and secure. Must be quick to access, and must be reliable… the applications must work, webservices must be exposed. Services must be listening queues in order to process transactions, all this work is paramount to this future be viable.

When i look a small device carring a smart operating system on that i see i good idea, a lot of people working on it and a huge infrastructured to make it work.

 

Thanks

 

 

 

Congrats Android team.

About SQL Server Performance Parameters

Logical Disk: Disk Transfers/sec. This counter provides the overall throughput on the specific disk. Use this counter to monitor growth trends and forecast appropriately.

Logical Disk: Disk Read Bytes/sec & Disk Write Bytes/sec. This counter provides a measure of the total bandwidth for a particular disk.

Logical Disk: Average Disk sec/Read (Read Latency). This counter indicates the time it takes the disk to retrieve data. On well-tuned I/O subsystems, ideal values are 1-5 ms for logs (ideally 1 ms on a cached array), and 4-20 ms for data (ideally below 10 ms). Higher latencies can occur in peak times, but if high values are occurring regularly, investigate the cause.

Logical Disk: Average Disk sec/Write (Write Latency). This counter indicates the time it takes the disk to write the data. On well-tuned I/O subsystems, ideal values would be 1-5 ms for log (ideally 1 ms on a cached array), and 4-20 ms for data (ideally below 10 ms). Higher latencies can occur in peak times, but if high values are systematically occurring, investigate the cause.

Logical Disk: Average Disk Byte/Read. This counter indicates the size of I/Os being read. This value may affect disk latency and larger I/Os may result in slightly higher latency. When used to monitor SQL Server, this tells you the average size of the I/Os SQL Server is issuing.

Logical Disk: Average Disk Byte/Write. This counter indicates the size of I/Os being written. This value may affect disk latency and larger I/Os may result in slightly higher latency. When used to monitor SQL Server, this will tell you the average size of the I/Os SQL Server is issuing.

Physical Disk: % Disk Time: DataDrive. Monitor this counter to ensure that it remains below two times the number of disks.

Logical Disk: Current Disk Queue Length. For this counter, lower values are better. Values above 20 may indicate a bottleneck in the request waiting to be served by the disk, and should be investigated. Bottlenecks can create a backlog that may spread beyond the current server accessing the disk and result in long wait times for end users. Possible solutions to a bottleneck may be to add more disks to the RAID array, replace with faster disks, or move some of the data to other disks.

Logical Disk: Average Disk Queue Length. This counter indicates the average number of outstanding I/O requests. The general rule is that you should be at two or fewer outstanding I/O requests per spindle, but this may be difficult to measure due to storage virtualization, differences in RAID levels between configurations. Look for higher than average disk queue lengths in combination with higher than average disk latencies. This combination could indicate that the storage array cache is being over utilized or that spindle sharing with other applications is affecting performance.

Logical Disk: Average Disk Reads/Sec and Logical Disk: Average Disk Write/Sec.  These counters indicate the rate of read and write operations on the disk. Monitor these counters to ensure that they remain below 85 percent of the disk capacity. Disk access time increases exponentially if reads or writes are more than 85 percent of disk capacity. To determine the specific I/O capacity for your hardware, refer to the vendor documentation, or use the SQLIO disk subsystem benchmark tool to calculate it. For more information, see SQLIO Disk Subsystem Benchmark Tool (http://go.microsoft.com/fwlink/?LinkID=105586&clcid=0x409).

When you are using RAID configurations with the Logical Disk: Average Disk Reads/Sec or Logical Disk: Average Disk Write/Sec counters, use the formulas listed in the following table to determine the rate of I/Os on the disk.

RAID level	Formula
RAID 0	I/Os per disk = (reads + writes) / number of disks
RAID 1	I/Os per disk = [reads + (2 * writes)] / 2
RAID 5	I/Os per disk = [reads + (4 * writes)] / number of disks
RAID 10	I/Os per disk = [reads + (2 * writes)] / number of disks

For example, if you have a RAID 1 system with two physical disks, and your counters are at the values shown in the following table:

Counter	Value
Average Disk Reads/Sec	80
Average Disk Write/Sec	70
Average Disk Queue Length	5

The I/O value per disk can be calculated as follows:

(80 + (2 * 70))/2 = 110

The disk queue length can be calculated as follows:

5/2 = 2.5

In this situation, you have a borderline I/O bottleneck.

Monitor disk latency and analyze trends. The amount of I/O and latency specific to SQL Server data files can be found by using the sys.dm_io_virtual_file_stats dynamic management view in SQL Server 2005. For more information, see sys.dm_io_virtual_file_stats (http://go.microsoft.com/fwlink/?LinkID=105587&clcid=0x409).

 

 

Credits for the PSS

Grouping Sets

This is a feature on SQL 2008, it is meant to allow multiple groupings within the same statement.

It could make this

 

SELECT customer, NULL as year, SUM(sales) FROM T GROUP BY customer

UNION ALL

SELECT NULL as customer, year, SUM(sales) FROM T GROUP BY year

 

into this

SELECT customer, year, SUM(sales)
FROM T
GROUP BY GROUPING SETS ((customer), (year))

 

(samples from MSDN website)

Sparse Columns and Sparse Property

This could be interesting to save space in case we have a lot of records that could be filled or null.

 

Take a look at the books online excerpt

Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

Sparse columns can be used with column sets and filtered indexes:

• Column sets
  INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. However, you can also view and work with all the sparse columns of a table that are combined into a single XML column. This column is called a column set. For more information about column sets, see Using Column Sets.
• Filtered indexes
  Because sparse columns have many null-valued rows, they are especially appropriate for filtered indexes. A filtered index on a sparse column can index only the rows that have populated values. This creates a smaller and more efficient index. For more information, see Filtered Index Design Guidelines.

Sparse columns and filtered indexes enable applications, such as Windows SharePoint Services, to efficiently store and access a large number of user-defined properties by using SQL Server 2008.

if you have the books online installed, take a look at the full article.

have fun

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/ea7ddb87-f50b-46b6-9f5a-acab222a2ede.htm

 

 

USE AdventureWorks
GO

CREATE TABLE DocumentStore
(DocID int PRIMARY KEY,
Title varchar(200) NOT NULL,
ProductionSpecification varchar(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup varchar(20) SPARSE NULL ) ;
GO

INSERT DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27)
GO

INSERT DocumentStore(DocID, Title, MarketingSurveyGroup)
VALUES (2, 'Survey 2142', 'Men 25 - 35')
GO