I’m a speaker at PASS SUMMIT 2017

PASS_17_Going_600x315

Hello Friends,

I’m glad to announce that I’m a speaker at PASS Summit 2017, the largest SQL Server event on the planet (maybe in the universe 🙂 ).
In my session I will talk about high availability, using Azure as DR site.
I have been participating in the PASS Summit since 2012, and this will be the first time that I will participate as a speaker :).
It will be a short session “Lightning Talks” but I’ll try hard to deliver a good content.
For those who don’t know, the summit PASS is an annually event in the USA (usually in Seattle). You can register your self at PASS werbsite.
For those who going to Seattle, I’ll see you there. for those who will not be able to go this year, you can have part of the event at PASS website, they will transmit some information and interviews at PASStv during the event.

Regards,
Marecelo Fernandes

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Olá Amigos,
Estou feliz em anunciar que serei um palestrante no PASS Summit 2017, o maior evento de SQL Server do planeta (talvez do universo :))
Em minha sessão vou falar sobre alta disponibilidade, usando Azure como site de DR.
Tenho participado do PASS Summit desde 2012, e pela primeira vez participarei como palestrante :).
Será uma paletra curta “lightning talk” mas estou me esforçando para entregar um bom conteúdo.
Para quem não conhece, o PASS summit é realizado anualmente nos EUA (geralmente em Seattle).
Para quem vai para o Seattle, te vejo por lá. para quem não conseguirá ir este ano, no site do pass (PASStv) será transmitido algumas informações e entrevistas ao vivo durante o evento.
Obrigado,
Marcelo Fernandes

Anúncios

Thinking out of the box – SQL Server FCI using the File Share (SMB 3.0) as Storage option [Part 1]

Types of Storage for SQL Server

Before we begin with the “SMB 3” Let us first understand some types of storages that we can use in SQL Server and the difference between them.

There are several types of storage that can be used with SQL Server:

  • Internal disks – SSDs or traditional HDDs
  • Storage card PCI-E
  • Direct-attached storage (DAS)
  • Storage area networks (SAN)
  • Server Message Block (SMB) 3.0 file shares
    • Available in Windows Server 2012 and 2012 R2
    • SQL Server supports the use of the file shares from the 2012 version (stand-alone or FCI)

As you can see Microsoft allows us to use several types of storage for the installation of our SQL Server and storage our databases.

There are also some technologies that allow us to use Failover Clustering without the need for shared storage:

  • iSCSI Target Server
  • SMB FileShare (using SMB 3.0 or 3.02)
  • Cluster Shared Volume (CSV)

In this article we will focus on SMB which we can use in a SQL Server stand-alone or cluster (FCI)

 

Storage HDD vs SSD

I/O bottlenecks can occur in our environment so it’s imperative to understand what type of disk we use and the performance that is offered by these, so let’s compare the performance between the SSD disks and mechanical disks (HDD):

  • HDD – performance for sequential I/O acceptable
    • 100-200 MB/sec. per disc
  • HDD – low performance for random I/O
    • 100-200 IOPS per disk
  • SSD – good performance for sequential I/O
    • SAS/SATA 6Gbps can reach up to 550 MB/sec. per disc
    • SAS/SATA 3Gbps can reach up to 275MB/sec. per disc
    • PCI-E card can reach up to 6.5 GB/sec.
  • SSD – great for random I/O
    • SAS/SATA 6Gbps can reach up to 100.000 IOPS
    • PCI-E storage cards can reach up to 1.3 million IOPS

As you can see traditional HDDs do not have good performance in random I/O. SSD has outstanding performance for random I/O, and PCI-E is best for this type of activity.

Note that the slower disk is cheaper and the disk with better performance more expensive, so for some scenarios of I/O bottleneck the simple exchange of the spinning disks for a SSD or PCI-E cards isn’t always feasible due to cost.

Comparing I/O performance

To compare the performance of the technologies mentioned above we ran a simple I/O test on a shared storage in a data center which is a very common scenario in today’s environments. Unfortunately, not every company has a capital to dedicate a storage device for a particular service but that’s OK. I also ran the same test on a SSD disk and on a dedicated high-end storage array for SQL.

