Service Pack 4 for SQL Server 2005

The next service pack for SQL Server 2005 has been released. Service pack 4 includes cumulative update 1 to 11, some customer requested fixes as well as database Engine support for DAC operations.

It can be downloaded here.

SQL Bits 8

The next SQLBits conference is to be in Brighton from the 7th to 9th April 2011. More details from http://www.sqlbits.com/

SQL Server 2008 R2 System Views Map

This Systems Views Map shows the key system views included in SQL Server 2008 and 2008 R2, and the relationships between them.

It can be downloaded from here

Major Database Conferences

I started looking to see what database conferences there are and to my surprise there are a vast number . In my SQL Server field I follow:

SQL Bits
http://sqlbits.com/
SQL Bits has been started by a group of individuals that are passionate about the SQL Server product suite. There is a breadth of knowledge in the SQL Community that will benefit everyone in the community. We want to spread that knowledge. We all work with the SQL community, some of us for many years and have all been given the MVP award by Microsoft...

TechEd
http://www.microsoft.com/europe/teched/
Conference provides developers and IT professionals the most comprehensive technical education across the Microsoft current and soon-to-released suite of products, solutions and services…

Professional Association for SQL Server
http://sqlpass.org/
Stay up to date on developments in the SQL Server world through our focused newsletters, eBlasts, and SQL Server events etc..

There are other professional database specific conferences for Oracle , MySQL, NoSQL… etc along with:

VLDB: Very Large Data Bases
http://www.vldb.org/
The sole purpose of promoting and exchanging scholarly work in databases and related fields throughout the world…

SIGMOD: ACM SIGMOD Conf on Management of Data
http://www.sigmod.org/
SIGMOD's Mission The ACM Special Interest Group on Management of Data is concerned with the principles, techniques and applications of database management systems and data management technology…

PODS: ACM SIGMOD Conf on Principles of DB Systems
PODS, the Symposium on Principles of Database Systems, is the premiere international conference on the theoretical aspects of database systems...

ICDE: IEEE Intl Conf on Data Engineering
http://www.icde2010.org/
Data Engineering deals with the use of engineering techniques and methodologies in the design, development and assessment of information systems for different computing platforms and application environments...

ICDT: Intl Conf on Database Theory
http://alpha.uhasselt.be/icdt/
ICDT is a scientific conference on research on the foundations of database systems; it can be seen as the European version of PODS (the ACM Symposium on Principles of Database Systems)...

EDBT: Extending DB Technology
http://www.edbt.org/
The EDBT Association promotes and supports research and progress in the fields of databases and information systems technology and applications...

DAMA
http://www.dama.org
DAMA International is a global association of technical and business professionals dedicated to advancing the concepts and practices of information and data management...

CIKM: Intl. Conf on Information and Knowledge Management http://www.cikmconference.org/
The Conference on Information and Knowledge Management (CIKM) provides an international forum for presentation and discussion of research on information and knowledge management, as well as recent advances on data and knowledge bases. The purpose of the conference is to identify challenging problems facing the development of future knowledge and information systems, and to shape future directions of research by soliciting and reviewing high quality, applied and theoretical research findings...

IDEAS - International Database Engineering and Application Symposium http://ideas.concordia.ca/
The aim of the IDEAS series of symposiums is to address the engineering and application aspects of databases. The symposium provides an international forum for discussion of the problems of engineering database systems involving not only database technology but the related areas of information retrieval, multimedia, human machine interface and communication…

DEXA: Database and Expert System Applications
http://www.dexa.org/
Information systems and database systems have always been a central topic of computer science. In addition, the integration of knowledge, information and data justifies its actual attractiveness. Since 1990 DEXA is an annual international conference, located in Europe, which showcases state-of-the-art research activities in these areas...

DASFAA: Database Systems for Advanced Applications
http://www.dasfaa.org/
As an annual international conference in Pacific Asia region, DASFAA is an international forum for academic exchanges and technical discussions among researchers, developers and users of databases from academia, business and industry. The conference will promote research and development activities in database field among participants and their institutions from Pacific Asia and the world as well...

BNCOD: British National Conference on Databases
http://www.bncod2010.org.uk/
BNCOD was established in 1980, as the British National Conference on Databases, as a forum for research into the theory and practice of databases. Since then, BNCOD has become an international information systems conference and has attracted an international audience to discuss the leading research topics of the day in the field...

ADBIS: Symposium on Advances in DB and Information Systems
http://www.adbis2010.org/
The main objective of the ADBIS series of conferences is to provide a forum for the dissemination of research accomplishments and to promote interaction and collaboration between the database and information systems research communities from Central and East European countries and the rest of the world...

