Teach Yourself Azure in 4 Hours

Learning to use Azure:

1. Create Web Site - Check
   https://manage.windowsazure.com/?whr=live.com#Workspaces/All/dashboard
2. Create Database – Check
   https://manage.windowsazure.com/?whr=live.com#Workspaces/SqlAzureExtension/Databases
3. Upload Project to TFS – Check
   http://tfs.visualstudio.com/
4. Link TFS to Azure Web Site – Check
   http://www.windowsazure.com/en-us/develop/net/common-tasks/publishing-with-tfs/
5. Download Azure SDK for Visual Studio 2010 – Check
   http://www.microsoft.com/en-nz/download/details.aspx?id=15658
6. Download Web Site publication connection settings – Check
   http://www.asp.net/mvc/tutorials/deployment/cse-curated-view-deploy-to-waws
7. Publish Web Site to Azure – Check
   (see above)
8. Create Visual Studio VM – Check
   https://manage.windowsazure.com/?whr=live.com#Workspaces/VirtualMachineExtension/vms
9. Synchronise Local Database Schema to Azure Database - Check.
   http://msdn.microsoft.com/en-us/library/windowsazure/ee730904.aspx
10. Synchronise Local Database Data to Azure - Check.
    http://www.windowsazure.com/en-us/manage/services/sql-databases/getting-started-w-sql-data-sync/

Not easy, but worth it.
Got inspired by Scott Guthrie’s session on Azure yesterday at Tech Ed 2013, Auckland, NZ.

Geek Post Monthly Newsletter Volume 2 Issue 11

Geek Post

Volume 2 Issue 11 November 2010

