Getting Started with Diskspd

SQL Server

http://www.brentozar.com/archive/2015/09/getting-started-with-diskspd

 Diskspeed, or diskspd.exe, is Microsoft’s replacement for SQLIO. While I’m not going to replace our SQLIO tutorial, you can use the information in here to replicate the same type of tests on your systems to get the information you need. During the Dell DBA Days, Doug and I used diskspd as one of our techniques for getting a baseline of raw performance. We wanted to get an idea of how fast the servers and storage before running SQL Server specific tests.

HOW DO I GET DISKSPD?

You can download diskspd directly from Microsoft – Diskspd, a Robust Storage Testing Tool, Now Publically Available. That page has a download link as well as a sample command.

The upside is that diskspd is a fully self-contained download. You don’t need Java, .NET, or anything else installed to run it. Apart from Windows – you’ll still need Windows.

HOW DO I USE DISKSPD?

With the command line, of course!

In all seriousness, although diskspd is the engine behind Crystal Disk Mark, it stands on its own as a separate tool. Download the executable and unzip it to an appropriate folder. There are going to be three sub-folders:

• amd64fre – this is what you need if you have a 64-bit SQL Server
• armfre
• x86fre

I took the diskspd.exe file from the appropriate folder and dumped it in C:\diskspd so I could easily re-run the command. Let’s fire up a command prompt and try it out.

Here’s a sample that we ran:

SQLIO Tutorial: How to Test Disk Performance

SQL Server

http://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/

Storage is a black box, right? The SAN admins ask how much storage space you need, you tell them, and then they give it to you. They don’t tell you how fast (or slow) it is, they don’t know whether it meets your needs, and they probably don’t know where the bottlenecks are.

It’s time to find out how fast your storage really is using SQLIO – perhaps the worst-named tool in history. SQLIO has absolutely nothing to do with SQL Server – it’s just a tool to test IO. Whether you’re an Exchange guy, a SQL DBA, or a file & print fella, SQLIO will help you push your storage to the limit to see when it breaks.

STEP 1: DOWNLOAD SQLIO FROM MICROSOFT FOR FREE

Download SQLIO from Microsoft and install it on the Windows server you’d like to test.  It’s extremely lightweight – it takes less than 1MB on disk – and works on any version of Windows.  The support page doesn’t say Windows 2008, but it works fine.

Install it in c:\Program Files no matter what drive you want to test.  I prefer installing it in C because I delete and recreate my non-system partitions repeatedly while I’m testing them.  That way, I can find out if various parameters are faster or slower without reinstalling the OS.

After installing SQLIO, you won’t see anything in the Start menu because it doesn’t have a graphical user interface.  Deep calming breaths – this is easier than it looks.  Fire open Windows Explorer and go into the directory where you installed SQLIO, like c:\Program Files(x86)\SQLIO.

STEP 2: SET UP SQLIO’S CONFIGURATION FILES

If you just start running SQLIO, it uses a laughably small 8MB test file by default.  The problem with such a small test file is that it’s probably smaller than the cache involved in your various storage components (drives, storage processor, etc.)  I prefer using a 20GB testing file for starters.  Your SAN might have a larger cache, but keep in mind that it divides that cache across all of the servers connected to the SAN.

Configurando Log Shipping

SQL Server

https://msdn.microsoft.com/es-es/sqlserver/hh291511

Introducción

El hecho de que Log Shipping sea una de las tecnologías que antes vinieron de la mano con las primeras versiones de SQL Server, no quita que sea siendo muy válido en una gran cantidad de soluciones a problemas reales de la actualidad. Pero log Shipping tiene un requerimiento que lo limita de entrada a funcionar en un entorno gestionado bajo un único Active Directory (o al menos entre varios AD, pero con confiabilidad). En este artículo vamos a ver cómo podemos personalizar un entorno en el que sin disponer de confiabilidad entre varios AD, podamos hacer funcionar Log Shipping.

Log Shippingconsiste en automatizar el proceso de restauración de una copia de seguridad del registro de transacciones en otra base de datos en otra máquina.

Ejemplos en los que Log Shipping encaja como tecnología a implementar serían los siguientes:

• Centralización de todas las BBDD de las "instancias sql satélite" de nuestra empresa, bajo un único servidor, posibilitando que se realice una explotación posterior de los datos mediante herramientas de inteligencia de negocio.
• Disponibilidad redundante de la información físicamente en diferentes ubicaciones
• Necesitamos un entorno con múltiples nodos secundarios de solo lectura
• Necesitamos un sistema de configuración sencilla y mínimo coste de mantenimiento.
• Disponemos de un entorno de conectividad intermitente

Algunas de las características más interesantes de Log Shipping se podrían resumir en las siguientes:

• Permite que el envío de información sea programado fácilmente a intervalos definidos y regulares, de forma que no haga falta una conexión abierta constante, minimizando el riesgo de problemas ante cortes de conexión.
• • Si ocurre algún error en el envío (por ejemplo no hay conexión) se seguirá intentando en los siguientes envíos programados
• Permite la monitorización automática del estado de la configuración, minimizando la labor de los administradores de base de datos.
• Permite múltiples bases de datos secundarias
• Permite utilizar cualquier BBDD secundaria como respaldo de forma que en caso de caída del servidor principal, se pueda levantar como servidor principal
• Se permite la configuración automática a través de despliegues de scripts T-SQL minimizando errores de intervención manual.

