Blog SQL Server de Maximiliano Accotto

Consultoría en SQL Server , servicios de DBA en CONDUIT Soluciones

Como comprimir todos los índices de una tabla

 

El siguiente script nos comprimirá (en este caso en modo page) todos los índices de una tabla.

USE MIDB

GO

 

ALTER

INDEX ALL ON

[TUTABLA]

REBUILD WITH (DATA_COMPRESSION=PAGE);

Como detectar problemas de Deadlock (interbloqueos) usando el SQL Server Profiler

SQL Server Data Compression para mejorar la performance

Desde la versión 2008 Enterprise de SQL Server se pueden comprimir tanto datos como Backups. La compresión de datos (Data Compression) es una técnica que no fue en principio pensada para ahorrar espacio sino que para mejorar performance. Esto básicamente se debe a que hoy día los servidores de bases de datos tienen mas potencia en CPU pero el sistema de disco sigue siendo lento, con lo cual si yo pudiera consumir menos recursos de disco las cosas serian mas eficientes a costa de un mayor consumo de CPU.

Dicho en otros términos no es lo mismo leer un índice clustered de 1GB que uno de 100Mb.

Por defecto las tablas no están comprimidas ya que antes de hacerlo uno debería hacer un análisis de la instalación, revisar si la compresión tiene alguna ganancia y luego aplicarla, en otras palabras es una tarea del Dba’s en su role de tuning.

En este post veremos un ejemplo de como se mejora la performance en una tabla utilizando esta técnica de Data Compression.

Paso 1: Construimos 3 tablas iguales a partir de la tabla SalesOrderDetail de nuestra base de datos Adventureworks

SELECT * INTO Nocomprimida

FROM SALES.SalesOrderDetail

 

SELECT * INTO ComprimidaRow

FROM SALES.SalesOrderDetail

 

SELECT * INTO ComprimidaPage

FROM SALES.SalesOrderDetail

 

Paso 2: Creamos para cada una de ellas su índices Clustered, en la primera sin compresión, en la segunda usando compresión de Row y en la ultima usando compresión de Page.

 

CREATE CLUSTERED INDEX INoCompression ON dbo.Nocomprimida

(SalesOrderID, SalesOrderDetailID);

 

CREATE CLUSTERED INDEX ICompressionRow ON dbo.ComprimidaRow

(SalesOrderID, SalesOrderDetailID)

WITH (DATA_COMPRESSION = ROW);

 

CREATE CLUSTERED INDEX ICompressionPage ON dbo.ComprimidaPage

(SalesOrderID, SalesOrderDetailID)

WITH (DATA_COMPRESSION = PAGE);

 

Paso 3: Analizamos los espacios consumidos por cada tabla

compress1_thumb2 

 

Como podemos observar las tablas comprimidas tienen un ahorro de espacio significativo.

Paso 4: Probaremos el comportamiento a nivel performance de distintas consultas, haremos SELECT UPDATE y DELETE.

Probamos un SELECT

SELECT SalesOrderID, SalesOrderDetailID, ProductID

FROM dbo.Nocomprimida

WHERE SalesOrderID BETWEEN 50000 AND 52000

 

SELECT SalesOrderID, SalesOrderDetailID, ProductID

FROM dbo.ComprimidaRow

WHERE SalesOrderID BETWEEN 50000 AND 52000

 

SELECT SalesOrderID, SalesOrderDetailID, ProductID

FROM dbo.ComprimidaPage

WHERE SalesOrderID BETWEEN 50000 AND 52000

 

Ahora observaremos los planes de ejecución de estas 3 consultas.

 

compress2_thumb1

 

Lo que se observa es que para el mismo tipo de operación (Clustered Index Seek) los costos van disminuyendo al usar compresión. Básicamente esto se da ya que el costo de lectura en tablas comprimidas es mas bajo.

Ahora observemos las estadísticas de disco para las 3 consultas.

 

SET STATISTICS IO ON

 

SELECT SalesOrderID, SalesOrderDetailID, ProductID

FROM dbo.Nocomprimida