Hippolite Musings	Tena Koutou Katoa, In November, I: Conquered the Project Management tool which Telecom uses by entering 3 months actuals and 1 months forecast work for the 8 staff in my team.  I’m a trainee Team Lead! Attended another Vex Robotics Scrimmage. Received direction on how to maximise My Personal Performance (MPP) reporting.  This is corporate-speak for complying with my quarterly performance reviews. Fixed up WHEN library database in preparation for handing it over to somebody else.  Yippee! Sat and failed my Windows Communication Foundation exam.  Ah, well, there’s always next month! Studied (before and after exam) materials for said exam. Took final minutes as Secretary for Grenada Village Community Association at AGM.  Presented finances as acting Treasurer. Assisted Fiona to handover Membership Secretary duties of WHEN. Delivered one project.  Picked up three more.  Christmas is always busy (people trying to get stuff finished before the shutdown period). Blessings, James
Community Development	Grenada Village Online My former manager read last months blog; found out I needed training in how to give a logo a transparent background; sent me an email explaining how to do it; and now Grenada Village has a better look and feel.  Here’s how: Downloaded IrfanView. Load the graphic. Save As… Specify JPG Click on background Specify Transparent. Align Church Online Reorganised home page and master page so that Search Widget and Login Widget are both Web Parts in the right pane.  Implemented Top 5 News Items (as per Grenada Village Online). Reduced size of logo. Looks much better. Wellington Home Education Network Handed over Fiona’s Membership Secretary role. Still to hand over my Treasurer’s role. Wellington SQL Server Users Group We had Clive Trott come to show us Virtualisation, Unattended Installs and the Resource Governor. Wellington Dot Net Users Group We had Mark Clearwater from Intergen come to show us XAML, to the UI and Beyond. The Silverlight Group had  Andrew Tokeley from Xero come and show them Making a Great Silverlight Installations experience.
Developer News	Top Stories Show off your Azure skills using New Zealand Digital Content If you want a good incentive to learn more about Windows Azure then check out the "Best use of Windows Azure cloud computing services" category in the Mix 'n Mash competition that opened today. The competition is a chance to show the world what you can do with New Zealand digital content and data. All you need to do for a chance to win $1,000 is to build a mashup hosted on Windows Azure (there are other competition categories as well). PDC On Demand: The Future of C# and Visual Basic Technical fellow Anders Hejlsberg talks about the roadmap for C# and Visual Basic in vNext and beyond. Plus: Asynchrony in C# 5. Part II: Whence await. Part III: Composition. New Release: Visual Studio 2010 Productivity Power Tools A set of extensions to Visual Studio Professional (and above) which improves developer productivity. In this third major release of the Power Tools we have made significant improvements to the Solution Navigator and the Tab Well to address our top customer requests. ASP.NET MVC 3 Release Candidate Backwards compatible with ASP.NET MVC V1 and V2, this release adds a ton of new functionality and refinements.
Training	Complete Conference Session Schedule Now Online! The virtual Microsoft Certified Career Conference is almost here, and it will be packed with valuable information you won’t want to miss! Join highly respected leaders in the industry for technical and career development sessions and network with other professionals during and after the event. Check out the Conference Schedule and Register today. Microsoft Certified Professionals can use the code MCP50 for a 50 percent discount. Add the MCT & Educator Virtual Summit to Your Calendar MCTs and registered academic educators can join us for the MCT & Educator Virtual Summit—a complimentary readiness event on November 17. To make sure you do not miss this event, add it to your Outlook calendar. Certification/Exams - Save up to 20 Percent on Microsoft Certification Exam Packs with Second Shots Get the certification you need to keep your career on track. Find guidance on the training you’ll need, chart your training course, and save up to 20 percent when you purchase Microsoft Certification Packs of two to five exams. Training - MOC/Courseware/Exams/Clinics - New Microsoft Official Courses Mean New OneNote Trainer Packs Eight new OneNote trainer packs are available—bringing the total number to 40. All are available at the MCT Download Center. Check them out today! New IT Fundamentals Course for Windows Server 2008/R2 Microsoft Learning is proud to announce the release of Course 6420B: Fundamentals of Windows Server 2008. This five-day course covers the fundamental concepts and skills of server administration, Active Directory, networking, and security of Windows Server 2008 servers (including Windows Server 2008 R2) for students who are new to IT.
Study News	As per last month, I finished studying Microsoft .NET Framework 3.5 - Windows Communication Foundation.  I booked my MCTS exam 70-503 for Tuesday 9 November at Auldhouse.  I failed.  Now I’m doing the Skill Port on-line training (freebie offering via Telecom).  Will re-sit in the new year. What I have learned from this experience WCF is tricky, real tricky to get right.  And by virtue of its very nature, debugging it is hard, real hard.  For example, I had a real-life example to code.  I had a successful Web Service running inside an existing web site.  So I thought, “here’s a perfect candidate to switch over to WCF.”  Wrong.  I tried and failed for 3 days: First, I discovered that because the DEV & PROD servers are Windows Server 2003 and not 2008, that therefore I can’t use WsHttpBinding but have to use BasicHttpBinding. Then I spend an inordinate amount of time trying to decipher what combination of binding and address (HTTPS was not available to me) was giving me the really unhelpful “Service actively refused connection” error message. Turned out that I had altered my DEV code some time back from System.Oracle.Client to Oracle.Data.Client and the DEV server didn’t have the correct Oracle client installed.  Nowhere did WCF tell me this. I will keep trying.  I will succeed.  My success has merely been delayed.
Telecom News	Project Marley It’s no secret that a strategic decision has been made to bring in-house as much IT work as possible.  This was caused by a number of factors, not least because of the government’s Ultra-Fast Broadband (UFB) initiative will probably force structural separation of the Chorus business off from the rest of Telecom.  This will mean the requirement for some IT resources to be split off separately to Chorus. So, Project Marley is all about offering those HP staff who work exclusively on the Telecom account, to come over to permanent Telecom jobs.  This will be done sensitively, I have no doubt.  It doesn’t directly impact my small team, but what it does impact is that the appetite for increased permanent head count means my manager can now hire just about as many .NET developers as he would like.  So any of my Geek friends, if you’re reading this and desiring a change, let me know and I’ll see if I can arrange you an interview!
Situations Vacant	Lots See my paragraph above.
INETA News	A summary of Significant Dot Net User Group Chatter None - The hamster finally died. Linked-In had this: Scott Guthrie Talks Shop VII Developing testable web parts for SharePoint by Richard Fennel
PASS News	A summary of  Significant SQL Server User Group Chatter Ditto to the above. Linked-In had this: Top 10 Secrets of a SQL Server Expert SQL Server Security Concerns
Events Schedule	In this section, I highlight dates, times and venues of events either I am presenting at, or of interest to Geeks.  I’m available for bookings. Date Event Venue Speaker 1-Dec-2010 LINQ to Async: An Introduction to the Reactive Extensions Xero, Wellington Ivan Towlson 2-Dec-2010 SilverLight Firestarter Redmond or Online Lots 11-Dec-2010 SharePoint Saturday Christchurch Polytechnic Institute of Technology Lots 12-Dec-2010 VEX Robotics Scrimmage Hutt Valley High School N/A 15-Dec-2010 MonoTouch and MonoDroid Xero, Wellington Nic Wise 12-13 Feb 2011 VEX Robotics Regional Competitions St Peter’s College, Palmerston Northq N/A April 2011 ALM Conference Wellington Various
Humour	Web Designers vs. Web Developers An infographic of the differences between them.