En definitiva, podemos apoyarnos en log shipping, para realizar arquitecturas de comunicaciones que faciliten la consolidación de información, siguiendo esquemas como estos:

Resumen de funcionamiento de Log Shipping

A grandes rasgos, los pasos que realiza Log Shipping de forma automática son los siguientes:

Export all SSIS packages from msdb using Powershell

SQL Server

http://sqlblog.com/blogs/jamie_thomson/archive/2011/02/02/export-all-ssis-packages-from-msdb-using-powershell.aspx

Have you ever wanted to dump all the SSIS packages stored in msdb out to files? Of course you have, who wouldn’t? Right? Well, at least one person does because this was the subject of a thread (save all ssis packages to file) on the SSIS forum earlier today.

Some of you may have already figured out a way of doing this but for those that haven’t here is a nifty little script that will do it for you and it uses our favourite jack-of-all tools … Powershell!!

Imagine I have the following package folder structure on my Integration Services server (i.e. in [msdb]):

There are two packages in there called “20110111 Chaining Expression components” & “Package”, I want to export those two packages into a folder structure that mirrors that in [msdb]. Here is the Powershell script that will do that:

Param($SQLInstance = "localhost")

#####Add all the SQL goodies (including Invoke-Sqlcmd)#####
add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue
add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue
cls 

$Packages =  Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query "WITH cte AS (
 SELECT    cast(foldername as varchar(max)) as folderpath, folderid
 FROM    msdb..sysssispackagefolders
 WHERE    parentfolderid = '00000000-0000-0000-0000-000000000000'
 UNION    ALL
 SELECT    cast(c.folderpath + '\' + f.foldername  as varchar(max)), f.folderid
 FROM    msdb..sysssispackagefolders f
 INNER    JOIN cte c        ON    c.folderid = f.parentfolderid
 )
 SELECT    c.folderpath,p.name,CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg
 FROM    cte c
 INNER    JOIN msdb..sysssispackages p    ON    c.folderid = p.folderid
 WHERE    c.folderpath NOT LIKE 'Data Collector%'"

Foreach ($pkg in $Packages)
{
    $pkgName = $Pkg.name
    $folderPath = $Pkg.folderpath
    $fullfolderPath = "c:\temp\$folderPath\"
    if(!(test-path -path $fullfolderPath))
    {
        mkdir $fullfolderPath | Out-Null
    }
    $pkg.pkg | Out-File -Force -encoding ascii -FilePath "$fullfolderPath\$pkgName.dtsx"
}

To run it simply change the “localhost” parameter of the server you want to connect to either by editing the script or passing it in when the script is executed. It will create the folder structure in C:\Temp (which you can also easily change if you so wish – just edit the script accordingly). Here’s the folder structure that it created for me:
 

Notice how it is a mirror of the folder structure in [msdb].
Hope this is useful!

Moving SSIS Packages to New Server

SQL Server

http://dba.stackexchange.com/questions/68481/moving-ssis-packages-to-new-server

Moving SSIS Packages to New Server

I have a number of SSIS packages in a number of folders on MSDB SSIS on SQL Server 2008R2. We are migrating to a new server host but remaining on SQL Server 2008R2.

How can I copy/migrate these SSIS packages to the new server? Is Dtutil the best option?

Yes, DTutil is still going to be the best option. I have a post, SSIS Package Extract from MSDB on how to use dtutil + the data in the packages table to generate the call(s) to export to disk. Instead of the file destination, you would substitute sourceserver. If you've used folders to organize your packages, then you'll need to create them beforehand.

Script posted here for posterity. This applies for 2008+ packages or 2012/2014 package that have used the package deployment model. With 2005, sysssispackagefolder would be replaced with sysdtspackages90 and sysssispackagefolders with sysdtspackagefolders90

;
WITH FOLDERS AS
(
    -- Capture root node
    SELECT
        cast(PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
    WHERE
        PF.parentfolderid IS NULL

    -- build recursive hierarchy
    UNION ALL
    SELECT
        cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
        INNER JOIN
            FOLDERS F
            ON F.folderid = PF.parentfolderid
)
,   PACKAGES AS
(
    -- pull information about stored SSIS packages
    SELECT
        P.name AS PackageName
    ,   P.id AS PackageId
    ,   P.description as PackageDescription
    ,   P.folderid
    ,   P.packageFormat
    ,   P.packageType
    ,   P.vermajor
    ,   P.verminor
    ,   P.verbuild
    ,   suser_sname(P.ownersid) AS ownername
    FROM
        msdb.dbo.sysssispackages P
)
SELECT 
    -- assumes default instance and localhost
    -- use serverproperty('servername') and serverproperty('instancename') 
    -- if you need to really make this generic
    -- File extraction command line
    'dtutil /sourceserver localhost /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /copy file;".\' + P.PackageName +'.dtsx"' AS cmd
    -- Move Package command line
    -- 'dtutil /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /SOURCEUSER srcUserName /SOURCEPASSWORD $Hj45jhd@X /DestServer <servername> /MOVE SQL;"'+ F.FolderPath + '\' + P.PackageName + '" /DESTUSER destUserName /DESTPASSWORD !38dsFH@v' AS cmd
FROM 
    FOLDERS F
    INNER JOIN
        PACKAGES P
        ON P.folderid = F.folderid
 WHERE
     F.FolderPath <> '\Data Collector'