How to Create and Schedule a SSIS Job in SQL Server 2005
Posted at Sunday, June 01, 2008
This post covers how to create and schedule a SSIS job on SQL Server 2005.
The biggest challenge in doing this has to do more with the convaluted security setup, than with the SSIS package creation and/or scheduling.
The first step is to create the necessary security objects:
ssis_security_setup.sql
Create a Login who will own the job
Grant the sysadmin role to the previously created login
Create a user in the msdb system database for the login, creating the mapping
Grant the following roles to the user
Create a credential to be used by the proxy - the proxy will execute the SSIS package within the job
Create the proxy, mapping to the credential and provide access to the SSIS subsystem
Now you are ready to create a sample SSIS package.
I launch the SQL Server Business Intelligence Development Studio via All Programs -> Microsoft SQL Server 2005 -> SQL Server Business Intelligence Development Studio
I create a project named sample_ssis, which consists of a Transfer Database Task, which refreshes a database from one SQL Server instance to another:
sample_ssis.dstx
Task Properties
Once the sample SSIS package is created, import into the msdb system database via Integration Services:
Login to Integration Services
Import the SSIS Package from the File System
Login to the Database Engine using ssis_usr
Create a SQL Server Agent Job
Create a Step within the Job run by ssis_prxy
Create a Schedule for the Job
Monitor the Job
You should see something similar to the aforementioned if your job executed successfully.
The biggest challenge in doing this has to do more with the convaluted security setup, than with the SSIS package creation and/or scheduling.
The first step is to create the necessary security objects:
ssis_security_setup.sql
Create a Login who will own the job
USE [master]
GO
CREATE LOGIN [ssis_usr] WITH PASSWORD=N'password', DEFAULT_DATABASE=[msdb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
Grant the sysadmin role to the previously created login
sp_addsrvrolemember @loginame = N'ssis_usr', @rolename = N'sysadmin'
GO
Create a user in the msdb system database for the login, creating the mapping
USE [msdb]
GO
CREATE USER [ssis_usr] FOR LOGIN [ssis_usr]
GO
Grant the following roles to the user
sp_addrolemember N'SQLAgentReaderRole', N'ssis_usr';
GO
sp_addrolemember N'SQLAgentOperatorRole', N'ssis_usr'
GO
sp_addrolemember N'SQLAgentUserRole', N'ssis_usr'
GO
Create a credential to be used by the proxy - the proxy will execute the SSIS package within the job
USE [master]
GO
CREATE CREDENTIAL [ssis_cred] WITH IDENTITY = N'PRIVATE-E3A52F5\James', SECRET='password';
GO
Create the proxy, mapping to the credential and provide access to the SSIS subsystem
USE [msdb]
GO
sp_add_proxy @proxy_name=N'ssis_prxy', @credential_name=N'ssis_cred';
GO
sp_grant_proxy_to_subsystem @proxy_name=N'ssis_prxy', @subsystem_name=N'SSIS';
GO
Now you are ready to create a sample SSIS package.
I launch the SQL Server Business Intelligence Development Studio via All Programs -> Microsoft SQL Server 2005 -> SQL Server Business Intelligence Development Studio
I create a project named sample_ssis, which consists of a Transfer Database Task, which refreshes a database from one SQL Server instance to another:
sample_ssis.dstx
Task Properties
Once the sample SSIS package is created, import into the msdb system database via Integration Services:
Login to Integration Services
Import the SSIS Package from the File System
Login to the Database Engine using ssis_usr
Create a SQL Server Agent Job
Create a Step within the Job run by ssis_prxy
Create a Schedule for the Job
Monitor the Job
You should see something similar to the aforementioned if your job executed successfully.
Labels: SQL Server
SQL Server: Point in Time Recovery Example
Posted at Thursday, May 29, 2008
This example demonstrates how to backup a SQL Server Database and then restore it to a specific point in time.
The database in question uses the FULL recovery model, which supports such a PITR (Point in Time Recovery). The example uses a simple table to illustrate how all this works.
The example first creates a sample table and performs a full backup on the database. Then, values are inserted into the table periodically, interlaced with transaction log backups.
Set-up the example by running PITRBackupExample.sql.
The script yields the following output.
In this example, I want to restore the database to the point at which the 'Point-in-time I want to recover to!' value was inserted into the sample table. To do this, a restore point just after this transaction, specifically the '2008-05-28 11:58:09.680' timestamp, will need to be specified.
The recovery first places the database in single-user mode and performs a tail-log transaction log backup. Then, the full backup and transaction log backups are applied. For the last transaction log backup the STOPAT option is specified, along with the WITH RECOVERY option, which denotes that this operation is to end the recovery. Afterwards, the database is placed into multi-user mode. Last, we check the sample table to make sure that we recovered successfully to the desired timestamp.
Recover the example by running PITRExample.sql
The script yields the following output
The database in question uses the FULL recovery model, which supports such a PITR (Point in Time Recovery). The example uses a simple table to illustrate how all this works.
The example first creates a sample table and performs a full backup on the database. Then, values are inserted into the table periodically, interlaced with transaction log backups.
Set-up the example by running PITRBackupExample.sql.
The script yields the following output.
In this example, I want to restore the database to the point at which the 'Point-in-time I want to recover to!' value was inserted into the sample table. To do this, a restore point just after this transaction, specifically the '2008-05-28 11:58:09.680' timestamp, will need to be specified.
The recovery first places the database in single-user mode and performs a tail-log transaction log backup. Then, the full backup and transaction log backups are applied. For the last transaction log backup the STOPAT option is specified, along with the WITH RECOVERY option, which denotes that this operation is to end the recovery. Afterwards, the database is placed into multi-user mode. Last, we check the sample table to make sure that we recovered successfully to the desired timestamp.
Recover the example by running PITRExample.sql
The script yields the following output
Labels: Backup and Recovery, SQL Server
Unable to Open MSDB in Integration Services when using Named Instance(s)
Posted at Tuesday, May 27, 2008
Follow the accepted answer from the following post: Re: Unable to open MSDB node if you are using a named SQL Server instance and you can't access the SSIS packages within the MSDB database.
You will experience a 'Login Timeout' exception until you edit the XML file mentioned in the post. By default, Microsoft is configured to connect to the DEFAULT instance only; short of modifying this file, there is no way of specifying a different Database Service for Integration Services to use.
You will experience a 'Login Timeout' exception until you edit the XML file mentioned in the post. By default, Microsoft is configured to connect to the DEFAULT instance only; short of modifying this file, there is no way of specifying a different Database Service for Integration Services to use.
Labels: Bugs, SQL Server
SQL Server Statistics Maintenance Job
Posted at Tuesday, May 20, 2008
I use the following job to augment SQL Server's auto statistics create/update functionality:
create_update_statistics.sql
You can adjust this to fit your needs and implement as part of your Maintenance Plans and/or run from a batch file, such as:
create_update_statistics.bat
create_update_statistics.sql
sp_MSforeachdb @command1='
USE ?;
IF NOT ("?" IN ("model","msdb","master","tempdb"))
BEGIN
EXEC sp_createstats;
DECLARE @tbls TABLE (rowId INT IDENTITY(1,1), tblschema NVARCHAR(128), tblname NVARCHAR(128));
DECLARE @stmt NVARCHAR(2000), @rowId INT, @maxRowId INT, @tblschema NVARCHAR(128), @tblname NVARCHAR(128);
INSERT INTO @tbls (tblschema, tblname)
SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type="BASE TABLE";
SELECT @rowId = MIN(rowId), @maxRowId = MAX(rowId) FROM @tbls;
WHILE @rowId <= @maxRowId
BEGIN
SELECT @tblschema = tblschema, @tblname = tblname FROM @tbls WHERE rowId = @rowId;
SET @stmt = "UPDATE STATISTICS " + "[" + @tblschema + "].[" + @tblname + "];";
PRINT "?: " + @stmt;
EXECUTE (@stmt);
SET @rowId = @rowId + 1;
END
END
';
You can adjust this to fit your needs and implement as part of your Maintenance Plans and/or run from a batch file, such as:
create_update_statistics.bat
sqlcmd -S PRIVATE-E3A52F5\SS1 -E -i create_update_statistics.sql -o create_update_statistics.out
notepad.exe create_update_statistics.out
Labels: SQL Server, Statistics
Backup and Recovery of SQL Server System Databases
Posted at Monday, May 19, 2008
This posting covers how to backup and restore SQL Servers' system databases - master, msdb, model, etc.
In this example, I backup SQL Server's databases and then replicate a loss/corruption - and subsequent restoration - of the master database's underlying datafiles.
In SQL Server 2000, the rebuildm.exe executable was available to help you rebuild the master database so that you could start the SQL Server and then, in turn, restore the master (and related system databases) database from an available backup.
In SQL Server 2005, the rebuildm.exe executable is not available, rather the approved procedure is to call the setup.exe of the installation binaries, passing the appropriate parameters/switches, etc. in order to rebuild the master database. Afterwards, you could startup the database engine and then restore the system databases from backups. Unfortunately, this process is riddled with bugs, particularly post application of SP2.
Given the aforementioned, my preferred approach is to retain offline copies of the system databases' underlying datafiles. In the event of a loss/corruption of the master database, I simply restore these old datafile copies to the default location. This allows me to startup the database engine and restore the system databases from backups, avoiding the above flawed mechanisms. Such datafile backups should be taken at key times, such as post-upgrade, etc.
All the scripts used in this example are available in the following .zip file:
bckp_restore_sys_dbs_sql2005.zip
My example uses SQL Server 2005 - my instance is named PRIVATE-E3A52F5\SS1
Step 1 - backup_master_files.bat
I first take an offline file backup of the master database's datafiles:
Step 2 - backup_databases.bat
Next, I backup all the SQL Server's databases via a batch file:
This batch file calls the following SQL script:
backup_all_databases.sql
Yielding the following output:
Step 3 - destroy_master.bat
When ready to perform the test, I call this batch file to simulate a destruction/corruption of the master database.
At this point, attempts to start the database engine, would yield something similar to the following with the ERRORLOG:
Step 4 - recover_master.bat
Next, a batch file is called to perform the recovery:
The batch file first restores the offline backups of the master database's datafiles and then performs a recovery of that database via:
restore_master.sql
Yielding the following output:
Next, the batch file restores the other system databases via:
restore_databases.sql
Yielding the following output:
At this point, your SQL Server Instance should be ready for use.
In this example, I backup SQL Server's databases and then replicate a loss/corruption - and subsequent restoration - of the master database's underlying datafiles.
In SQL Server 2000, the rebuildm.exe executable was available to help you rebuild the master database so that you could start the SQL Server and then, in turn, restore the master (and related system databases) database from an available backup.
In SQL Server 2005, the rebuildm.exe executable is not available, rather the approved procedure is to call the setup.exe of the installation binaries, passing the appropriate parameters/switches, etc. in order to rebuild the master database. Afterwards, you could startup the database engine and then restore the system databases from backups. Unfortunately, this process is riddled with bugs, particularly post application of SP2.
Given the aforementioned, my preferred approach is to retain offline copies of the system databases' underlying datafiles. In the event of a loss/corruption of the master database, I simply restore these old datafile copies to the default location. This allows me to startup the database engine and restore the system databases from backups, avoiding the above flawed mechanisms. Such datafile backups should be taken at key times, such as post-upgrade, etc.
All the scripts used in this example are available in the following .zip file:
bckp_restore_sys_dbs_sql2005.zip
My example uses SQL Server 2005 - my instance is named PRIVATE-E3A52F5\SS1
Step 1 - backup_master_files.bat
I first take an offline file backup of the master database's datafiles:
net stop "SQL Server Agent (SS1)"
net stop "SQL Server (SS1)"
copy /Y "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf" "C:\backups\master.mdf"
copy /Y "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf" "C:\backups\mastlog.ldf"
net start "SQL Server (SS1)"
net start "SQL Server Agent (SS1)"
Step 2 - backup_databases.bat
Next, I backup all the SQL Server's databases via a batch file:
sqlcmd -S PRIVATE-E3A52F5\SS1 -E -i "C:\Documents and Settings\James\Desktop\backup_all_databases.sql" -o "C:\Documents and Settings\James\Desktop\backup_all_databases.out"
This batch file calls the following SQL script:
backup_all_databases.sql
--Use the foreach stored procedure
--To backup each database to disk - except - tempdb
--Create necessary sub-directories and validate each backup
EXEC master.sys.sp_MSforeachdb @command1='
BEGIN
DECLARE @bkpfile NVARCHAR(1000), @bkploc NVARCHAR(500), @bkpname NVARCHAR(500);
DECLARE @backupsetid INT;
SET @bkploc = ''C:\backups\?\'';
PRINT @bkploc;
SET @bkpname = @bkploc + ''?_backup_''+ cast(datepart(yyyy, getdate()) as nvarchar) + cast(datepart(mm,getdate()) as nvarchar) + cast(datepart(dd, getdate()) as nvarchar) + cast(datepart(hh, getdate()) as nvarchar) + cast(datepart(mi, getdate()) as nvarchar);
PRINT @bkpname;
SET @bkpfile = @bkpname + ''.BAK'';
IF NOT (''?'' = ''tempdb'')
BEGIN
BEGIN
EXECUTE master.dbo.xp_create_subdir @bkploc;
END;
BEGIN
BACKUP DATABASE ? TO DISK = @bkpfile WITH NOFORMAT, NOINIT, NAME = @bkpname, SKIP, REWIND, NOUNLOAD;
END;
BEGIN
SELECT @backupsetid = position FROM msdb.dbo.backupset WHERE database_name=''?'' AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb.dbo.backupset WHERE database_name=''?'' );
RESTORE VERIFYONLY FROM DISK = @bkpfile WITH FILE = @backupsetid, NOUNLOAD, NOREWIND
END;
END;
END';
GO
Yielding the following output:
C:\backups\master\
C:\backups\master\master_backup_20085181746
Processed 352 pages for database 'master', file 'master' on file 1.
Processed 2 pages for database 'master', file 'mastlog' on file 1.
BACKUP DATABASE successfully processed 354 pages in 0.517 seconds (5.599 MB/sec).
The backup set on file 1 is valid.
C:\backups\tempdb\
C:\backups\tempdb\tempdb_backup_20085181746
C:\backups\model\
C:\backups\model\model_backup_20085181746
Processed 152 pages for database 'model', file 'modeldev' on file 1.
Processed 2 pages for database 'model', file 'modellog' on file 1.
BACKUP DATABASE successfully processed 154 pages in 0.285 seconds (4.426 MB/sec).
The backup set on file 1 is valid.
C:\backups\msdb\
C:\backups\msdb\msdb_backup_20085181746
Processed 600 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 6 pages for database 'msdb', file 'MSDBLog' on file 1.
BACKUP DATABASE successfully processed 606 pages in 0.763 seconds (6.500 MB/sec).
The backup set on file 1 is valid.
C:\backups\ReportServer$SS1\
C:\backups\ReportServer$SS1\ReportServer$SS1_backup_20085181746
Processed 288 pages for database 'ReportServer$SS1', file 'ReportServer$SS1' on file 1.
Processed 1 pages for database 'ReportServer$SS1', file 'ReportServer$SS1_log' on file 1.
BACKUP DATABASE successfully processed 289 pages in 0.496 seconds (4.773 MB/sec).
The backup set on file 1 is valid.
C:\backups\ReportServer$SS1TempDB\
C:\backups\ReportServer$SS1TempDB\ReportServer$SS1TempDB_backup_20085181746
Processed 176 pages for database 'ReportServer$SS1TempDB', file 'ReportServer$SS1TempDB' on file 1.
Processed 1 pages for database 'ReportServer$SS1TempDB', file 'ReportServer$SS1TempDB_log' on file 1.
BACKUP DATABASE successfully processed 177 pages in 0.357 seconds (4.061 MB/sec).
The backup set on file 1 is valid.
Step 3 - destroy_master.bat
When ready to perform the test, I call this batch file to simulate a destruction/corruption of the master database.
net stop "SQL Server Agent (SS1)"
net stop "SQL Server (SS1)"
del "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf"
del "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf"
At this point, attempts to start the database engine, would yield something similar to the following with the ERRORLOG:
2008-05-18 13:09:26.73 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
2008-05-18 13:09:26.73 Server (c) 2005 Microsoft Corporation.
2008-05-18 13:09:26.73 Server All rights reserved.
2008-05-18 13:09:26.73 Server Server process ID is 3360.
2008-05-18 13:09:26.73 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2008-05-18 13:09:26.74 Server This instance of SQL Server last reported using a process ID of 456 at 5/18/2008 1:08:35 PM (local) 5/18/2008 7:08:35 PM (UTC). This is an informational message only; no user action is required.
2008-05-18 13:09:26.74 Server Registry startup parameters:
2008-05-18 13:09:26.74 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2008-05-18 13:09:26.74 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2008-05-18 13:09:26.74 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2008-05-18 13:09:26.74 Server Error: 17113, Severity: 16, State: 1.
2008-05-18 13:09:26.74 Server Error 2(The system cannot find the file specified.) occurred while opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
Step 4 - recover_master.bat
Next, a batch file is called to perform the recovery:
net stop "SQL Server Agent (SS1)"
net stop "SQL Server (SS1)"
net stop "SQL Server Analysis Services (SS1)"
net stop "SQL Server FullText Search (SS1)"
net stop "SQL Server Integration Services"
net stop "SQL Server Reporting Services (SS1)"
copy /Y "C:\backups\master.mdf" "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf"
copy /Y "C:\backups\mastlog.ldf" "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf"
net start "SQL Server (SS1)" /m
sqlcmd -S PRIVATE-E3A52F5\SS1 -E -i "restore_master.sql" -o "restore_master.out"
net start "SQL Server (SS1)"
sqlcmd -S PRIVATE-E3A52F5\SS1 -E -i "restore_databases.sql" -o "restore_databases.out"
net start "SQL Server Agent (SS1)"
net start "SQL Server Analysis Services (SS1)"
net start "SQL Server FullText Search (SS1)"
net start "SQL Server Integration Services"
net start "SQL Server Reporting Services (SS1)"
The batch file first restores the offline backups of the master database's datafiles and then performs a recovery of that database via:
restore_master.sql
RESTORE DATABASE [master] from DISK = 'C:\backups\master\master_backup_20085181746.BAK' WITH REPLACE ;
GO
Yielding the following output:
Processed 352 pages for database 'master', file 'master' on file 1.
Processed 2 pages for database 'master', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.
Next, the batch file restores the other system databases via:
restore_databases.sql
RESTORE DATABASE [msdb] from DISK = 'C:\backups\msdb\msdb_backup_20085181746.BAK' WITH REPLACE ;
GO
RESTORE DATABASE [model] from DISK = 'C:\backups\model\model_backup_20085181746.BAK' WITH REPLACE ;
GO
Yielding the following output:
Processed 600 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 6 pages for database 'msdb', file 'MSDBLog' on file 1.
RESTORE DATABASE successfully processed 606 pages in 0.531 seconds (9.341 MB/sec).
Processed 152 pages for database 'model', file 'modeldev' on file 1.
Processed 2 pages for database 'model', file 'modellog' on file 1.
RESTORE DATABASE successfully processed 154 pages in 0.127 seconds (9.933 MB/sec).
At this point, your SQL Server Instance should be ready for use.
Labels: Backup and Recovery, SQL Server
Configuring Database Mail for SQL Server 2005
Posted at Friday, August 25, 2006
I created a how-to article to assist others in setting up Database Mail for SQL Server 2005, using a local SMTP Server; here it is
How to Configure Database Mail SQL Server 2005
Feel free to contact me with any questions, etc.
How to Configure Database Mail SQL Server 2005
Feel free to contact me with any questions, etc.
Labels: SQL Server
_507.png)
Subscribe
