On December 5th, I will be presenting a Microsoft Technet Evening on High Availability in SQL Server.
Session Abstract: SQL Server 2005 is designed to reduce both planned and unplanned downtime, provide solutions for disaster recovery, and provide greater system availability to database users through High Availability technologies. In this presentation Frederik will discuss various features (including Failover Clustering, Database Mirroring, Log Shipping, and Peer-to-Peer Replication) of SQL Server which help you meet your 99.999% uptime SLAs.
To finish off, we will have a sneak preview on the new high availability features of the upcoming SQL Server 2008.
Frederik Vandeputte is a Senior Consultant and trainer for Kohera, the Microsoft SQL Server/Business Intelligence Competence Center of the Cronos Group. Frederik is the Vice-President and co-founder of the Belgian SQL Server User Group (SQLUG.BE).
On November, 22nd we are organizing our next SQLUG.BE event. Marc Martens will talk about Reporting Services.
Reporting Services is Microsoft's server based reporting solution that is delivered together with SQL Server 2005. In this session you will learn how to create a report, deploy and manage it. Also covered is how you can use Reporting Services to control the load on your source system when running a report. If there is time left we will shown how to write custom extensions for your reports.
Prepare yourself for a long night. First we have the session, afterwards we are offered a guided tour in "Huis van de Toekomst". Doors will be closed far after 22:00 hour.
The starting hour will be 18:30, doors open from 18:00 hour.
The event will take place in Huis van de Toekomst
Indringingsweg 1 B-1800 Vilvoorde
Registration is needed and seats limited, so hurry up. You can register here.
This improvement adds a new time series forecasting algorithm (ARIMA: Auto Regressive Integrated Moving Average) to the data mining algorithm suite that provides more stable long term predictions.
Enterprise Reporting Engine
Improvements represent the two major infrastructure changes for Reporting Services. Reporting Services enhances the processing engine and rendering extensions to enable new functionality, such as Tablix support, and scalability as well as remove the dependency on IIS. Additionally, new report designer and configuration tool are provided that improve usability and workflow for RS customers.
T-SQL Improvements
Object Dependencies: The object dependencies improvement provides reliable discovery of dependencies between objects through newly introduced catalog view and dynamic management functions. Dependency information is always up-to-date for both schema-bound and non-schema-bound objects. Dependencies are tracked for stored procedures, tables, views, functions, triggers, user-defined types, XML schema-collections, and more.
Database Mirroring Enhancements
SQL Server 2008 builds upon the momentum of SQL Server 2005 by providing a more reliable platform with enhanced database mirroring:
Automatic bad page repair – allows the principal and mirror machines to transparently recover from 823/824 types of data page errors by requesting a fresh copy of the corrupted page from the mirroring partner.
Log stream compression – compression of the outgoing log stream in order to minimize the network bandwidth used by database mirroring.
Miscellaneous performance enhancements:
using asynchronous log write requests on the mirror in order to shorten the log write time and thus speed-up the commit acknowledgement.
better utilization of the mirroring log send buffers in order to pack multiple smaller log blocks into a single network send.
Supportability and diagnosability improvements:
additional performance counters to allow for more granular accounting of the time spent across the different stages of the DBM log processing.
new DMVs and extensions of existing views in order to expose additional information about the mirroring sessions.
Extended Events
SQL Server Extended Events is a general event-handling system for server systems. The Extended Events infrastructure supports the correlation of data from SQL Server, and under certain conditions, the correlation of data from the operating system and database applications. In the latter case, Extended Events output must be directed to Event Tracing for Windows (ETW) in order to correlate the event data with operating system or application event data.
Performance Data Collection
Collect data from various sources in SQL Server and OS to help with performance troubleshooting and server maintenance. With this improvement, organizations improve their analysis of common performance issues:
Define what data is collected and organize the collection into collection sets
Start/stop/manipulate collection sets programmatically (T-SQL and .NET API)
Define where data is stored (relational database)
View data through reports in SQL Server Management Studio.
Provide platform to plug in more data collectors in the future.
Improved XML Support
To leverages the new date and time types, SQL Server’s XML Schema collection now provides full support for the xs:date, xs:time and xs:dateTime data types. Support for union types is also enhanced by returning correct results for “instance of” queries when union types are involved, and adding support for lists of unions and unions of lists constructs in XML Schemas.
DATE/TIME Data Types
SQL Server 2008 introduces new date and time data types. The new data types enable applications to have separate date and time types, larger year ranges for date value, larger fractional seconds precision for time value, time-zone offset aware datetime type that containing date, time and time zone offset portion, user defined option on fractional seconds precision of time related types and datetime2 and datetimeoffset provide standards conformant semantics. Along with the T-SQL support on the new types, both native (ODBC, OLEDB) and managed (SqlClient) providers also provide the full support through the client driver APIs.
Large User-Defined Types Improvement
Large user-defined types allows users to expand the size of defined data types by eliminating the 8‑KB limit.
ORDPATH Improvement
ORDPATH improvement provides an important new functionality to our customers who use hierarchical data. It provides a superior way of modeling hierarchies in SQL Server by introducing the HierarchyID system data type and corresponding built-in methods which are designed to make it easier to store, query and operate hierarchical data. HierarchyID is also optimized for representing trees, the most common type of hierarchical data.
BIDS Helper is a set of Visual Studio.Net add-ins that extend and enhance the functionality of the SQL Server BI Development Studio. The current release (1.1.0.1) containts the following add-ins:
I really like the "Deploy MDX Script" add-in. This feature allows you to right click on a cube in an Analysis Services solution and deploy just the calculation script. This comes in very handing while debugging and testing MDX calculations in offline mode.
It's a open source and available as a free download on CodePlex. Toeltje van de week!!!
Rumor has it that a new CTP of SQL Server 2008 is on its way. A recent article on the MCP Magazine mentions the planned +/-60 days release schedule between CTPs. CTP3 aka the June CTP was released on June, 4. The June CTP was nice but lacks a lot of the announced features. So ... looking forward to the new stuff to spend those rainy Belgium summer evenings ;-)
If you wanna get a good overview of the new stuff, check out these resources:
If you have about 1 to 2 hrs to spend: the free online course on SQL Server 2008. It's really good, and for the moment, the best and most (public available) in depth overview available.
On September, 27th we are organizing our next SQLUG.BE event. The event - an introduction to Analysis Services 2005, will take place at the Kohera Office and will be presented by my colleague Nico Verbaenen.
Full details and free registration can be found on here.
Just discovered a great tool via Kalen Delaney's Blog. This tool gives you a low level graphical view on how data is stored in a database. Toeltje van de week!!!
SQL Internals Viewer is a tool for looking into the SQL Server storage engine and seeing how data is physically allocated, organised and stored.
All sorts of tasks performed by a DBA or developer can benefit greatly from knowledge of what the storage engine is doing and how it works. This tool has been designed to make database internals more accessible.
The Quick Start section gives a guide on how to use SQL Internals Viewer.
The Resources section lists articles and books with information on SQL Server internals.
I really like the ribbon in Office 2007. I think it is a great improvement over the old menus. However, every now and then I get lost in the ribbon and can't seem to find that very nice little menu option. Microsoft released an excellent set of Excel workbooks. The Excel sheets list every single menu option in the old Office applications and their corresponding place in the Office 2007 ribbon.
At it's annual Worldwide Partner Conference yesterday, Microsoft announced the release dates for it's next trio of products: Windows 2008, SQL Server 2008 and Visual Studio 2008. On February 27, 2008, Microsoft will launch these products in Los Angeles with a wave of kickoff events around the world to follow.
Bart Bultinck, fellow at SQLUG.BE, and some other top bloggers in the Belgian Blogsphere started the excellent blog site TryCatch.be. Chummy, Kurt Roggen, Peter De Tender, Tom Vandaele, any many more to come are blogging now on this must have RSS feed.
Microsoft released a new version of the SQL Server 2005 Best Practices Analyzer (July 2007). "CTP" was dropped from the name, so we are RTM now, I guess.
The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.
Title: Clinic 7045: What's New in Microsoft® SQL Server™ 2008
Course Type: Self-paced Course
Available Offline: Yes
Estimated Time of Completion: 2 Hours
Language: English
Description: In this clinic, you will learn about the new and enhanced features included in SQL Server 2008. You will explore the new data types and the data management features. Additionally, you will learn about the enhanced Integration Services, Analysis Services, and Reporting Services included in SQL Server 2008. This online clinic is composed of a rich multimedia experience.
To get the most out of this clinic, it is recommended that you have:
Knowledge of general SQL database concepts that are largely independent of a specific version of SQL Server.
Knowledge of Microsoft SQL Server 2005 features.
Knowledge of deploying and upgrading database solutions.
Knowledge of how to solve performance issues related to SQL Server hardware.
Performed the job role of a SQL Server 2005 database administrator or database developer.
Product or technology experience in Microsoft SQL Server 2000 or SQL Server 2005.
Objectives: At the end of the course, students will be able to:
Describe the features of SQL Server 2008 and their benefits.
Describe the features of enterprise data platform that help you to secure data in applications.
Describe the dynamic development features that facilitate the development of database applications.
Describe the features of SQL Server 2008 that provide data storage solutions beyond relational databases.
Describe the enhanced features in SSIS that help you to integrate data effectively.
Describe the enhanced features in SSAS that help you to improve the BI infrastructure.
Describe the enhanced features in SSRS that help you to improve the scalability of the reporting engine.
On Thursday I will be presenting on the first Belgian Community Day.
Five belgian usergroups, BIWUG, VISUG, SQLUG, IT-Talks, and Pro-Exchange, have decided to combine their efforts to organize a joint-event just before summer starts. This event will be the biggest community event in Belgium! So if you are eager to learn, eager to get answers to your questions and eager to socialize, come and join us for our very first Belgian Community Day.
I will deliver a fun introduction to Microsoft BI. My collegue Werner Geuens will give an in depth session on statistics.
Intro Session - biTunes – A fun introduction to Microsoft BI with SQL Server 2005 and… iTunes !
Session abstract:
SQL Servers 2005 gives you all the tools you need for building end-to-end business intelligence (BI) applications. In this demo driven session we walk through the complete SQL Server 2005 BI stack. We'll use SQL Server Integration Services to import the iTunes (yes, that's right) music library. We'll build an OLAP cube on top of our MP3 library with SQL Server Analysis Services. Finally we'll use SQL Server Reporting Services and Excel 2007 to reveal information you thought you could never retrieve from iTunes. Come take a walk on the BI-side…
Speaker: Frederik Vandeputte, Senior Consultant at Kohera and Vice President of the Belgium SQL Server User Group
Deep dive: Statistics in SQL Server 2005, what can go wrong
Session abstract: Maybe you are not aware of it but every index is accompanied by its statistics. You can create statistics without index, but why would you do that? Even the engine creates statistics when it feels like doing so, how can that be of use to us? We have new database settings concerning statistics in SQL Server 2005; let's take a look at these. Once you know the use of statistics you'll realize that they need maintenance, how should we do that?
Speaker: Werner Geuens, Senior Consultant at Cronos.
This nice KB article lists the Microsoft SQL Server 2005 builds that were released after Microsoft SQL Server 2005 Service Pack 2 (SP2) was released. http://support.microsoft.com/kb/937137
In the SQL Server 2005 the BACKUP LOG WITH TRUNCATE_ONLY statement was marked depricated, but still worked. In SQL Server 2008 it returns an error.
BACKUP LOG testtrunc WITH TRUNCATE_ONLY
Msg 155, Level 15, State 1, Line 3 'TRUNCATE_ONLY' is not a recognized BACKUP option.
So how can we truncate the logfile in SQL Server 2008? Well, just switch to SIMPLE RECOVERY and back to FULL.
ALTER DATABASE testtrunc SET RECOVERY SIMPLE ALTER DATABASE testtrunc SET RECOVERY FULL
Be carefull, don't run this on an production database. You are erasing the transaction log file and will loose the possibility to do point in time restores.
So what's is the MERGE statement all about? Well basically it's doing an INSERT, UPDATE and DELETE in a single statement. It used to be part of the early betas of SQL Server 2005 but didn't make in the RTM version. And now it's back :-)
Here's a little example:
MERGE Departments AS d USING Departments_delta AS dd ON (d.DeptID = dd.DeptID) WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName WHEN NOT MATCHED THEN INSERT (DeptID, DeptName, Manager) VALUES (dd.DeptID, dd.DeptName, dd.Manager) WHEN SOURCE NOT MATCHED THEN DELETE;
Departments_delta contains updated Department information. Based on the primary key we decide wether to insert, delete, or update the Department table.
Five belgian usergroups, BIWUG, VISUG, SQLUG, IT-Talks, and Pro-Exchange, have decided to combine their efforts to organize a joint-event just before summer starts. This event will be the biggest community event in Belgium! So if you are eager to learn, eager to get answers to your questions and eager to socialize, come and join us for our very first Belgian Community Day.
The event will take place at the premises of SemCom in Keerbergen on Thursday, the 28th of June 2007. Free registration and full details can be found on communityday.be
Due to some technical problems with Blogger and SFTP issues I dind't blog very much recently. The problems are now fixed, so we are ready to go again :-)
Microsoft released the first public CTP of Katmai yesterday ... so we have new stuff to play with and to blog about :-)
Today at the first Microsoft Business Intelligence (BI) Conference, Microsoft Corp. presented its vision for the next version of its flagship data management and analysis platform. SQL Server™ code-named “Katmai,” the next release of Microsoft® SQL Server.
Microsoft released yet another cummulative patch for SP2 . After applying this patch you will be running on build 3161. Full details and downloads are available here.
With the use of mobile devices becoming more and more mainstream, it is likely that we will see an increased need to integrate the data stored on these devices with other databases. Replicating data from and to mobile devices is probably one of the more technically challenging parts of mobile database development and management. On Thursday, April 19th Erik Bollen will be walking us through replication with SQL Server Mobile and point out some of the common pitfalls.
This event will take place in the offices of Info Shelf in Asse-Zellik.
Still running on SQL Server 2000? Time to move on!! Exactly over 365 days Microsoft will end the main stream support for SQL Server 2000. Extended (payed) support stops in 2013. Check out SQL Server 2000 Support Lifecycle.
How many times have you been watching the progress bar during a SQL Server Backup or restore operation? And how times have you answered "Wel, I guess ... " when people asked you "Hey mister DBA, how much more time will it take before the restore has completed?".
I have been doing quite some backup/restore operations during recent upgrading and migration projects. The sys.dm_exec_requests DMV contains one row for every running command on a SQL Server instance. Two interesting columns (estimated_completion_time, percent_complete) help you answering the "when" and "how much longer" questions.
The SELECT statement below will give you a time estimation for every running "BACKUP DATABASE" or "RESTORE DATABASE" statement. Play around with the WHERE clause to monitor other long running operations. /* Created by free online sql formatter */
Last Thursday KHMMechelen organized an event on Business Intelligence. Together with 5 other colleagues from the Cronos Group we gave 6 presentations. This was the agenda:
Positioning of BI tools in the market – Bart Van Mulders
The Buzz Words – Nick Wuyts
Skills required for a successful BI project – Bart Gorremans
Competitive comparison of some important Business Intelligence Suites
BusinessObjects – JornVandendriessche
Microsoft Business Intelligence – Frederik Vandeputte
Oracle BI – StijnVandereycken
Given the audience of about 100 (ex) students I decided to do a rerun of my biTunes presentation. I got pretty positive feedback from a lot students, Thanks for that. Here's a wrap up of the Q&A:
What's that nifty tool you used to zoom in and out? It's called ZoomIt and is available as a free download from the MS Sysinternals website.
Can I download the biTunes source code? Yes, I posted it here.
Why is the Undo feature missing from Integration Services. This is known issue, and feature request on many people's which list.
Can you load the iTunes Library with plain T-SQL code? Yes, you can check out Peter Debetta's post.
Cronos, Datamotive, I4BI, Kohera ... Who's Who?
Datamotive is the BI competence center in the Cronos Group using tools like BO, Cognos ...
I4BI is the competence center with full focus on Oracle BI
Kohera is dedicated to implementing Microsoft's Business Intelligence suite and delivering services on the MS SQL Server platform.
We really liked the event, and are planning more BI roadshows comparing Microsoft BI with other vendors. So, if you are interested ... drop me a line.
SQL Server 2005 SP2 contains quite a few interesting new features. A few months ago I blogged about my old time feature requests: logon triggers. Another nice feature in SP2 are the Custom Reports in SQL Server Management Studio which allow you to add your own Reporting Services reports to the Management Studio.
Just right click anywhere in the Object Explorer, Choose Custom Reports and point to your favourite .RDL file. From now on this report will be just a mouse click away.
So, adding reports is easy, but how about removing them? All data about custom reports is stored in the a file called reports.xml which resides well hidden in your profile folder (%userprofile%\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\reports.xml).
If you want to remove a report from the Context menu follow these steps:
Close all running SQL Server Management Studios
Open %UserProfile%\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\reports.xml
Remove the corresponding section from the XML
Save reports.xml
Restart SQL Server Management Studio
The report should be gone
By default the Management Studio gives a warning message when you start a custom report. You can suppress these warnings via the GUI. However if want to unsuppress these warnings you need to edit the same reports.xml file. Check out : "How to: Unsuppress Run Custom Report Warnings" from Books Online.
One more note: Microsoft recently released a set of reports called "SQL Server 2005 Performance Dashboard Reports". They are pretty cool and can be download here.
Microsoft quietly released an updated version of SQL Server 2005 SP2. The new build number is 9.00.3042.01. The previous build number was 9.00.3042.00. The update corrects an issue with the cleanup task in maintenance plans. Here is the download link for SQL Server 2005 SP2.
The Belgian Developer and IT Pro days are comming soon. If you want to take advantage of the early bird offer, hurry up, because the offer expires tomorrow.
There are quite some interesting SQL Server/BI presentations scheduled on Wednesday and Thursday. Jake Zborowski and Ashvini Sharma from Microsoft Corp will be delivering a complete BI track on Wednesday. On Thursday Gunter Staes from MS Belgium will give a session entitled "MOSS 2007 Configuration of Excel Services, SSO & Data Conections - The Mac Guyver Techniques !". Also on Thursday, one my favourite SQL Server authors and speakers, Bob Beauchemin will give 4 SQL Server sessions. Check out the full agenda.
Keep an eye on the SQLUG.BE homepage because we are working on something special for our members.
CTPs of Service Pack 2 for SQL Server have been around for a little while now. Rumor has it that SP2 will finally be released on Monday. That's a nice coincidence, since on Thursday we are celebrating the first anniversary of the Belgian SQL Server User Group . Gunter Staes, Partner Technology & Solution Advisor at Microsoft will highlight the top features of SP2. We will conclude the evening with a nice buffet. We still have a few seats available, so if you wanna join us, click here.
[UPDATE 19-FEB-06] SP2 is indeed released and can be downloaded here.
Microsoft published a preliminary agenda. On March 28th Jake Zborowski and Grant Dickinson from Microsoft Corp will present a full track on SQL Server Business Intelligence.
Keep an eye on the Dev IT Pro Days website because there are more SQL Server related topics comming soon ...
Table Analysis Tools for Excel: This add-in provides you with easy-to-use tasks that leverage SQL Server 2005 Data Mining under the covers to perform powerful analytics on your spreadsheet data.
Data Mining Client for Excel: This add-in allows you to go through the full data mining model development lifecycle within Excel 2007 using either your spreadsheet data or external data accessible through your SQL Server 2005 Analysis Services instance.
Data Mining Templates for Visio: This add-in allows you to render and share your mining models as annotatable Visio 2007 drawings."
SQL Server Reporting Services Add-in for SharePoint Technologies CTP Microsoft SQL Server 2005 Reporting Services Add-in for SharePoint Technologies (Reporting Services Add-in) allows you to take advantage of SQL Server 2005 Service Pack 2 (SP2) report processing and management capabilities within Windows SharePoint Services 3.0 or Microsoft Office SharePoint Server 2007. The download provides the following functionality
SQL Server Database Publishing Wizard 1.0 RC SQL Server Database Publishing Wizard enables the deployment of SQL Server databases into a hosted environment on either a SQL Server 2000 or 2005 server. It generates a single SQL script file which can be used to recreate a database (both schema and data) in a shared hosting environment where the only connectivity to a server is through a web-based control panel with a script execution window. If supported by the hosting service provider, the Database Publishing Wizard can also directly upload databases to servers located at the shared hosting provider."
And there is even more .... A full list of recent SQL related downloads can be found here.
One of the scenarios I showed during my presentation on upgrading to SQL Sever 2005 was "The Cowboy Way of upgrading". Here's the situation. A SQL 2000 database containing a user called sys, cannot be upgraded to SQL Server 2005. If you run the Upgrade Advisor, you will get a warning on this show stopper. So, the best practice here is to rename the user on SQL Server 2000 and afterwards upgrade to SQL 2005.
In "The Cowboy Way of upgrading" I restored the SQL Server 2000 database with the sys user on my SQL 2005. The restore generated an error because of the sys users and the database was marked suspect.
Remember that is definitely not the best way of upgrading. But since quite a few people asked about the script I decided to post it. Have fun but be careful.
Connecting to localhost\sql2005... 15 percent processed. 23 percent processed. 30 percent processed. 46 percent processed. 53 percent processed. 61 percent processed. 76 percent processed. 84 percent processed. 92 percent processed. 100 percent processed. Processed 104 pages for database 'sys_database', file 'sys_database_Data' on file 1. Processed 1 pages for database 'sys_database', file 'sys_database_Log' on file 1. Converting database 'sys_database' from version 539 to the current version 611. Database 'sys_database' running the upgrade step from version 539 to version 551. Warning: User "sys" (principal_id = 5) in database "sys_database" has been renamed to "_ID_0x00000005_RENAMED_FROM_sys". "sys" is a reserved user or schema name in this version of SQL Server. Warning: Database "sys_database" was marked suspect because of actions taken during upgrade. See errorlog or eventlog for more information. Use ALTER DATABASE to bring the database online. The database will come online in restricted_user state. Database 'sys_database' running the upgrade step from version 551 to version 552. Database 'sys_database' running the upgrade step from version 552 to version 553. Database 'sys_database' running the upgrade step from version 553 to version 554. Database 'sys_database' running the upgrade step from version 554 to version 589. Database 'sys_database' running the upgrade step from version 589 to version 590. Database 'sys_database' running the upgrade step from version 590 to version 593. Database 'sys_database' running the upgrade step from version 593 to version 597. Database 'sys_database' running the upgrade step from version 597 to version 604. Database 'sys_database' running the upgrade step from version 604 to version 605. Database 'sys_database' running the upgrade step from version 605 to version 606. Database 'sys_database' running the upgrade step from version 606 to version 607. Database 'sys_database' running the upgrade step from version 607 to version 608. Database 'sys_database' running the upgrade step from version 608 to version 609. Database 'sys_database' running the upgrade step from version 609 to version 610. Database 'sys_database' running the upgrade step from version 610 to version 611. Msg 3167, Level 16, State 3, Line 1 RESTORE could not start database 'sys_database'. Msg 3167, Level 16, State 1, Line 1 RESTORE could not start database 'sys_database'. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
Here's how I fixed this one. First of all I forced the database online (ALTER DATABASE sys_database SET ONLINE). SQL Server will put the database in single user. In the next step I checked the system catalogs to find out that SQL Server created a new schema called _ID_0x00000005_RENAMED_FROM_sys. My table was moved from the sys schema to this new schema.
In the next step I created a new schema and used ALTER SCHEMA to move the table from _ID_0x00000005_RENAMED_FROM_sys to my new schema.
CREATE SCHEMA system
ALTER SCHEMA system TRANSFER _ID_0x00000005_RENAMED_FROM_sys.myTable
In the last step I put the database in multi user mode.
ALTER DATABASE sys_database SET MULTI_USER
Here's the full script.
USE master
--Force online ALTER DATABASE sys_database SET ONLINE
--Now in single user, show object explorer first USE sys_database
SELECT name, SCHEMA_NAME(schema_id) as schema_name, * FROM sys.objects WHERE NAME = 'myTable'
SELECT * FROM sys.schemas
SELECT * FROM sys.database_principals
SELECT * FROM sys.server_principals
CREATE SCHEMA system
ALTER SCHEMA system TRANSFER _ID_0x00000005_RENAMED_FROM_sys.myTable
SELECT * FROM system.myTable
INSERT INTO system.mYTable (col1) VALUES ('Frederik')
Microsoft Belgium launched an Upgrade to SQL Server 2005 campaign. One of the cornerstones of the campaign is the brand new website www.sqlserverupgrade.be. The website is loaded with links to documents, webcast, hand-on labs and much more.
On December, 6th I will be presenting a TechNet Evening on Upgrading to SQL Server 2005. I will demonstrate different upgrade scenarios and show you the pros and cons of each upgrade path. I will pinpoint potential pitfalls and show you the tools which can help you during the upgrade process.
Full details and free registration can be found here.
As a Sinterklaas present all attendees will receive a copy of the SQL Server Upgrade Resource kit for free.
In April I wrote an article entitled "SQL Server 2005 logon triggers". The article will be published in the forthcomming book "The best of SQL Server Central - Volume 4".
In the article I described how to mimic Oracle logon triggers using event notifications in SQL Server 2005.
That was part one. Now comes part 2. Last week Microsoft released the CTP of SQL Server 2005 SP2. Here's a list of all new features in SP2. And one of them is logon triggers. Great, finally we have them.
So can we do everything with logon triggers? No, there are some differences with the method I descibed in April using event notifications.
Logon triggers are part of the "logon transaction", this means you can rollback or cancel a logon. Event notifications go on the queue, after the logon. So, no easy way to cancel a logon.
triggers are raised synchronously with events, whereas event notifications are asynchronous.
Logon triggers only fire if authentication succeeds, event notifications can also capture failed logons and logouts.
Anyway it is a nice new feature. I rewrote my logon trigger and it's now a lot easier to understand (keeping in mind the differences mentioned above). You can find the script at the end of this post.
Ever wanted to prevent those Excel or Access users from reading your SQL tables directly? Well these 7 lines of codes will do the trick:
CREATE TRIGGER logon_trigger_not_from_excel ON ALL SERVER FOR LOGON AS BEGIN IF APP_NAME() LIKE '%excel%' ROLLBACK END
Here's a complete sample script:
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF -- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO -- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A -- PARTICULAR PURPOSE. -- Frederik Vandeputte - www.vandeputte.org
--TESTED ON SQL 2005 SP2 CTP
USE MASTER GO --Clean up old logging db IF DB_ID ('Logging_demoSP2') IS NOT NULL BEGIN ALTER DATABASE Logging_demoSP2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE Logging_demoSP2 END GO --Create Logging DB and table CREATE DATABASE Logging_demoSP2 GO
USE Logging_demoSP2
CREATE TABLE Logging (
LoggingID int IDENTITY(1,1), EventTime DATETIME, EventType VARCHAR(100), LoginName VARCHAR(100), HostName VARCHAR(100), AppName VARCHAR(255), FullLog XML ) GO
--The LOGON Trigger CREATE TRIGGER logon_trigger ON ALL SERVER FOR LOGON AS BEGIN
DECLARE @message_body XML
SET @message_body = eventdata()
INSERT INTO Logging_demoSP2.dbo.Logging ( EventTime, EventType, LoginName, HostName, AppName, FullLog ) VALUES ( CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME), CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)), CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)), CAST(@message_body.query('/EVENT_INSTANCE/ClientHost/text()') AS VARCHAR(100)), APP_NAME(), @message_body)
END
GO
--Open demo connections
select * from logging
--Prevent acces from Excel CREATE TRIGGER logon_trigger_not_from_excel ON ALL SERVER FOR LOGON AS BEGIN IF APP_NAME() LIKE '%excel%' ROLLBACK END
--Try to connect from Excel
--Clean DB USE MASTER GO ALTER DATABASE Logging_demoSP2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE Logging_demoSP2 DROP TRIGGER logon_trigger ON ALL SERVER DROP TRIGGER logon_trigger_not_from_excel ON ALL SERVER
This zip files contains all the scripts, database backup, and BIDS solution for running your own biTunes BI solution.
Scripts were created with SQL Server 2005 SP1.
I included a small sample iTunes music library file. Copy your own iTunes Music Library.xml file to c:\biTunes\iTunes Music Library.xml to analyze your own MP3 collection.
During the dry-runs of my biTunes presentation people often asked me why iTunes? iTunes happened to be the MP3 player I used on Mac and Windows. So I answered: "It doesn't matter, it's just an MP3 player and it's very similar to Media Player, Winamp or other players".
There is an interesting MSDN evening comming up. Gunther Beersaerts, Senior Technical Specialist @ Microsoft will talk about Data Dude.
November 15, 2006: Introducing Visual Studio Team Edition for Database Professionals
Visual Studio Team Edition for Database Professionals is a database development product designed to manage database change and improve software quality through database testing. Additionally this product brings the benefits of Visual Studio Team System and life cycle development to the database professional. During this session you'll get an overview of this product, including the integration with source code control, the schema and data compare functionalities, rename refactoring, the new T-SQL editor and database testing.
iTunes meets BI!! I will be speaking at Teched : Developers in Barcelona. The session is called "biTunes - Building an end-to-end Business Intelligence (BI) application with SQL Server 2005 and … iTunes!"
This is the abstract.
SQL Servers 2005 gives you all the tools you need for building end-to-end BI applications. In this 100% demo driven session we walk through the complete SQL Server 2005 BI stack. We'll use SQL Server Integration Services to import the iTunes (yes, that's right) music library. We'll build an OLAP cube on top of our MP3 library with SQL Server Analysis Services. Finally we'll use SQL Server Reporting Services and Excel 2007 to reveal information you thought you could never retrieve from iTunes. Come and take a walk on the BI-side...
The session is planned for Wednesday, Nov 8 13:30 - 14:45 (UPDATED) . I am looking forward to meet you there.
I am planning a couple of dry runs during the next weeks. If you would like to attend one of these , drop me an e-mail.
I was playing around with MDX and calculated measures in AS 2005. One of the nice things in AS 2005 is the script view. The Script view gives you all the calculations in a "text editor". This is really great if you need to do bulk changes to your calculated members. E.g. change the format for all uour calculated measures.
Have you tried that in AS 2000? Well I did, it takes some DSO programming Been there, done that during the last weeks.
So I was playing around in the script view, copy/pasting, global replacing ...and all of a sudden my cube was empty... How come? Well here's what I did wrong.
Open the sample solution that ships with SQL Server 2005 Samples (C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Tutorials\Lesson4). Deploy the cube and play arround in the cube Browser. You should see some data.
Now, go to the Calculations tab, and choose the Script view. You will see something like this:
/* The CALCULATE command controls the aggregation of leaf cells in the cube. If the CALCULATE command is deleted or modified, the data within the cube is affected. You should edit this command only if you manually specify how the cube is aggregated. */
CALCULATE;
If you create calculated measures (or other calculations) they will come in here. If you create a new dummy calculated measure the script view might look as follows:
/* The CALCULATE command controls the aggregation of leaf cells in the cube. If the CALCULATE command is deleted or modified, the data within the cube is affected. You should edit this command only if you manually specify how the cube is aggregated. */ CALCULATE;
CREATE MEMBER CURRENTCUBE.[MEASURES].[dummy] AS [Measures].[Reseller Sales-Discount Amount]*2, VISIBLE = 1 ;
After you deployed the cube you will see the dummy calculated measure in the cube browser.
Now let's go back to the Calculations tab and just delete the line "CALCUATE;"
Redeploy, go back to the browser and what will you see: NADA!!
One of the things I learned with Ingres was that "not supported" is not the same as "it does not work".
So, last week I spend some evenings/nights pushing my luck and managed to get Vista and SQL Server 2005 running on an intel based iMac. During those installations I saw quite some freaky eror messages, warnings, aborted installations, and more funny stuff. Which again reminded me of ... ;-)
At the end of the week I had SQL Server 2005 running on Vista on an intel based iMac. It's not supported ... but it seems to work.
Do you like those fancy reports in the SQL Server 2005 management studio? Well if you do, you probably searched your hard disk for the RDL files. Did you find them? I don't think so, neither did I.
It's funny ... Once you go Mac you want to go Vista. I have been following Hans Verbeeck's adventures trying to get Vista running on his Mac Book Pro. Last week Bart finally got OS X running on his standard PC. He posted some screenshots and videos.
Yesterday I installed Vista Build 5536 (Pre RC1) on an Intel iMac. I used the recently updated Boot Camp 1.1 to create a Windows partition. After that the installation was a snap. No more need to delete the EFI partition.
Next steps are the drivers and of course ... SQL Server.
We all know we should use schema_binding and always list all column names in SELECT statements and view definitions. We know we should ... but nobody does it ;-)
Here's al little scripts which demonstrates how funny a SELECT * FROM ...view behaves when you start altering the underlying tables.
Tip of the day ... don't use SELECT * FROM in a view.... ;-)
/* Created by free online sql formatter */ /* --Drop objects if they exists DROP TABLE t1 DROP VIEW v1 */
CREATETABLEt1( xINT, yVARCHAR(5))
INSERTINTOt1 (x, y) VALUES(1, 'abc')
GO
CREATEVIEWv1 AS SELECT* FROMt1 GO
--This shows columns a and b SELECT* FROMv1
--Add a third column to our table ALTERTABLEt1 ADDzDATETIME GO
UPDATEt1 SETz=GETDATE() --Third column is in table SELECT* FROMt1 --Third column is NOT in view SELECT* FROMv1
--Drop column x from table ALTERTABLEt1 DROPCOLUMNx
--Here's the freaky part --Column headers are x and y but data shown is from columns y and z SELECT* FROMv1
Earlier this week I moved my blog to the great guys of Fab4.be.
This move might cause some problems if you were reading my via an RSS reader. Just to put things straigth. These are the correct RSS feeds (both in RSS and ATOM format).
Fact 1: I really like iTunes. It is a cool music player and it's pretty good in managing a complete MP3 library. I am importing all my CDs and LPs and vinyl singles into iTunes on a Mac. I still have some work to do but I am heading towards to an LP and CD free living room.
Fact 2: I want an iPod. I don't have one right now. Here's why. First of all I think they are pretty expensive. Today a 4 GB iPOD nano costs 259 EUR (July, 2006 Belgian Price in Apple Store). A 4GB iPod can hold about 1000 songs. That's 0, 259 EUR per song. In real money that's more than 10 BEF per song.
Second reason ... if I had an iPOD ... when would I use it? Well, at work, at home and in my car. Using the iPod on my MTB or while running (Nike+iPod :-)) would be nice but if I want to cut prices I have to sacrifice some features.
So I came up with the cohPOD. The cohPod only works in my car (c), in the office (o) and at home (h).
The cohPod is available in two editions: a 700 MB model and a 4,7 GB model.
Here's how it works:
Step 1: choose your cohPod model
Pick a bank CD-RW (700 MB) or DVD+/-RW 4,7 GB), take a pen and label it cohPod.
Step 3: Create a cohPod playlist on your main iTunes computer
In my iTunes library on my Mac I created a cohPod playlist. I dragged all the songs I wanted on my cohPod to this playlist. Make sure the total size of the playlist does not surpass the capacity of your cohPod (700 MB/4,7 GB).
Step 3: Burn the cohPod playlist to your CD-ROM
To do this manually, right click on the cohPod playlist and choose Burn Playlist to disk. This method will also work if your main iTunes library is on a PC.
I am working on an Apple Script which will automate this step. However the script did not make it in the RTM version of the cohPod. It might be included in cohPod SP1 or in the long rumoured cohVideoPod.
Step 4: Listen to the cohPod in my car
My Car CD player plays MP3 CDs, unfortunately it does not support DVDs. In other words, the 700MB cohPOD works fine in my car without any cables. The 4,7GB model is currently not supported.
Step 5: Listen to the cohPod at work
My Office PC runs Windows XP (no SQL Server on Mac yet ;-(), so I wrote this little VB-script to import the cohPOD playlist. All I need to do is launch the ImportCohPod script (via Launchy) and I there you go ...
'Change to match your CD drive FolderToAdd = "e:\"
'Set objects Set objApp = CreateObject("iTunes.Application") Set colSources = objApp.Sources Set objLibrary = colSources.ItemByName("Library") Set colPlaylists = objLibrary.Playlists
'Are you sure? answer = MsgBox("Are you sure you want to import " & FolderToAdd & " into you cohPod Playlist?", vbYesNo, "cohPod") If answer = vbNo Then Return End If
'Delete old cohPod Library if exists Set objPlaylist = colPlaylists.ItemByName("cohPod") If Not (objPlaylist is Nothing) Then objPlaylist.Delete End if
'Create new cohPOD playlist Set objPlaylist = objApp.CreatePlaylist("cohPod")
'Add files to cohPod playlist objPlaylist.AddFile(FolderToAdd)
answer = MsgBox(Cstr(objPlaylist.Tracks.Count) & " songs added to your cohPod Playlist (" & CInt(Cstr(objPlaylist.Size/1024/1024)) & " MB)", vbYes, "cohPod")
Conclusion
The 700MB cohPod holds about 175 songs, the 4,7 GB model holds about 1200 songs. A rewritable DVD costs about 1 EUR these days. This means the cohPOD costs less than 0,001 EUR per song. Compared to the 0,259 EUR per song for the 4GB iPod Nano, this is really cheap.
Secondly the cohPod does not require any cables. Not for synchronizing, nor for playing it in the car. Which makes it a true wireless xPod.
Third plus: battery life. Have you ever seen a DVD run out of batteries?
The cohPod doesn't look as flashy as Apple's iPods. It doesn't have all the cool features of the iPod (color screen, Photo's/Video's, multiple playlist, ....). People always talk about features ... but all they do with an iPod is playing songs, clicking next and previous and I can do all this with the cohPod.
Two more pics of the cohPod connected to a Mac and to a PC.
