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.