WHERE SalesOrderID BETWEEN 50000 AND 52000

 

SELECT SalesOrderID, SalesOrderDetailID, ProductID

FROM dbo.ComprimidaRow

WHERE SalesOrderID BETWEEN 50000 AND 52000

 

SELECT SalesOrderID, SalesOrderDetailID, ProductID

FROM dbo.ComprimidaPage

WHERE SalesOrderID BETWEEN 50000 AND 52000

 

SET STATISTICS IO OFF

 

Table 'Nocomprimida'. Scan count 1, logical reads 174, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ComprimidaRow'. Scan count 1, logical reads 108, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ComprimidaPage'. Scan count 1, logical reads 73, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Se observa como se han disminuido las lecturas lógicas al usar la compresión.

Probamos un DELETE

Nuestra siguiente prueba es hacer un borrado masivo de las tres tablas y comparar sus planes de ejecución y estadísticas de disco como hicimos con el SELECT.

SET STATISTICS IO ON

 

DELETE FROM Nocomprimida

 

DELETE FROM ComprimidaRow

 

DELETE FROM ComprimidaPage

 

SET STATISTICS IO OFF

 

COMPRESS3_thumb1

 

Table 'Nocomprimida'. Scan count 1, logical reads 15750, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ComprimidaRow'. Scan count 1, logical reads 9232, physical reads 0, read-ahead reads 4, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ComprimidaPage'. Scan count 1, logical reads 5900, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Probamos el UPDATE

Haremos la misma prueba pero ahora actualizando todos los registros de las tablas.

SET STATISTICS IO ON

 

UPDATE Nocomprimida

SET UnitPrice = UnitPrice * 2

 

UPDATE ComprimidaRow

SET UnitPrice = UnitPrice * 2

 

UPDATE ComprimidaPage

SET UnitPrice = UnitPrice * 2

 

SET STATISTICS IO OFF

 

 

COMPRESS4_thumb3

Table 'Nocomprimida'. Scan count 1, logical reads 1513, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ComprimidaRow'. Scan count 1, logical reads 2099, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ComprimidaPage'. Scan count 1, logical reads 1301, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Conclusiones:

El Data Compression es una funcionalidad muy interesante, si bien no en todos los casos responde como lo vimos en este post es muy bueno evaluar las tablas mas criticas si con la compresión podemos o no mejorar performance.

Mi experiencia con esta funcionalidad es muy positiva, pero siempre recalco que lo mejor es hacer las pruebas correspondientes para determinar si es conveniente usarlo para alguna tabla, en muchos casos no se logra una buena compresión y los resultados son casi iguales, cuando hablamos de resultados estoy hablando del costo de las distintas operaciones.

Aquí les dejo algunos links adicionales y el script de toda esta demo.

SQL Server Compression Estimator

http://ssce.codeplex.com/

Data Compression: Strategy, Capacity Planning and Best Practices

http://msdn.microsoft.com/en-us/library/dd894051.aspx

Como exportar todos los JOBs usando el Management Studio

En varias ocasiones necesitamos exportar los JOBs (trabajos) del agente a otro servidor, por ejemplo para una migración. Esta operación se puede hacer de forma simple desde el SQL Server Management Studio como veremos a continuación.

Paso 1: Entre a la instancia origen con su SSMS.

Paso 2: Vaya al agent de SQL Server como se muestra en la siguiente imagen

jobs1

Paso 3: Seleccione la opción JOB

jobs2

Paso 4: Activamos el Object Detail, para ello podemos ir al menú view y luego a la opción : Object Explorer Details o bien pulsar F7.

jobs3

Paso 5: Desde el object Explorer Details seleccionar todos los JOBS a exportar

jobs4

Paso 6: Pulsar el botón alterno del mouse sobre el object explorer Details y seleccionar : Script Jobs as –> CREATE TO – File .

Seleccionamos la ubicación y el nombre del archivo a guardar y ya tendremos los jobs en modo Script, ahora lo único que nos hace falta es abrir el .SQL en el servidor destino y ejecutarlo.