Who do you have to kill?

…in order to be nominated for an MVP award?

That was the tongue-in-cheek question I posed to a couple of MVP Leads, here at the Microsoft TechEd conference.

Background

I knew that it was awfully bad taste to go seeking this reward for oneself.  That it should be bestowed upon one by others who have recognized your contribution to the community through voluntary efforts.  However, after not being shoulder tapped (after many years of dedicated service, IMHO), I decided to ask the question.

Problem

Turns out, I’d been too generalist.  Which is natural for a New Zealander, but works against you in this case.  Because I’m both Web Dev and DB Dev, I’d been straddling the two streams, and contributing to both camps.  However, the MVP programme is structured along Product lines, and you must demonstrate in-depth knowledge, skill and dedication in that one area, in order to qualify.

Hence, I don’t qualify.

Solution

Question now is, do I want to bother refocussing on only one area?  Or am I basically happy to continue as I am, knowing that that particular gong is out of reach?

Thoughts and/or opinions?

As a matter of interest, the two areas that strike me as interesting, in which I feel I “had a shot” would be VB and/or SQL BI.

What on Earth have I been up to?

I’ve been busy:

Date	Title	Venue
02-Apr-09	WebCast: Intro to ORM for DBAs	PASS USA
16-Apr-09	What’s New in Microsoft SQL Services	Wellington SQL Users
30-Apr-09	Microsoft Certifications	Auckland SQL Users
14-May-09	How To: Design and Develop Applications to Ensure Quality	VUW
26-May-09	How To: Design and Develop Applications to Ensure Quality	Ellerslie .NET Users
14-Jun-09	Microsoft Sync Framework	NZ .NET Code Camp, Porirua
18-Jun-09	Microsoft Sync Framework	Wellington SQL Users
15-Jul-09	How To: Design and Develop Applications to Ensure Quality	Wellington .NET Users
30-Jul-09	Microsoft Sync Framework	Auckland SQL Users & Ellerslie .NET Users
04-Aug-09	Creation: Why I Believe	Malakai’s Care Group, Wellington Elim
20-Aug-09	How To: Design and Develop Applications to Ensure Quality	Software Quality NZ
13-Sep-09	Microsoft Certifications	NZ .NET Code Camp, Auckland
29-Sep-09	Creation: Why I Believe	Jeannie’s Care Group, Wellington Elim

I’m available for bookings!

Wellington SQL Server User Group Meeting April 2009

I attended a presentation last night:
<>
</>

Title	Wearing multiple hats.  Being a DBA and a developer.
Audience	Wellington SQL Server User Group
Presenter	Dave Dustin – Wolters Kluwer Health Dave is also convenor of the Auckland SQL Server User Group
Brief	Given the size of many New Zealand companies and their respective IT departments, many IT professionals wear multiple hats.  Dave will be giving his view of coming from the development world into the DBA role, and what it means if you need to work in both areas.
Attendees	About 20 – a good number for us
Date/Time	Thursday 16 April @ 5:30pm
Slides	Coming
Impressions	It was unfortunate that Dave’s security key was not present, so that he was unable to start his laptop and show us his slides.  However, he did remarkably well from just memory. I was reminded of the usefulness of Test-Driven Development (TDD) aka “test-first” or “fail-first”: Before you code, write a test which fails. Then write the code that makes the test succeed Then you have a permanent regression test case
Further Comments	He was not able to do the second half of his presentation, which was about Visual Studio Team System 2008: Database Pro edition – GDR.

COALESCE T-SQL Function

I am constantly needing and forgetting the syntax for this function.  Finally, if I put it here, I'll have quick and easy access to it:

SELECT COALESCE(@column1 + ',', '') + column1
FROM Table1 