KRDB - Knowledge Representation Meets Databases
http://sunsite.informatik.rwth-aachen.de/Societies/KRDB/
KRDB (Knowledge Representation meets Databases) is an opportunity for researchers and practitioners from the two areas to exchange ideas and results. KRDB meets annually during major KR/AI and DB conferences...

CoopIS - Conference on Cooperative Information Systems
http://www.onthemove-conferences.org/index.php/coopis2010
Cooperative Information Systems provide enterprises and communities of users with flexible, scalable and intelligent services in large-scale networking environments.... The CIS paradigm has traditionally encompassed distributed systems technologies such as middleware, business process management (BPM) and Web technologies. In recent years, several innovative technologies are emerging: SaaS, cloud computing, Internet of Service, Internet of Things, Service Oriented Computing, mash-ups, Web Services, Semantic Web and Knowledge Grid. These new technologies enable us to consider more aggressive solutions for building scalable cooperative information systems...

A database system

Basically, it is nothing more than a computer record keeping system: this is, a system whose overall purpose is to record and maintain information. The information concerned can be anything that is deemed to be of significance to the organization the system is serving – anything, in other words, that may be necessary to the decision-making processes involved in the management of that organisation.

Defined by Date, C.J. (1981) An Introduction to Database Systems, Boston, Addison-Wesley.

Definition of a System

A model of a whole entity: when applied to human activity, the model is characterized fundamentally in terms of hierarchical structure, emergent properties, communication, and control. An observer may chose to relate this model to real-world activity. When applied to natural or man-made entities, the crucial characteristic is the emergent properties of the whole.

Defined by Checkland, P.B. (1999) Systems thinking, systems practice, Chichester, John Wiley.

Denali - The next SQL version

The first CTP has been released for Denali, the next version of SQL Server, likely to be released to Manufacturing in 2012. Microsoft
SQL Server isn’t just a database, but an entire "information platform", stated Ted Kummert from Microsoft.

Denali will introduce holistic data integration and a management solution focusing on data with new Data Quality Services for knowledge-driven data cleansing and Impact Analysis and Lineage.

SQL Server AlwaysOn will provide a set of capabilities to help businesses maximize uptime of their mission critical applications and simplify high availability deployments.

This version will be the largest SQL Server Integration Services (SSIS) release.

Microsoft codename Atlanta is a secure cloud service that proactively monitors your Microsoft SQL Server deployments. It will capture configuration information that may later be required for troubleshooting and allow tracking of changes nightly.

SQL Server 2008 R2 Parallel Data Warehouse was also just launched.

Recovery Markers for Business Continuity

In every disaster recovery plan for SQL Server it should have specified the key goals. The goals that should be recorded are the Recovery Point Objective and Recovery Time Objective.

The Recovery Point Objective (RPO) is the amount of acceptable data loss specified within your contractual obligations.

The Recovery Time Objective (RTO) is the amount of time the service can be unavailable after a disaster.

Example

RPO - 5 minutes of data loss only
RTO - 1 hour before service needs to be restored

Backup Process

A backup and recovery process should be put in place to backup the databases and backups should be regularly restored to test the validity of the backup process.

The Database is Shutdown

When the databse state is showing as shutdown some troubleshooting steps are required.

To identify the database status run:-