Nota: No se olvide de pasar los operadores previamente, para ello puede usar este mismo procedimiento pero en lugar de ir al menú JOBS del Agent debe seleccionar Operators

Como determinar si una instancia soporta BackupCompression y como esta configurado

 

El Backup Conpression es una excelente funcionalidad que se agrego en SQL Server 2008 Enterprise Edition y sus versiones superiores.

En SQL 2008 solo esta habilitado para la edición Enterprise y ya en 2008 R2 y 2012 para la Standard también.

Con el siguiente script podemos saber si nuestra instancia soporta Backup Compression y de ser así como esta configurado.

Declare @BackupCompression sql_variant

 

SELECT @BackupCompression = ISNULL((SELECT value

                                    FROM sys.configurations

                                    WHERE name = 'backup compression default'),2)

 

IF @BackupCompression = 2

   Print 'No hay soporte en esta version de SQL Server para Backup Compression'

IF @BackupCompression = 0

   Print 'Backup Compression esta soportado pero en modo Disable por defecto'

IF @BackupCompression = 1

      Print 'Backup Compression esta soportado modo Enabled por defecto'

Control de backups usando Policy Management

El control del backup suele ser una tarea densa y de mucha importancia. Como DBA me gustaría saber a que bases les esta faltando backups, cuales no tienen un backup reciente, etc.!

Lo primero que debemos tener armado es una política de backup, no todas las bases pueden llegar a ser iguales.

Luego de tener nuestra política armada, viene el momento de hacerle un control, por ejemplo si agregan una nueva base de datos como saber que tiene backup?

Para hacer dicho control existen varias alternativas, desde el armado de script en SQL hasta el uso de herramientas de monitoreo como puede ser la suite System Center.

En este post les mostrare como utilizar Policy Management de SQL Server 2008 o superior para hacer dicho control.

¿Porque uso Policy Management ?, mis razones para este caso son:

  • No dependo de herramientas de terceros como DBA.
  • Es nativo de SQL Server a partir de la versión 2008
  • Su uso es mas intuitivo que script
  • Puedo definir una política en un server y ejecutarla en varias instancias para su control.

 

Pasos para crear la política de control de backup:

  • Lo primero que haremos es entrar a nuestro Management Studio (SSMS) con los permisos necesarios (con el role PolicyAdministratorRole en la MSDB es mas que

    suficiente si no es sysadmin)

  • Desplegamos la sección Management y luego Policy Management como se muestra en la siguiente imagen

 

policy1

  • El siguiente paso ahora es crear una condición, para ello vamos al sub ítem “Conditions” pulsamos el botón alterno del mouse y seleccionamos la opciones

   “New Conditions”

  • Le pondremos un nombre a la condición (por ejemplo: ControlBackup) y seleccionaremos la Faceta “Database” como se muestra en la siguiente imagen:

 

polici2

  • En expresiones buscaremos los atributos de backup que son:
    1. @LastBackupDate : Indica cual fue la ultima fecha y hora del backup full de una base de datos
    2. @LastDifferentialBackupDate: Indica cual fue la ultima fecha y hora del Backup Diferencial de una base de datos.
    3. @LastLogBackupDate: Indica cual fue la ultima fecha y hora del backup log de una base de datos

 

Dependiendo de mi política de backup (supongamos un backup full todos los domingos y diferenciales todos los días) crearemos el control.

Para ello seguiremos los siguientes pasos

  • Agregamos en la condición  el atributo @LastBackupDate y en el operador ponemos la opción “>”

 

polici3

  • Haremos luego clic con el mouse sobre … para poder escribir la expresión: en la misma escribiremos el siguiente código:

    DateAdd('Day', -8, GetDate())

 

Polici4

Esto hará un control de backup mayor a 8 días, si el backup  es inferior a esa fecha nos marcara un error.

  • Ahora agregaremos la propiedad @LastDifferentialBackupDate para controlar que los backup diferenciales estén ok, en la expresión le pondremos el siguiente código: DateAdd('HH', -25, GetDate())

 

polici5