As we can see we have 3 types of solutions where the cheapest is the storage shared with SAS disks and more expensive (and I mean really expensive J) is the high-end storage which can run you millions of dollars.

Note: Testing conducted with the DiskSpd tool with the following configuration:

diskspd-b8K-d180-o16-t4-h-r-w25-L-Z1G-D:\iotest.dat c10G

smb1

Reading the above table, we see that the best solution is the high-end storage that costs too much but if you’re looking closely at the SSD disk you can see that performance similar to the high-end storage but the SSD disk costed me only $150 dollars which is not so far away in price for shared storages offered by most of datacenters.

Looking at only the total I/O column we have:

 

smb2

In this case the cost-benefit of the SSD disks is more advantageous to the shared disk.

Choosing the Storage based on Workload

The following are a list of tips that will help you choose the best hard drive for your SQL Server environment based on workloads:

  • SSD offers better performance for random I/O
    • It also offers better sequential performance compared to HDD
    • SSD is more expensive than the HDD (per GB)
    • The price of SSD is becoming more affordable
  • HDD offers a reasonable performance for sequential I/O
    • HDD have a bad performance for random I/O (Which can be “masked” through a controller’s cache)
    • Flash-based caching can provide better performance for the HDD
  • SSD is the best choice if “the cost” is not a problem
    • Recommended for environments with high overhead of random I/O
    • Recommended for environments that have I/O bottlenecks

 

3.0 SMB File Shares

We have seen in previous threads that SQL supports basically two types of disks: the HDD and SSD, but how about installing SQL Server into a shared folder on a File Server?

In the past a lot of people would be scared by this idea, because historically the statistics of the SMB (Server Message Block) aren’t very good, we can highlight some negative points that many infrastructure administrators cited about the SMB:

  • File shares are slow
  • The connection with a file share can fail
  • SMB consumes too much CPU

In the new SMB 3 these negatives points were treated or disposed of, below are some key points about SMB 3 that will be detailed throughout this chapter:

  • Supports I/O using multiple concurrent network cards
  • Fault tolerant of network interfaces
  • Integrated with Windows Failover Clustering
  • Windows Server 2012 supports “Direct SMB”, which allows the use of network cards supported by the “Remote Direct Memory Access (RDMA)”
    • RDMA requires SMB Multichannel
    • RDMA offers high speed with low latency and low CPU consumption

FCI SQL Server using the File Share (SMB 3.0) as a storage option

Windows Server 2012 was made available the new version of the Server Message Block SMB 3.0 and upgraded to 3.2 in Windows 2012 R2.
Only SQL Server 2012 or higher supports SMB as a storage solution SQL Server Standalone or FCIs.

smb3

Figure 1.1: Example of using File Server environment as a Storage option

 

In Figure 1.1 we illustrate a SQL Server clustered environment using a File Server (SMB) as storage solution for SQL.

You can see that we are using the SOFs (Scale-Out File Server) which is a new file storage solution and a SQL Server cluster with 3 nodes

This is a solution for enterprise-level storage because it is scalable, reliable and always available.

It provides easy provisioning and management because we can use the familiar tools of Microsoft SCCM, PowerShell to manage the environment.

It also uses the latest network technologies (Convergent, Ethernet RDMA) which provides us greater flexibility,

The solution also reduces capital and operational costs because we have a storage that can be used by other services.

An SMB solution offers us some features we don’t have in conventional solutions:

  • SMB Transparent Failover – continuous availability if one node fails
  • SMB-Scale-out – Automatically balanced Active / Active file server clusters
  • Direct SMB (SMB over RDMA) – Low latency, high throughput, low CPU usage
  • Multichannel SMB – Increased network throughput and fault tolerance
  • SMB Encryption – Secure data transmission, without the costly PKI infrastructure
  • VSS for SMB File Shares – Backup and Restore using an existing VSS framework
  • SMB PowerShell, VMM Support – Manageability and support through the System Center and PowerShell

SMB Transparent Failover

One of the components that doesn’t have much protection in a traditional cluster are disks, if there is a failure in the storage or in the middle of communication between the storage and the server (Fiber, HBA, etc.) the SQL Server service that is clustered will fail! SMB 3 has a great feature that allows us to perform the application failover using SMB (in the case of our example SOFs) with zero downtime for SQL Server, there is a small and fast I/O delay during a failover but that’s it.