Exam 70-442 Objective 5

Tested myself on Exam 70-442 (Designing and Optimizing Data Access by Using Microsoft SQL Server 2005) Objective 5 (Performance Tuning a Database and a Database Application) and received only 32%.  I've got a bit of study to do, specifically:

1. Optimize and tune queries for performance
   Creating Partitioned Views
   Table Hints (Transact-SQL)
   SET ANSI_PADDING (Transact-SQL)
   Introducing SQL Trace
   max degree of parallelism Option
2. Optimize indexing strategies
   XML Best Practices
   Planning Guidelines for Partioned Tables and Indexes
   Creating Partitioned Tables and Indexes
3. Scale database applications
   Creating Distributed Partitioned Views
4. Resolve performance problems
   Dynamic Management Views and Functions (Transact-SQL)
   Performance Optimizations for the XML Data Type in SQL Server 2005
   sys.dm_os_wait_stats (Transact-SQL)
5. Optimize data storage
   Data Normalization

Exam 70-442 Objective 4

Tested myself on Exam 70-442 (Designing and Optimizing Data Access by Using Microsoft SQL Server 2005) Objective 4 (Designing a Transaction Strategy) and received 100%.  However, I've still got a bit of study to do, specifically:

1. Manage concurrency by selecting the appropriate transaction isolation levels
2. Design the locking granularity level
3. Design transaction scopes
4. Design code that uses transactions
   Optimization using Single Phase Commit and Promotable Single Phase Notification
   Committing a Transaction in Single-Phase and Multi-Phase
   Enlisting Resources as Participants in a Transaction
   Transaction Management Escalation

Exam 70-442 Objective 3

Tested myself on Exam 70-442 (Designing and Optimizing Data Access by Using Microsoft SQL Server 2005) Objective 3 (Designing Error-Handling Routines) and received only 78%.  I've got a bit of study to do, specifically:

1. Design code that validates input data and permissions
2. Design code that detects and reacts to errors
3. Design user-defined messages to communicate application events
   FETCH (Transact-SQL)

Exam 70-442 Objective 2

Tested myself on Exam 70-442 (Designing and Optimizing Data Access by Using Microsoft SQL Server 2005) Objective 2 (Designing a Database Query Strategy) and received only 60%.  I've got a bit of study to do, specifically:

1. Write and modify queries
   Declarative Security Used with Class and Member Scope
2. Design queries for retrieving data from XML sources
   How To: Use Role Manager in ASP.NET 2.0
3. Design a cursor strategry
   DECLARE CURSOR (Transact-SQL)

Exam 70-442 Objective 1

Tested myself on Exam 70-442 (Designing and Optimizing Data Access by Using Microsoft SQL Server 2005) Objective 1 (Designing Efficient Access to a SQL Server Service) and received only 46.8%.  I've got a bit of study to do, specifically:

1. Design appropriate data access technologies
   ServerConnection Class
   Special Considerations When Using Query Notifications
   Query Notifications in ADO.NET 2.0
   Database Administrator's Guide to SQL Server Database Engine .NET CLR Environment
   Connecting to an Instance of SQL Server
   ADO.NET Architecture
2. Design an appropriate data access object model
   Updating an Application to SQL Server Native Client from MDAC
   Using ADO with SQL Server Native Client
   Using Row Versioning-based Isolation Levels
   Understanding Row Versioning-Based Isolation Levels
   Row Versioning Resource Usage
3. Design a cursor strategy for a data access component
   DECLARE CURSOR (Transact-SQL)
4. Design caching strategies
   ASP.NET Caching Overview
5. Design client libraries to write applications that administer a SQL Server service
   Configuring SQL Server in SMO
   A Technical Comparision of Replication and Remote Data Access Features in SQL Server 2005 Mobile Edition 3.0
   How to: Implement a Business Logic Handler for a Merge Article (Replication Programming)
   How to: Programmatically Monitor Replication (RMO Programming)
   Microsoft.SqlServer.Replication Namespace
   Replication Management Objects Concepts
6. Design queries that use multiple active results sets (MARS)
   Multiple Active Result Sets (MARS) in SQL Server 2005

Introduction to SQL Server Data Services