Depende de como queramos controlar las cosas, podemos o bien crear políticas de control separadas para el backup full y el resto o bien todo junto. Yo por lo general los separo para tener un mejor control granular.

  • Luego de crear la condición lo que haremos es generar la política, para ello iremos a la sección “Policies” dentro de : “Policy Management” y con el botón alterno del mouse crearemos una nueva usando la opción “New Policy” completando los siguientes datos como aparecen en la figura:

 

polici6

Aquí podremos seleccionar si deseamos que la política sea ejecutada bajo demanda o bien armar un job de SQL con alguna frecuencia (por ejemplo todos los días a las 9AM).

Otra opción de selección es determinar si queremos aplicar la política a todas las bases de datos, en este ejemplo voy a excluir a las de sistema ya que tienen una política de backup distinta (deberé luego armarles una para ellas)

Para lograr esto seleccionaremos el botón “Every” y luego la opción  “New Condition” para completar la siguiente pantalla:

polici7

  • Bien ya con esto tendremos generada nuestra política en el SQL Server la cual podremos o bien ejecutar bajo demanda y/o por programación de un JOB

 

polici8

  • Para ejecutar la política bajo demanda es tan simple como hacer botón alterno del mouse sobre “PoliticaControlBackup” y luego la opción “Evaluate”

 

Conclusiones:

El uso de políticas para hacer controles sobre nuestras instancias de SQL Server es una muy buena alternativa! aquí hemos visto como podemos controlar los backups pero se pueden hacer muchas mas cosas usando las PBM

Error: In order to start tuning application needs to create some objects in MSDB database al usar el (Database Engine Tuning Advisor)

Si deseamos darle permisos a un login de SQL Server para que utilice esta herramienta no es necesario que sea Sysadmin de la instancia. Con solo ser Db_owner() de la base de datos que desea trabajar  es suficiente.

Ahora bien, al querer entrar con un login al DTA nos puede dar el siguiente mensaje de error:

In order to start tuning application needs to create some objects in MSDB database. You have to be a member of sysadmin fixed server role in order to initialize MSDB for tuning (System.Windows.Forms)

¿Que es lo que sucede?

El DTA si nunca se utilizo para esa instancia necesita como primer paso crear una serie de tablas en la MSDB las cuales si no están las intenta crear, como seguramente con el login que estamos usando no tiene permisos sobre la MSDB (y esto esta bien) deberíamos de alguna forma crear esas tablas.

Veamos primero antes de haber ejecutado alguna vez el DTA las tablas de la MSDB, para ello debemos entrar con un login que tenga permisos

dta1

Como podemos observar no hay tablas creadas dentro de MSDB mas allá de las del sistema.

Para resolver nuestro problema la forma las simple y sin darle mas permisos al login es que un Sysadmin o login con permisos en la MSDB ejecute por primera vez el DTA para que se creen estas tablas y luego así el resto de los logines no necesitan hacerlo.

Entonces lo que haremos es lo siguiente:

  • Entramos al SSMS con alguna cuenta de privilegios, podría ser un Sysadmin.
  • Creo una nueva consulta con New Query
  • Ahí escribo la siguiente query de ejemplo

 

use master

go

 

select *

from sys.tables 

  • Seleccionamos toda la query y luego hacemos botón alterno del mouse
  • Ahí seleccionamos: Analyze Query in Database Engine Tuning Advisor

 

Esto lo que hará es iniciar la herramienta pero con un usuario con permisos sobre la MSDB, al no ver las tablas creadas lo hará por única vez y ahora el resto de los usuarios podrán usarla de forma natural sin problemas.

Si observamos ahora la MSDB veremos las siguientes tablas

dta2

Como generar un script con solo los índices de una base de datos

 

Muchas veces necesitamos armar un script con todos los índices de una base de datos. Para ello yo escribí este script en Powershell el cual se encarga de generar dicha salida.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$Servidor = Read-Host "Server Name"
$Basededatos = Read-Host "Database Name"
$Filename =  Read-Host "File Name Script"
$Filename = $Filename + ".SQL"
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server')  $Servidor 
$dbs=$s.Databases
foreach ($tables in $dbs[$Basededatos].Tables)
{
    #Generate script for all indexes in the specified table
    foreach($index in $tables.Indexes)
    {
       $index.Script() + "`r GO `r" | out-File $Filename -Append
    }
}   