The transparent fail over can be planned or unplanned which means we increase the level of availability of our application as a result. This does not mean that if by chance a SQL Server fail over occurs this process will be transparent to the application, SQL Server has downtime, what has no down time is the file server that we are using as an alternative storage. We eliminate the protection issue between the storage communication and the SQL Server.

smb4

Figure 1.2: simulation of Transparent Failover in SMB 3

In Figure 1.2 we have an illustration of the transparent fail over process, it shows a SQL Server cluster that has been installed in the file server directory (\\fileserver\SQLtest) and in this context we note that the service was active on the first node and was moved (failed over) to another node and during this the SQL Server service is not affected, it remains online. This type of protection is only available with the SMB 3.

Direct SMB (SMB over RDMA)

Before we begin with the SMB over the Direct Memory Access (RDMA) let us first understand what the RDMA.

RDMA is a protocol that allows access to the memory of a remote computer. That is, if you have a network card with RDMA support the SMB client has direct access to the memory of the SMB server making the file transfer extremely fast and with very low CPU consumption.

The benefits of RDMA are:

  • Low latency
  • High yield
  • Zero copy capability
  • OS/by-pass

The following technologies leverage RDMA Hardware

  • InfiniBand
  • iWARP RDMA over TCP/IP:
  • ROCE: RDMA over Ethernet convergence

smb5

Figure 1.3: illustration of the operation of the RDMA in SMB 3

 

SMB Scale out

With Scale-out File Server (SOFs) you can share the same folder on multiple nodes in a cluster. In the example in Figure 1.2 we have a two-node file server cluster using the scale-out SMB protocol, a computer running Windows Server 2012 R2 or Windows Server 2012 can access file server on either of two nodes. This is possible because of the new Windows Failover Cluster features along with the File Server SMB 3.0 protocol. With this in place you can now say that SOFs will always be online and in case of increased demand we can simply add new servers in the cluster and all this in a production environment with the operations totally transparent to the applications

The main benefits provided by SOFs Server include:

  • File Server Active-Active All cluster nodes may accept and serve the SMB client requests with transparent fail over between nodes.
  • Increased bandwidth The maximum bandwidth of a share corresponds to the sum of the width of all nodes of cluster file server. In previous versions of Windows Server, the total bandwidth was limited to the bandwidth of a single cluster node. You can increase the total bandwidth by adding new nodes.
  • CHKDSK with zero downtime There was a significant improvement made to CHKDSK in Windows Server 2012 that drastically reduces the amount of time that a system is off-line for repairs the CSVs (Clustered Shared Volumes) eliminating the offline phase.
  • Clustered Shared Volumes Cache CSVs in Windows Server 2012 introduce support for a read cache significantly improving performance.
  • Simpler management With SOFs you can add new CSVs and create multiple shares. You no longer need to create multiple Cluster File Servers each with separate cluster disks.
  • Automatic Rebalancing of SOFs’s clients In Windows Server 2012 R2, automatic rebalancing improves the scalability and manageability of SOFs. SMB client connections are controlled by shares rather than by server so clients are redirected to the node with better access to the volume of that share thereby significantly improving performance and reducing traffic between servers.

 

SMB Multichannel

SMB Multichannel is another great feature of SMB 3.0 since it provides us increased network throughput and fault tolerance.

Let’s imagine two scenarios where we have a 4-core environment and a 10 GB network card without Multichannel technology and another environment with Multichannel technology.

In the first scenario, let’s imagine that we have a session with several I/Os we would see a high use of only one core while we have 3 without use. This is because in the old version of SMB we did not have Multichannel support and SMB only creates 1 TCP connection.

If I run the same test in the second scenario with SMB 3 supporting Multichannel we will see the distribution of the use of cores, this happens because SMB 3 detects that the network card has the Receive Side Scaling (RSS) feature and creates multiple connections TCP allocating the load between the CPUs.

You do not need to make changes or settings in your environment to use Multichannel, SMB 3 detects RSS and automatically creates multiple connections.

Figure 1.4 illustrates this behavior of SMB Multichannel.

 

smb6