When	Thursday 19 February @ 5:30pm
Where	Wellington SQL Server User Group, Intergen Offices, Plunket House, Lambton Quay
What	I presented on SQL Server Data Services
Impressions	Was well received. I started with a question; whether the audience would prefer the presentation I'd prepared, or the Microsoft presentation I had received that morning.  They chose my prepared presentation.  I was a little disappointed because the Microsoft presentation had flash graphics, and I couldn't be blamed for being under-prepared!  Nevertheless, there was a fruitful to-and-fro with the audience: Matty was insightful, as usual. Clive was incredulous and unimpressed with the lack of features provided thus far. Adrian asked a pertenant question afterwards, which I'd like to answer here as it's very relevant to the entire discussion.  He was honest about his (and I suspect many people's) confusion about Azure vs. SSDS. Think of Azure as like a Cloud O/S. Think of SSDS as like a Cloud DB. Yes, you could store blobs and entities to Azure, but over time you really shouldn't be, because Microsoft is going to add more functionality to SSDS to enable relational queries, backup, restore, and BI. My slides are here. Microsoft's slides are here.

Exam 70-441

Exam Number	070-441
Title	Designing Database Solutions by Using Microsoft SQL Server 2005
Date	Wednesday, January 14, 2009
Candidate ID	DT1645310
Passing Score	700
Your Score	850
Result	Pass

Sectional Results

1. Designing Database Testing and Code Management Procedures, 86%
2. Designing an Application Solution for SQL Server 2005, 87%
3. Designing Database Objects, 85%
4. Designing a Database, 84%
5. Developing Applications That Use SQL Server Support Services, 84%

T-SQL Interactive Coding Standards

T-SQL Interactive Coding Standards

• Amanda Jackson
• Senior Developer
• http://www.fronde.com/

Why?

• Maintainable Code
• Easy to read, easy to understand

A few standards to whet your appetite

• CAPITALISE keywords
• Indent your code
• Comments, comments & more comments
• Do not cal functions repeatedly
• No anonymous inserts
• Don’t use select *
• Avoid using not equals operators
• Don’t use hard-coded values
• Catch errors

Ideas time…

• Put URLs of pages you’ve followed in code
• Use transactions where appropriate
• Name your transactions – helps with debugging
• Segment your code into sections
• Don’t put JOINs into the WHERE clause
• Columns in neat orders
• Don’t use one line statements
• Debug tables
• Cursors are a necessary evil? (Don’t know if I agree with this)
• Table variables vs. Temp tables (work out which one is appropriate)
• Indexing
• Pseudo code in comments

Resources

• http://nzgirlgeek.blogspot.com/
• Amanda.jackson@fronde.co.nz

Microsoft SQL Server 2008 Reporting Services

Microsoft SQL Server 2008 Reporting Services

• Architecture Overview
• Presented by Adam Cogan
• Chief Architect of SSW and
• Microsoft Regional Director
• http://www.ssw.com.au/
• Delivering Awesome Web Applications

Agenda

• Why Upgrade?
• Installing
• Architecture
• IIS
• Report Processing Changes
• Report Design Improvements
• Memory Improvements / Performance
• Discontinued Features

Why Upgrade (to SSRS 2008)?

• Dundas Charts – Beautiful charts
• No IIS – will improve performance
• Stability
• 503 errors: server too busy
• Performance – big reports always work now
• Report Builder 2.0 – Report development in the browser
• Tablix control – Table + Matrix

Performance (how to measure)

• Create Web Test
• Record Contacts.rdl (6704 pages)
  • Open SSRS
  • Open Contacts Report
  • Goto last page
  • Goto random pages
• Look at ‘Total Time’
  • Add Duration field to Test Results pane
  • Run a couple of times each
  • Run both 2005 and 2008

Rule #49

• never accept user’s emotive description of performance slowness
• measure before and after

How was it faster?

• The new architecture allows them to cache a few bytes per page
• Enables quick restart of pagination of a partially/fully…

Ways to install

• Upgrade
• Side by Side
• Fresh Install

Green ticks on installation… Next

• Next get 2005 and 2008 working – using Justin King’s blog to help
• Upgrading
• Show a few new features

Architecture Changes

• Report Server is not hosted in IIS
• IIS is re3place by SQL Server internal components
• Report Manager is gone (moved to part of Windows Service)

Upgrading

• No Support for custom virtual directories

Why remove IIS?

• IIS complexity increased support costs
• Now easier to configure
• Too many other IIS settings impacted RS
• Other application hosted in IIS impacted RS
• IIS required a two service model
  • Hard to deliver features like resource governing
  • Communication between services causes problems
• Reusing SQL Server components has great benefits
• Sometimes block deploying RS in the enterprise
• Management don’t like IIS not on same machine as SQL Server
• Cross platform environment on Web

IIS – The Lost Functionality

• Some IIS functionality is not provided
  • Anonymous and Digest authentication
  • Client certificates
• ISAPI’s are not supported
  • Use ASP.NET HTTP Modules instead
  • Most impacted are Single Sign on solutions
  • ISA Server is…

Report Builder 1.0

• Only worked on Reporting Models
• Accessible from tool bar

Report Design Improvements – Reports Builder 2.0

• A Standalone Report Designer (don’t need VS.NET)
• (Not included in RTM – download)
• Layout surface shared with Visual Studio…

Discontinued Features

• IE 5.5 support
• …

Monitoring SQL Server with DMVs

Monitoring SQL Server with DMVs

• Dr Greg Low
• Sunday @ 9am

What we will cover

• DMVs Introduced
• A New Insight Into Existing Technologies
• An Insight Into Newer Technologies
• Usimg DMVs in Custom Reports

DMVs Introduced

• SQL Server 2005+
• Internal state/helath of server
• Previously used system tables, DBCC, Profiler, Perfmon
• Diagnose problems, tune performance
• DMVs and DMFs

Scope and Permissions

• Require SELECt permission plus:
• Server scope – VIEW SERVER STATE
• Database scope – VIEW DATABASE STATE
• Create user in master and DENY to restrict across database
• Sys schema and dm_* naming

A New Insight Into Existing Technologies

• O/S
  • Sys.dm_os_performance_counters
• DB
  • Sys.dm_db_partition_stats
  • Sys.dm_db_index_usage_stats
  • Sys.dm_db_physical_stats
  • Sys.dm_db_operational_stats
  • Sys.dm_db_missing_index_details
  • Sys.dm_db_missing_index_columns
• Statistics
  • Sp_helpstats ‘Production.Product’;
  • CREATE STATISTICS ColorStats ON Production.Product(Color) WITH FULLSCAN;
  • DBCC SHOW_STATISTICS(‘Production.Product’,ColorStats);
• Server
  • Sys.dm_exec_sessions
  • Sys.dm_exec_requests
  • Sys.dm_exec_sql_text(…)
  • Sys.dm_exec_query_status
  • Sys.dm_exec_cached_plans
• Legacy
  • EXEC sp_who2
  • Master..sysprocesses
  • Master..syscacheobjects

An Insight into Newer Technologies

• sys.dm_clr_properties
• sys.dm_clr_appdomains
• sys.dm_clr_tasks
• sys.dm_db_mirroring_connections
• sys.dm_broker_connections
• sys.dm_broker_queue_monitors
• sys.dm_tran_top_version_generators
• sys.dm_tran_version_store

Using DMV’s in Custom Reports

• Look at Standard Reports to learn how to use DMVs
• Run a Custom report (generated in SSRS)
• Warning message: Trojan reports!
• Custom report runs in the context of the currently selected database – might not be the appropriate context
• Running the report once throws it into the drop down of recently used reports
• Added in SQL Server 2005 Service Pack 2
• Same familiar RDL format
• Object-Related Reports - Using existing object context
• SSMS 2008 can’t run SSRS 2008 reports! Must be SSRS 2005.

Learning to Use DMVs

• Report Samples are shipped with the product
• Good examples of end-to-end user of DMVs and DMFs
• Buck Woody – blogs

Top 15 DBA Tasks SQL 2005/2008

Top 15 DBA Tasks SQL 2005/2008
• Adam Cogan / Justin King
• www.SSW.com.au

Our tips (from the floor)
• scripting backups, index, reorgs
• SQL Agent jobs
• Using MSX
• Maintenance plans
• Emails for low disk space
• Polling for deadlocks
• Using SMO to generate scripts
• SCOM
• Send email on restart

Tools available (also from the floor)
• SQL Backup from Red-gate (works on all versions and not just Enterprise)
• Spotlight – alerts for email
• HybridX
• SQL Delta www.sqldelta.com (like red-gate)
• Idera
• Data Dude – Team System – AKA Microsoft Visual Studio 2008 Team Edition for Database Professionals
• Toad for SQL (an Oracle GUI)

Agenda
• Automating Alerts

Fear of the new century: no email
• people fear going without email

How do you do it?
• Automation is your friend
• You should automate all ordinary tasks
• This frees up time for you to perform “fun” tasks

1. Do you measure uptime?
• Measuring downtime to impress the boss
• Monitor Uptime
• Monitor Performance
• 2000 – How
o MOM
o 3rd parties
o Roll your own; generate Monthly report
• 2008
o Monitor servers – SCOM 2007 with SQL Server Management Pack
o Management Data Warehouse (MDW)

2. Are you up to date?
• Patching/Service packs
• SELECT @@VERSION (from Registered Servers folder)
• NetPing
• SQL Ping www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx by Chip Pearson - Free
• SQL Squirrel by NGS www.ngssoftware.cm/products/database-security/ngs-squirrel-sql.php

3. Do you script everything?
• Manual + SQLCMD
• Powershell + SMO (was DMO)
• Data dude
o GDR
• SSW SQL Deploy

4. Are you using a Domain Account?
• Configure to run as a domain account

5. Don’t run as an Administrator(?)
• Grant admin privs or lose functionality
• Missing BUILTIN\Administrators account
• ShellRunas by Marc Ruvonovich


7. Have you Turned on the Default Alerts?
• Severity Level 19 and above
• Other errors
o Developers want an email when a problem
o Network Admins don’t want junk in the Event Log
o SQL2000 – mail was unreliable

8. Have You Created Your Own Alerts?
• You can create application-specific alerts
• RAISERROR WITH LOG
• Use Database Mail in the proc – Async, Queued

9. Alerting Based on Perfmon Counters
• Configure through same UI as Transact-SQL…
• Monitor critical information not otherwise easy to get at: Memory Usage, Database Size, Tempdb usage
• Note that this is NOT an expensive operation…

A cool tool…
• TeraCopy
• O for awesome

Management of data warehouse (performance)

Management of data warehouse (performance)

• Greg Low (again)

Agenda

• Show
  • Definition of the new tools and features
  • Demonstrations
• Flow
  • Explanation of how the features work
  • Demonstration of the features components
• Know and Go
  • Internals and architecture
  • Planning and Implementation

SQL Sever 2008 Performance Monitoring Tools

• Data Collector
• Management Data Warehouse
• Performance and Configuration* Reports
• Activity Monitor
• Graphical Showplan*
• SQL Profiler*
• Dynamic Management Views*
• Resource Governor (Enterprise Only)
• XEvents
• Database Engine Tuning Advisor (Enterprise Only)*

*=Enhanced Feature Take a snapshot (copy) of data

• from system data management views
• into a static copy
• e.g. sys.dm_exec_sessions

Activity Monitor (is really sweet)

• Processes
• Resource Waits
• Data File I/O
• Recent Expensive Queries

Data Collection and Evaluation Components

• SQL2K8 Engine Windows Service (Data Collector)
• SQL2K8 msdb
• Etc (missed it)

Data Collector Internals and Architecture

• Data Provider (Collection Item)
  • TSQL
  • Performance Counters (e.g. sys.dm_os_performance_counters)
  • Trace Definition
• Collection Set
  • Msdb Database tables
  • Linked to Agent jobs
• SSIS
• Management Data Warehouse

Planning Tips and Tricks

• Think Central
  • Collecting to a central MDW keeps you from monitoring the collection itself
  • Using a “Central Management Server” makes…
• Watch the Clock
• Space, the Final Frontier

Performance Monitoring Tools Roadmap

• Data Collection
  • Data Collection Sets
• Performance and Diagnostics Monitoring
  • System Collection Sets Reports
• Historical and baseline comparisons
  • Management Data Warehouse going forward
• Trouble-shooting and Tuning
  • Policy based management

Answering the Queries Your Users Really Want to Ask

Answering the Queries Your Users Really Want to Ask

• Dr Greg Low
• glow@solidq.com
• SolidQ Australia

When querying a database, what do users want?

1. They’re not sure
2. They don’t want to be precise
3. They do want to use their own terminology
4. The want the answers fast

What do we give them?

1. Really limited choices
2. Really strict terminology
3. …

What we will cover

• What can you do for me?

Can’t we just use LIKE?

• WHERE Description LIKE ‘%hockey%’

Strings vs Words

• Search for: Pen
• Get Back: Pencil = fail
• Or Open, pendulum, penis

CONTAINS

• AND
• OR
• (‘”exact match”’)
• FORMSOF(INFLECTIONAL, word)
• FORMSOF(THESAURUS, word)

FREETEXT

• search for a sentence
• with ranking

CONTAINSTABLE

• ISABOUT (with weighting)

For true location of where to place your personalised thesaurus:

• don’t go to the Books Online site
• Instead read Greg’s blog here: http://sqlblog.com/blogs/greg_low
• SQLDATA\MSSQL10.MSSQLSERVER\MSSQL\...

Upgrade Options

• New Index Structure (the structure has been brought internal to the database engine)
• Upgrade options:
  1. Import(default)
  2. Rebuild
  3. Reset
• Possible Upgrade methods
  1. In place
  2. Restore/attach

Potential Extensions

• New data types
  1. XML data types
  2. CLR UDT
• Extend the IFTS feature set
  1. snippets with hit-highlights
  2. field weighted relevance
  3. customizable tokenizing
  4. customizable proximity operator
  5. property level search
• Be heard now!

Summary

• IFTS can add significant value
• Implementation -> straightforward
• Management -> straightforward
• Users love this
• SQL Server 2008 has changed the game!

SQL 2008 – TSQL Enhancements

SQL 2008 – TSQL Enhancements

• Justin King
• Architect SSW
• http://kingjustin.com/
• http://www.ssw.com.au/

Agenda

1. Platform changes
2. XML Enhancements
3. Compound Operators
4. Row Constructors
5. MERGE keyword
6. Table Valued Parameters
7. Sparse Columns
8. Date Time enhancements
9. Filestream
10. Spatial data
11. Hierarchy Data Type

Platform Changes (Justin’s top 8)

1. Database Mirroring enhancements
2. Policy Based Management
3. Auditing
4. Data Compression
5. BACKUP Compression
6. Powershell Integration
7. Transparent Database Encryption
8. Change tracking on databases

XML Enhancements

• Improved schema validation capabilities
• Enhancements to XQuery support
• Enhanced functionality for performing XML data manipulation language (DML) insertions
• Full timezone support for data/time types (including xs:date, xs:time)
• http://www.microsoft.com/sqlserve/2008/en/us/wp-sql-2008-whats-new-xml.aspx

Compound Operators

• Can now declare and initialize variables in the same statement
• +=
• -=
• /=
• *=
• %=

Row Constructors

• Values clause returns relational table with multiple rows
• Use with INSERT statement to insert multiple rows as an atomic operation;

Change Tracking

• Version of the data
• Most commonly used for entity data models

MERGE Keyword

• UPSERT
• Atomic statement combining INSERT, UPDATE and DELETE operations based on conditional logic
• Set-based operation; more efficient than multiple separate operations
• MERGE is defined by ANSI SQL; you will find it in other database platforms as well e.g. Oracle
• Useful in both OLTP and Data Warehouse environments
  • OLTP: merging recent info from external source
  • DW: incremental updates of fact, slowly changing dimensions

Table Valued Parameters

• CREATE TYPE [typeName] AS TABLE
• Great for passing in table parameters to a stored proc
• Strongly type variables
• Helps address the need to pass array elements to stored proc/functions

Sparse Columns

• Ordinary Columns optimised for NULL values
• No space used unless data added (4 byes extra if you do use)
• Behaves the same for end user

Date Time enhancements

• New date and time types!
• DATE – ANSI compliant
• TIME – ANSI compliant
• DATETIMEOFFSET – TZ aware DateTime
• DATETIME2 – DateTime with variable precision and larger range support

Filestream

• Stores binary files on file system
• Can’t use with MIRROR-ed databases
• Varbinary(max) – gets over 2GB limit
• Not accessible by files only by SQL Server
• Reduce size of database and backups

Spatial Data

• Two new system CLR data types
• GEOMETRY
• GEOGRAPHY

Hierarchy Data Type

• New system CLR type supporting trees
• Internally stored as Varbinary <= 900 bytes
• Holds a path that provides…

Resources

• Books Online
• TechEd DVD’s
• PDC Videos
• MSDN Webcasts
• Greg Low SQL Downunder podcast