Para luego ejecutarlo simplemente es necesario entrar a la consola de nuestro Powershell como vemos a continuación

image

Script de PowerShell para scriptiar los Jobs de un servidor

 

En muchas oportunidades necesitamos hacer un Script de los jobs de producción para llevarlos a otro sitio. Por ejemplo a contingencia.

Para ello podríamos utilizar el siguiente script de Powershell y agregarlo a un step de un job de SQL 2005 / 2008 / R2.

El mismo toma los Jobs que tienen como categoría la palabra producción y genera el script (.sql) de dichos jobs

 

 

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')| out-null

$RutaScripts = "\\servername\"
# Create an SMO connection to the instance
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "SERVIDORSQL" 

$jobs = $s.JobServer.Jobs


$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

$scrp.Options.FileName = $f
$scrp.Options.AppendToFile = $true


foreach ($job in $s.jobserver.jobs) 
{
$a = $job.category




if ($a.toupper() -eq "PRODUCCION")
           
    {
    
     $filename = $job.name + ".sql"
     
     write-host $filename
     
     $f = [System.IO.Path]::Combine($RutaScripts, $filename)
     out-file -filePath $f -inputobject "USE MASTER `nGO`n"


     out-file -filePath $f -inputobject "DECLARE @jobId binary(16) `n SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = N'$job') `n  
     IF (@jobId IS NOT NULL) `n  EXEC msdb.dbo.sp_delete_job @jobid `n  go `n " -append

  
     $scrp.Script($job) >> $f

     out-file -filePath $f -inputobject "`n" -append
  
     out-file -filePath $f -inputobject "`n EXEC msdb.dbo.sp_update_job @job_name=N'$job',@enabled=0 `n GO `n" -append


    }

else
 {

 }
}

 

ScriptJobByPowerShell scriptjobByPowerShell

Como detectar problemas de performance con el Management Studio

 

El día 27/9/2011 di un webcast donde muestro como se puede utilizar el SSMS para determinar problemas de performance.

En el mismo se muestra el uso de los reportes del SSMS como así también del Active Monitor.

Aquí les comparto el link

Webcast TechNet: Detectando problemas de performance usando el Management Studio

Como instalar SQL Server con su Service Pack o Hotfix incluidos

 

Una de las cosas que nos sucede muy a menudo es que cuando instalamos un SQL Server luego de debemos aplicar el ultimo services pack y/o hotfix.

Esto no es una tarea para nada difícil pero son dos pasos en la instalación, esto además de consumirnos mas tiempo en la tarea también puede pasar que nos olvidemos de hacerlo y tengamos nuestro servidor sin los parches adecuados.

En esta guía lo que vamos a ver es como armar un paquete de instalación de SQL Server que incluya además el services pack o hotfix.

La metodología que vamos a utilizar se denomina SlipStreamed, este método apareció en SQL Server 2008 a partir de su primer Service Pack.

Paso 1

Debe copiar el medio de instalación de su SQL server a una carpeta, por ejemplo en mi caso arme una carpeta en el disco D llamada SQL2008R2SP1 entonces copiare el medio de SQL original en D:\SQL2008R2SP1

Aquí le aconsejo que copie todo el medio donde se incluye el instalador de x86 y x64.

Paso 2

Baje el hotfix o service pack, en mi caso baje el service pack 1 de SQL 2008 R2, recuerde que debe bajarlo en el mismo idioma que tiene el medio de instalación, en mi caso es en ingles

Recuerde de bajar tanto el de x86 como el de x64 así luego nos queda un solo medio de instalación para ambas tecnologías

SQLServer2008R2SP1-KB2528583-x64-ENU.exe

SQLServer2008R2SP1-KB2528583-x86-ENU.exe

Paso 3