Figure 1.4: comparative environmental behavior with and without Multichannel
(image kindly provided by Jose Barreto [http://blogs.technet.com/b/josebda/])

 

Let’s imagine 2 other scenarios, 1 without SMB 3 with two file servers the first with two network cards with RSS support and another with two network cards without RSS. The other scenario is exactly the same as the first one but with SMB 3 Multichannel support, image 1.5 illustrates this.

In the scenario without SMB 3 Multichannel support, although we have two network cards in both file server clusters, we do not have the automatic fail over that is present only in SMB 3, let’s imagine that we have a session with several I/Os in this if we use only 1 network card (both in the cluster with RSS support and the other without RSS support) and we would have the single-core high-power scenario due to the creation of only 1 TCP connection.

In the scenario with SMB 3 Multichannel support, we have automatic fail over and we can use all available bandwidth on the server as Multichannel will use the two network cards to increase bandwidth. In the cluster with the network cards without RSS support, but with the SBM 3, Multichannel will use the 2 network cards by opening only one connection in each of them.

 

Figure 1.5 illustrates this behavior of SMB Multichannel with multiple network adapters.

smb7

Figure 1.5: Comparing behavior of environments with and without Multichannel using multiple NICs (image kindly provided by Jose Barreto [http://blogs.technet.com/b/josebda/])

 

The key points of the Multichannel SMB are:

Full Throughput

  • Bandwidth aggregation with multiple NICs
  • Uses multiple cores of the Cpu when using Receive Side Scaling (RSS)

Automatic failover

  • SMB Multichannel implements fault detection, end-to-end
  • Enjoy teaming NICs if present (but not require)

Automatic configuration

  • SMB detects and uses multiple network paths

SMB performance Multichannel

Jose Barreto who is Principal Program Manager at Microsoft and helped in the development of SMB 3 did a great performance testing with the SMB3 with the following settings:

  • Windows Server 2012 using 4 10GbE NICs
  • Linear scaling of bandwidth:
    • NIC 1-1150 MB/sec
    • 2 NICs-2330 MB/sec
    • 3 NICs-3320 MB/sec
    • 4 NICs-4300 MB/sec.
  • Network cards with support for RSS (Receive Side Scaling)
  • Bandwidth for small I/O cause bottlenecks in CPU

The result obtained with the tests were spectacular, note the performance with the Multichannel

smb8

Figure 1.6: SMB Performance Multichannel (image kindly provided by Jose Barreto [http://blogs.technet.com/b/josebda/])

 

We note that for small I/Os we do not get much advantage by increasing the amount of network card, the higher I/O the gain is significant as we increase the number of network cards, reaching about 4500 Mb/s with 4 network cards for I/O size greater than 16384

Remember the test table comparing some types of storage that we discussed at the beginning of the this article?

smb9

Obviously the test I did with Diskspd was different from the test  that José Barreto ran, but it serves as a parameter to break some paradigms, note the MB/s column of storage vs. SMB 3.

I’m not telling you to leave the SAN and go to SMB 3.0 tomorrow as this would have a certain financial and bureaucratic impact, but it’s worth a test in your environment, isn’t it? And how about considering SMB3 for new projects?

SMB 3 is another alternative that we have of storage with the differential of performance, availability and scalability. So “think out of the box!

As shown, we have greater disk protection with Transparent Failover and we use the full bandwidth available on the servers.

PS: It’s very important to pay attention that the environment used here had an specif network card, the normal nics will works, but if you have a very transactional environment you should have a nic with RDMA and RSS support.

 

Regards,

Marcelo Fernandes

 

SQL Saturday SP #570

Olá Amigos,

No dia 08/10/2016 tive o prazer de participar do SQL Saturday #570 SP, tive o prazer de palestrar em todas as edições do SQLSP. E esta edição estava lotada…. Parabéns  Diego Nogare (Site | Twitter) e aos demais organizadores, realmente o evento estava sensacional.

Esta foi a turma de palestrante: (acredito que esta foi a edição com o maior número de palestrantes no Brasil)

img_8008

O tema de minha sessão nesta edição foi “Criando ambientes híbridos de alta disponibilidade (On Premises e Cloud)” e caso queira baixar a apresentação e as demos, fique a vontade, já os disponibilizei no site do SQLSaturday.

Abaixo algumas fotos do evento, caso queira ver as demais fotos e tenha paciência para o download, tem quase 900Mbs de fotos 🙂.

 

Te vejo no próximo SQL Saturday, que ja tem data e local, será o SQL Sat 573 em Brasília no dia 26/11/2016, te vejo lá.

SQLSATSP #424

IMG_6659Olá Amigos….

No sábado dia 26/Setembro ocorreu mais uma edição do SQLSat em São Paulo, tive o prazer de ministrar mais uma sessão para o SQLSat. Nesta edição dividi a sessão com o brother Alex Silva (Twitter ) e falamos de clusterlog. você pode efetuar o download do material apresentado no portal do SQLSaturday

IMG_6376No ppt consta a parte bonus da palestra com o authoritative restore do cluster database, nem todos conhecem o poder que este cara tem e como ele pode salvar o seu emprego 🙂

A casa estava cheia, e foi muito bom rever os amigos e fazer novos amigos, nesta edição tivemos 33 palestrantes com 8 MVPs, 7 PFEs e 1 MCM

realmente foi um evento fantástico, parabéns aos organizadores e a todos que diretamente ou indiretamente colaboraram para o sucesso do evento.

Segue o link para download das fotos do evento.

Bom, nos vemos no próximo SQLSat que ja tem data, será o SQLSatDF em Brasília no dia 21/Novembro.

TechEdBR #EuPalestrei \o/

Olá amigos….

IMG_0983Este final de semana (24 a 25 de Setembro) foi fantástico, tiver o prazer de dividir uma sessão com meu brother Nilton Pinheiro (Twitter | Site) no TechEd, foi minha primeira palestra para o TechEd, ter o seu nome na grade de palestrantes em um dos maiores eventos de tecnologias e ainda na sala “Spartan” são fatos que não ocorrem todos os dias 🙂

Nesta sessão do TechEd falamos de novas tecnologias para aumentar a disponibilidade de seu ambiente com o Windows Failover Cluster, falamos um poouco de iSCSI, SMB 3.0 e CSV, e a casa estava lotada….

CP26-SuW8AA9GyF.jpg large

Passamos alguns finais de semana preparando esta sessão e tivemos ótimos feedbacks, muito obrigado a todos que compareceram ao TechEd.

IMG_0989[1]Este evento foi bem especial pois foi a primeira vez que ministro em um evento onde um outro brother que top master SQL Server Jedi etc e tal também está na grande como palestrante. Fiquei muito feliz ter ter meu nome na grade de palestrante onde o Roberto Cavalcanti (Sr. Support Escalation Engeneer) também participou, sempre que faço uma conexão por Dallas tento visitar por algumas horas o Roberto lá em Las Colinas onde sempre sou muito bem recebido.

Muito obrigado a todos e até próximo TechEd

2015 Microsoft MVP Virtual Conference

vconf 

Olá amigos!

Gostaria de convidá-los para o grande evento que a Microsoft e os MVPs estão organizando, e que acontecerá nos dias 14 e 15 de maio, a partir das 12h00 (Horário de Brasilia)

Junte-se aos MVPs do Brasil, Estados Unidos e Latam que estarão compartilhando o seu conhecimento em sessões práticas e gratuitas, com cenários do mundo real e últimas novidades sobre Tecnologia Microsoft.

steveO MVP Virtual Conference trará 95 sessões com conteúdo para IT Pros, Developers e Consumer experts, planejadas para ajudá-lo a navegar no momento mobile-first Cloud first. E para abrir o evento, teremos o prazer de receber Steve Guggenheimer, Microsoft VP of Developer Platform.

Por que atender ao MVP V-Conf? A conferência está dividida em 5 trilhas simultâneas:  IT PRO (English), DEV (English), Consumer (English), Sessões em Português e em Espanhol, temos sessões para todas as audiências!

Inscreva-se e garanta a sua vaga!

A conferência será acompanhada pelos nossos canais sociais, e você pode acompanhar em tempo real, seguindo @mvpaward e usando a hashtag #MVPvConf.

Confira a agenda e programe-se:

grade

grade2

A minha sessão será no dia 15/05 as 05:00 PT (21:00 horário de Brasília) e será moderada pelo MVP Nilton Pinheiro (Twitter | Site)