USE master
SELECT databaseproperty(‘databasename’,'isShutdown’)

Clear the database status:-

USE master
ALTER DATABASE databasename SET offline

Bring the database back online:-

USE master
ALTER DATABASE databasename SET online

Check database

DBCC CHECKDB

Other things to check

• Is Autogrow enabled?
• Is their enough filespace?
• Is their sufficient memory and disk space?
• Are the physical files marked as read only?
  

SQLBits - The 7 Wonders of SQL

On arriving at sqlbits I was greeted by a friendly duck enjoying what was left of the rain. The community day was full of amazing speakers with great enthusiasm for the field. Some of the themes covered Virtualisation , SAN Storage, NoSQL (Not Only SQL) eventually consistent databases, SQL Azure and the cloud, Data as a strategy, geospatial reporting, Master Data Management, the SQLCAT etc. etc. It was great to be able to share experiences in the field with like minded individuals. I look forward with anticipation, to the next event.

Microsoft SQL Server 2008 Service Pack 2 is released

Microsoft SQL Server 2008 Service Pack 2 is released and can be downloaded

Service Pack 2 contains updates for

SQL Server Utility
Data-Tier Application (DAC)
Integration capability for Microsoft Reporting Services with the Microsoft SharePoint 2010 Technologies
Support for a maximum of 15,000 partitions in a database
It includes SQL Server 2008 SP1 Cumulative Update 1 to 8

SQL Server 2008 R2 Express

The new version is available with graphical management tools, reporting capabilities , full text search and spatial support. It has 10GB of data storage andis compatible with the cloud SQL Azure Database service. The Database with Advance Services contains the most features and can be downloaded here.

Installation notes on how to Install SQL Server 2008 R2 (Express Edition) can be found here.

Current Trends

The current trends for databases are

Virtualization of the SQL Stack
Cloud based databases
NoSQL databases
Business Intelligence
Data Archiving

Microsoft SQL Server2008 R2 Best Practices Analyzer

SQL Server 2008 R2 Best Practices Analyzer has been released for SQL Server 2008 and SQL Server 2008 R2. It is a diagnostic tool and can be downloaded here.

2008 R2 Compatibility

It is not possible to restore a SQL Server 2008 R2 database on SQL Server 2008 due to the physical version numbers being incompatabile. You can run DBCC DBINFO to read the database header page to collect the actual information:

DBCC TRACEON (3604);
GO
DBCC DBINFO
GO
DBCC TRACEOFF (3604);

dbi_version = 661

A summary of versions

* SQL Server 7.0 databases have version number 515
* SQL Server 2000 databases have version number 539
* SQL Server 2005 databases have version number 611/612
* SQL Server 2008 databases have version number 655
* SQL Server 2008 R2 databases have version number 660

An excellant post The Rambling DBA: Jonathan Kehayias
and Paul Randal
explain this.

SQLBits

The next SQLBits conference, SQLBits 7: The Seven Wonders of SQL, will be taking place from September 30th to October 2nd at York University.

Database Management Methods

I am currently researching what methodologies, tools or processes are currently used in the day to day management of SQL Servers.

Can you help with my survey on Database Management Method on bit.ly/aMEqbW

Thank you helping with the research

SQL Server 2008 R2

SQL Server 2008 R2 was released to Released to Manufacturing on 21 April 2010.

The official press release summarizes the main benefits as:
1. Managed self-service business intelligence (BI) for reporting and analysis
2. Enterprise-class scalability and greater IT efficiency
3. Platform integration spanning the data centre to the cloud

SQL Server Codenames

I was interested to see what all the SQL codenames were

Sphinx - SQL Server 7.0
Plato - SQL Server 7.0 OLAP Services
Shiloh - SQL Server 2000
Liberty - SQL Server 2000 (64-bit)
Rosetta - SQL Server Reporting Services
Yukon - SQL Server 2005
Katmai - SQL Server 2008
Kilimanjaro - SQL Server 2008 R2
Madison - Microsoft SQL Server 2008 R2 Parallel Data Warehouse
Bulldog - SQL Server 2008 R2 Master Data Services (Master Data Management)

Database Mail Catalog views

Database Mail Catalog views display metadata about the status of e-mail messages processed by Database Mail.

USE MSDB
select * from sysmail_allitems

Contains a row for each attachment submitted to Database Mail.

USE MSDB
select * from sysmail_mailattachments

Contains returned message details for Windows or SQL Server

USE MSDB
select * from sysmail_event_log

Contains a row for each message sent successfully

USE MSDB
select * from sysmail_sentitems

Contains one row for each failed message

USE MSDB
select * from sysmail_faileditems

Contains row for Messages with unsent or retrying status which are still in the mail queue

USE MSDB
select * from sysmail_unsentitems

SQL Server Version Numbering

This defines the product version numbering format for SQL Server. It is MM.nn.bbbb.rr which is defined as:
MM - Major version
nn - Minor version
bbbb - Build number
rr - Build revision number

Taxonomy

Taxonomy is the science of classification. It comes from the Greek taxis meaning arrangement or division and nomos meaning law. It provides a conceptual framework for discussion, analysis, or information retrieval. It arranges hierarchically, elements of a group (taxon) into subgroups (taxa) that are mutually exclusive. Each group has the same properties, behaviours, and constraints. And each increasing level has further properties, behaviours, and constraints.

In biological terms this would be the classification of species.

Fragmentation

Fragmentation is the storing of data non-contiguously on disk.

Detecting Fragmentation

The level of fragmentation can be determined by running the Dynamic Management View (DMV): sys.dm_db_index_physical_stats.
To return information for all databases detailing all statistics for all tables and indexes within the instance of SQL Server run

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);

A couple of the DMV columns are defined below

avg_fragmentation_in_percent: Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.. The percent of logical fragmentation (out-of-order pages in the index). The lower this value, the better it is. If this value is higher than 10%, some corrective action should be taken.

avg_page_space_used_in_percent : Average percentage of available data storage space used in all pages. Higher the value, the better it is. A value is lower than 75%, some corrective action should be taken.

Reducing Fragmentation

After the degree of fragmentation is identified from sys.dm_db_index_physical_stats, a guideline on the course of action to be taken is listed below

avg fragmentation in percent	Suggested Action
> 5%	No action required
> 5% and < = 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON) can be executed online or offline. A replacement for DBCC DBREINDEX