Debemos extraer los contenidos de los archivos que hemos bajado en el punto 2 a una carpeta interna del paso 1.

En mi caso seria D:\SQL2008R2SP1\SP1.

Para hacer esta operación desde la línea de comandos haremos lo siguiente

D:\>SQLServer2008R2SP1-KB2528583-x64-ENU.exe /x:d:\sql2008r2sp1\sp1
D:\>SQLServer2008R2SP1-KB2528583-x86-ENU.exe /x:d:\sql2008r2sp1\sp1

Paso 4

Copiar los archivos que se descomprimieron en el paso anterior en la carpeta raíz del instalador exceptuando Microsoft.SQL.Chainer.PackageData.dll,

esto se debe hacer por cada una de las plataformas de instalación (x64 y x86)

En el caso del ejemplo deberíamos copiar desde

d:\sql2008r2sp1\sp1\x64 a d:\sql2008r2sp1\x64 y

d:\sql2008r2sp1\sp1\x86 a d:\sql2008r2sp1\x86

Yo voy a utilizar el comando Robocopy que ya viene en mi SO.

D:\>robocopy D:\SQL2008R2SP1\SP1\x86 D:\SQL2008R2SP1\x86 /XF Microsoft.SQL.Chainer.PackageData.dll
D:\>robocopy D:\SQL2008R2SP1\SP1\x64 D:\SQL2008R2SP1\x64 /XF Microsoft.SQL.Chainer.PackageData.dll

Paso 5

Copiar Setup.exe y Setup.rll que se encuentra dentro de la carpeta d:\sql2008r2sp1\sp1 hacia d:\sql2008r2sp1\ reemplazando los que están originales.

D:\>robocopy D:\SQL2008R2SP1\SP1 D:\SQL2008R2SP1 Setup.exe
D:\>robocopy D:\SQL2008R2SP1\SP1 D:\SQL2008R2SP1 Setup.rll

Paso 6

Editar el archivo defaultsetup.ini que se encuentra en cada una de las carpetas del medio original para cada plataforma (x86 y x64), en el caso del ejemplo deberíamos editar los archivos de las siguientes ubicaciones

D:\SQL2008R2SP1\x86\DefaultSetup.ini

D:\SQL2008R2SP1\x64\DefaultSetup.ini

A cada archivos hay que agregarle la siguiente línea

PCUSOURCE=".\SP1"

 

 

Ya tenemos listo el proceso y ahora simplemente ejecutaremos el setup.exe .

Como resolver problemas de conexión remota hacia SQL Server

 

Usted puede presentar problemas de conexión desde una terminal cliente hacia su SQL Server, el error que puede recibir es el 1326 con la siguientes leyendas “Cannot connect to sqlserver”,

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

Pero si puede conectar de forma local.

En este video muestro como se debe resolver este problema

 

Como configurar el envió de mails desde SQL Server

 

En este video muestro cuales son los pasos para poder configurar el correo saliente en SQL Server a partir de la versión 2005 que ya incluye SMTP sin la necesidad de instalar un cliente MAPI como en versiones anteriores.

Además muestro como es la configuración para utilizar el SMTP de gmail o google si es que deseamos salir por ahí.

Como saber que parámetros de configuración se han cambiado y cuales son sus valores por defecto

 

Nuestro SQL Server dispone de varios parámetros de configuración, en muchos casos es necesario hacerle modificaciones a ellos con el cuidado necesario.

Muchas veces me he encontrado que se tocan parámetros sin medir consecuencias y se puede afectar al buen funcionamiento del servidor en si.

Por ello decidí armar este mini video de como podemos saber que parámetros se han modificado pero además saber cuales son los valores que estos tienen por defecto.

Generar Script automático para hacer Attach de las bases en un servidor

 

Cuando necesitamos por ejemplo hacer una migración , ya sea a un nuevo equipamiento y/o a una nueva versión, una de las técnicas para pasar las bases de datos de un lugar al otro es el método Attach / Detach.

Ahora bien, si tenemos pocas bases de datos no hay ningún problema porque hacer el Attach no llevaría mucho tiempo.

