SQL 2005 Blocking Issues

sp_who2 active
sp_blocked
dbcc inputbuffer(74)

Coping SSIS Jobs to different Server

SELECT 'dtutil /COPY SQL;' + NAME + ' /SourceS servera /SQL ' + NAME + ' /DestS serverb
FROM msdb.dbo.sysdtspackages90

Disable and Enable Jobs

set nocount on
set quoted_identifier off
select "exec sp_update_job @job_name = '" + name + "', @enabled = 0 " + "
" + "" from sysjobs where enabled=1 order by 1

Understanding "login failed" (Error 18456) error messages in SQL Server 2005

List of users in a Windows Global group

C:\>net group [GroupName] /domain

ex: NET GROUP DBAGroup /DOMAIN

SQL 2005 - Query to retrive SQL Agent Jobs scheduled time.

select j.name,sc.active_start_time,active_end_time,*
from sysjobs j
inner join dbo.sysjobschedules jsc
on j.job_id = jsc.job_id
inner join dbo.sysschedules sc
on jsc.schedule_id = sc.schedule_id
where j.name like '%ssis%'

MDAC - SNAC components

MDAC is a two side component. i.e; on infrastructure (windows) side and driver (sqlserver) side. MDAC is replaced by SNAC. SNAC is on the "drivers" side.

In a nutshell, SQL Native Client is a stand alone data access Application Programming Interface (API) that is used for both OLE DB and ODBC. It combines the SQL OLE DB provider and the SQL ODBC driver into one native dynamic link library (DLL) while also providing new functionality above and beyond that supplied by the Microsoft Data Access Components (MDAC). SQL Native Client can be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2005 features such as Multiple Active Result Sets (MARS), User-Defined Types (UDT), and XML data type support.

MDAC now ships as a component of the Windows operating system and as such there are a number of setup, redistribution, and deployment issues that have occurred as a result of this. you have developed an application based on the latest MDAC release only to discover that when you deploy it, the users in your organization do not have the latest MDAC release so your app won’t function correctly. By wrapping the OLE DB and ODBC technologies into a single library, we are able to avoid these issues by making a clean break from MDAC so that you can effectively deploy SQL Native Client as needed, without concern about if it will “play nicely” with other versions of MDAC.

Data Access Technology Road Map
http://msdn.microsoft.com/en-us/library/ms810810.aspx

SQL Server Consolidation

What is SQL Server Consolidation
Reducing the Number of Physical Sql Servers and also Sql Server Instances.

What are Business Drivers for Sql Server Consolidations.
1. Reduction of Total Cost of Ownership. (Maintaining Personal / Patch Management / Data centre Costs/ Energy Consumption (GreenIT)).
2. Best practices like database maintaince, back up,high availbility,DR strategies etc.

-----------------------------------------
Features of SQL SERVER 2005 for Consolidation

1. Large Scale Performance (Scale UP)
2. non-uniform memory access (NUMA) architecture
3. Work Load Governerance.
4. Multiple applications connecting through two different IP Address.

-----------------------------------------

Microsoft Consolidation Options

1. Hardware Partioning
High Throughput Applications
Complete Isolation
Reboot after resizing


2. Resource Management
Medium-High Throughput Applications
Manage Resource Usuage
Same Operating system level

3. Virtualization
Medium-Low throughput
Isolating legac applications
Complex management
Limed scale-up

OLAP vs OLTP