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

Data Quality Process

Profiling (Identifying data quality issues).
Generalized Cleansing (tests to meet business rules).
Parsing and standardization (restructing data into a common format).
Matching (finding unique identifiers and performint de-duplication).
Enrichment (phone and email validation)
Monitoring (checking conformance to data quality requirements).

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

The construction of data warehouses involves data cleaning, data integration, and data transformation.

OLAP operations such as roll-up, drill-down, slicing, and dicing.

A data warehouse is a subject-oriented, integrated, time-variant, and nonvolatile collection of data in support of management’s decision making process

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

SSRS 2008 Tablix -- Repeating Column Header for Tablix (Table)

Go to the Tablix advanced mode, select the static member from the Row Groups and set the properties as shown in the figure

Relational VS MultiDimensional (OLAP) Databases

The key difference between OLAP dimensions and simple relational dimensions is the central role played by hierarchies in OLAP implementations.An OLAP dimension is strongly structured around its hierarchies, and the metadata of a cube definition includes the hierarchical levels.
This is one of the great strengths of an OLAP implementation.

OLAP is a sibling of dimensional models in the relational database, with intelligence about relationships and calculations defined on the server, that enable faster query performance and more interesting analytics from a broad range of query tools.

The recommended architecture for most purposes feeds the OLAP server from a dimensional data warehouse in the relational DBMS.

Overall the following points are important in OLAP.
1. Meta Data (like semi additive and non additive info),
2. Calculations Defined (Pre-aggregated data) and
3. Analaytical Functions defined on the Server

Example explaining the advantage of Hierarichies in OLAP
A query such as total sales for Q1 2002 is simple to formulate and should return from an OLAP server nearly instantaneously. But the user who wants total sales for an arbitrary period such as January 3 through March 12, 2002, for which no predefined hierarchy exists.

Advantages are as follows
1. It provides an intuitive user interface for browsing data.
2. It gives you spectacular query performance, primarily owing to the intelligent navigation of aggregates and partitions.
3. Parent-child dimension structures are easy and intuitive to implement.
3. It gives you server-defined rules for handling semiadditive and nonadditive measures.


The above explanation holds good for SSAS vs Sql Server Relational, Sql Server Relational vs Microstrategy,

Fact and Dimenisional Tables Relationships

Star schema, which maintains one-to-many relationships between dimensions and a fact table is widely accepted as the most viable data representation for dimensional analysis.

Realworld DW schema, however, frequently includes many-to-many relationships between a dimensionand a fact table.

Issues of many-to-many relationships.
1. losing the simplicity of the star schema structure
2. increasing complexity in forming queries, and
3. degrading query performance by adding more joins.

Two ad-hoc methods to resolve

1. Denormalizing the dimension tables.
2. Building the Bridge Table.

To find more information : go to the white paper An Analysis of Many-to-Many Relationships Between Fact andDimension Tables in Dimensional Modeling

Dimensional Modeling - Design Changes

The following changes are anticipated to the design after the data warehouse is up and running

1. Adding new unanticipated facts (that is, new additive numeric fields in the fact table), as long as they are consistent with the fundamental grain of the existing fact table.

2. Adding completely new dimensions, as long as there is a single value of that dimension defined for each existing fact record.

3. Adding new, unanticipated dimensional attributes.

4. Breaking existing dimension records down to a lower level of granularity from a certain point in time forward.

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