Ahora que sucede si tenemos muchas bases de datos? es una tarea bastante engorrosa hacer una por una, yo utilizo para esto un script que generar el código de attach con las ubicaciones de los Data y Log de cada base de datos.

Este script es el que utilizo al hacer este tipo de trabajos y la verdad que no solo me hace ahorrar mucho tiempo sino también cometer menos errores cuando hay una cantidad de bases de datos grande en nuestra instancia.

Aquí les dejo el código y el adjunto del script, espero les sea útil como me fue a mi Sonrisa

 

DECLARE @dbid int
 DECLARE @name varchar(50)
 DECLARE @file varchar(150)
 DECLARE @IDFILE INT
 DECLARE @CMD VARCHAR(4000)
 SET @CMD = ''
 SET @IDFILE = 1

DECLARE cAttach CURSOR
 READ_ONLY
 FOR SELECT dbid, name FROM 
 sysdatabases 
 where name not in 
 ('master','tempdb','model','msdb','northwind', 'pubs','distribution' )
 
OPEN cAttach
 
FETCH NEXT FROM cAttach INTO @dbid, @name
 WHILE (@@fetch_status <> -1)
 BEGIN
 IF (@@fetch_status <> -2)
 BEGIN
 
 -- buscamos los archivos
 DECLARE archivos CURSOR
 READ_ONLY FOR
 select [filename] FROM  sysaltfiles 
                   where dbid = @dbid
                   order by fileid 
 
 OPEN archivos
 FETCH NEXT FROM archivos INTO @file
 WHILE (@@fetch_status <> -1)
 begin
 
 BEGIN
   
   SET @CMD = @CMD +  ',@FILENAME' +CONVERT(varchar(10),@IDFILE) + '=' 
   + '''' + rtrim(@file) + '''' + CHAR(13)
   set @IDFILE = @IDFILE + 1
 end
 FETCH NEXT FROM archivos INTO @file
end
 CLOSE archivos
 DEALLOCATE archivos
 
print '';
 print 'EXEC sp_attach_db @dbname = ''' + (@name) + '''' ;

 print @cmd + ';'
 
 SET @CMD = ''
 SET @IDFILE = 1

END
 FETCH NEXT FROM cAttach INTO @dbid, @name
 END
 
CLOSE cAttach
 DEALLOCATE cAttach
 GO

You cannot specify a provider or any properties for product 'SQL SERVER'.

 

En una migración de servidores desde SQL2000 a 2005 o 2008 si tenemos servidores vinculados (Linked Server) podemos llegar a tener el siguiente error al querer aplicar el Script en el destino (por ejemplo SQL 20008).

Aquí muestro un ejemplo del script

sp_addlinkedserver 'CASACENTRAL','SQL SERVER',

'SQLOLEDB','SQLPROD','','','SERVICECENTRAL'

go

Al ejecutar este script en un 2008 o 2005 nos encontramos con el siguiente error

Msg 15428, Level 16, State 1, Procedure sp_addlinkedserver, Line 40
You cannot specify a provider or any properties for product 'SQL SERVER'.

El problema se da porque el driver SQLOLEDB no existe en 2008 o 2005, para lo cual deberíamos modificar el script como muestro a continuación

EXEC sp_addlinkedserver

@server='CASACENTRAL', 

@srvproduct='',

@provider='SQLNCLI', 

@datasrc='SQLPROD'

go

Aquí solo se ha modificado el Provider por SQLCLI, ahora si nuestro Script va a funcionar y podemos migrar nuestro Linked Server.

Debo mencionar que esto no sucede con todos los Linked sino que básicamente sobre aquellos que usamos un nombre distinto al del servidor SQL,

como pudimos ver en nuestro ejemplo el servidor se llama SQLPROD y el Linked CASACENTRAL.

Además de esto, muchas veces me preguntan como se migran estos linked server, hay varias opciones, a mi me gusta mucho usar esta tool free.

http://www.codeproject.com/cs/database/ScriptLinkedServers.asp