SSIS Documentation

Description

• Author: Brian Knight, Allan Mitchell, Darren Green, and Douglas Hinson
• Publisher: Wrox
• Publish Date: January 12, 2006
• Pages: 720

This book will help you get past the initial learning curve quickly so that you can get started using SSIS to transform data, create a workflow, or maintain your SQL Server. Offering you hands-on guidance, you'll learn a new world of integration possibilities and be able to move away from scripting complex logic to programming tasks using a full-featured language.
What you will learn from this book

• Ways to quickly move and transform data
• How to configure every aspect of SSIS
• How to interface SSIS with web services and XML
• Techniques to scale the SSIS and make it more reliable
• How to migrate DTS packages to SSIS
• How to create your own custom tasks and user interfaces
• How to create an application that interfaces with SSIS to manage the environment
• A detailed usable case study for a complete ETL solution

Who this book is for
This book is for developers, DBAs, and users who are looking to program custom code in all of the .NET languages. It is expected that you know the basics of how to query the SQL Server and have some fundamental programming skills.

When does my SQL Server support end ?

SQL Server Version	Release Date	Mainstream Support End	Extended Support End	Link to Microsoft Support Lifecycle
SQL 7	03-01-1999	12-31-2005	01-11-2011	Support Lifecycle
SQL 2000	11-30-2000	04-08-2008	04-09-2013	Support Lifecycle
SQL 2005	01-14-2006	04-12-2011	04-12-2016	Support Lifecycle
SQL 2008	11-06-2008	01-14-2014	01-08-2019	Support Lifecycle
SQL 2008 R2	07-20-2010	01-14-2014	01-08-2019	Support Lifecycle

The Support Lifecycle links will have more detailed information about Service Packs and support guidelines for the specific versions.

Query to find Service account of SQL Server

DECLARE @serviceaccount varchar(100)

EXECUTE master.dbo.xp_instance_regread

N'HKEY_LOCAL_MACHINE',

N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',

N'ObjectName',

@ServiceAccount OUTPUT,

N'no_output'

SELECT @Serviceaccount

How to retrieve table name and privilages of user in SQL server

Select TABLE_NAME, PRIVILEGE_TYPE from INFORMATION_SCHEMA.TABLE_PRIVILEGES where

GRANTEE = 'Toolbox'

Query to find the job history in SQL Server

select a.run_date,b.name,a.run_duration,run_time,a.server  from sysjobhistory a inner join sysjobs b on a.job_id=b.job_id where a.job_id='8E7D12F1-6B7E-4C08-8063-D8AAFD99F267' and a.step_id =0  order by a.run_date desc

Query to find the object permission for specific objects

select

sys.database_permissions.state_desc + ' ' + sys.database_permissions.permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS

from sys.database_permissions

join sys.objects on sys.database_permissions.major_id =

sys.objects.object_id

join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id

join sys.database_principals on sys.database_permissions.grantee_principal_id =

sys.database_principals.principal_id

where sys.objects.name='object_name’

How to find table count of all tables in a database in sql

How to find table count of all tables in a database

SELECT o.name AS "Table Name", i.rowcnt AS "Row Count"

FROM sysobjects o, sysindexes i

WHERE i.id = o.id

AND indid IN(0,1)

AND xtype = 'u'

AND o.name <> 'sysdiagrams'

ORDER BY i.rowcnt DESC

Can we take backup of Resource database

We can't take backup of resource database but we can have backup of Data file and log file..

In SQL Server  changes will only be made to the this database and will be reflected on all the system and user databases on the instance. If you need to apply a service pack on multiple instances, all you need to do is copy the Resource database's MDF and LDF files to the target instances. Rolling back the changes is as simple as overwriting the database files with an older copy. The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf and are located, by default, in <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Ghost cleanup in sql server

Ghost Cleanup is the removal of pages that have been marked for deletion.  When a record is deleted, it is marked with a bit, and is no longer "available".  In other words, it becomes a ghost record.  The Ghost Cleanup process comes along, and physically removes the records, so the space can be reused.  If you want to stop Ghost clean up follow the below process

Trace flag 661: Disable the ghost record removal process

Trace flag 661 disables the ghost record removal process. A ghost record is the result of a delete operation. When you delete a record, the deleted record is kept as a ghost record. Later, the deleted record is purged by the ghost record removal process. When you disable this process, the deleted record is not purged. Therefore, the space that the deleted record consumes is not freed. This behavior affects space consumption and the performance of scan operations.

When you turn on trace flag 661 at startup or in a user session, trace flag 661 always applies across the server and has global scope. If you turn off this trace flag, the ghost record removal process works correctly.

WAL in SQL server

.WAL      

A)    Write ahead logging protocol  (WAL)  is a technique which helps to adhere to two of the four acid properties ‘Atomicity’ and durability, Now that we understand what is atomicity and durability… next is to understand “How WAL works” and also we need to peep into the advantages we get from it.

B)    Let me just put my words as per SQL Books online which is very much easier to understand:
Write-ahead log (WAL) guarantees that no data modifications are written to disk before the associated log record is written to disk. This maintains the ACID properties for a transaction.

C)    To understand how the write-ahead log works, it is important for us to know how modified data is written to disk. SQL Server maintains a buffer cache into which it reads data pages when data must be retrieved. Data modifications are not made directly to disk, but are made to the copy of the page in the buffer cache. The modification is not written to disk until a checkpoint occurs in the database, or the modification needs to be written to disk so the buffer can be used to hold a new page. Writing a modified data page from the buffer cache to disk is called flushing the page. A page modified in the cache, but not yet written to disk, is called a dirty page.

D)    At the time when modification is made to a page in the buffer, a log record is built in the log cache that records the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. If the dirty page is flushed before the log record is written, the dirty page creates a modification on the disk that cannot be rolled back if the server fails before the log record is written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written. Log records are written to disk when the transactions are committed.