SQL 2008 : Report Builder

Version 2 out...

http://www.microsoft.com/downloads/details.aspx?FamilyID=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en

Reporting Services 2008 : Invalid Namespace

Got the following error, when trying to connect to my new Reporting Services 2008 build.

" No Report Servers were found.

Details : Invalid Namespace "

Fortunately, Justin King has already solved it for us, and has an really easy to follow guide to resolving this issue here >

http://www.kingjustin.com/post/2008/08/Reporting-Services-2008-Invalid-Namespace.aspx

( Thank you, Justin ! )

SQL Server Video : Nibbling Deletes

http://www.sqlservervideos.com/player/wms/nibbling-deletes

Outlook 2007 speedup

The Addins enabled by default in Lookout 2007 hamper it's performance somewhat.

You can achieve a fair performance boost, by >

1) Open Outlook as Administrator (right-click icon, 'run as administrator' if you're not an admin already)

2) Navigate to Tools > Trust Center > Addins

3) Click 'go' (to the right of 'COM Addins' at the bottom of the screen).

4) Untick anything you don't need.

System Center Virtual Machine Manager 2008

RTM is released. Now just have to wait for the MSDN discs :)

http://msdnrss.thecoderblogs.com/2008/10/21/system-center-virtual-machine-manager-2008-has-rtmed/

http://www.microsoft.com/systemcenter/scvmm/downloadbeta.mspx

Recommended : Notepad++

Notepad++. A FREE notepad replacement.

http://notepad-plus.sourceforge.net/uk/site.htm

Am using it instead of ultraedit now...

SQL 2008 : Enable TCP/IP

After installing, we need to enable TCP/IP so external machines can access SQL...

1. run SQL Server Configuration manager

2. select SQL Server Network Configuration

3. select Protocols for 'instancename'

4. right click on TCP/IP and select 'enable'

SysPrep

Generates a new random SID (security ID), unique to the image / VM.

Start > Run > Sysprep [Enter]


If you forget to do this in preparing your VMs, you'll need NewSid.

Install Messenger on 64 bit platforms

MSN Messenger on Windows 2008 64bit...

http://www.msghelp.net/printthread.php?tid=86312

Enabling Remote Desktop Users

via Advanced User Management.

Put them in this group to avoid granting Admin permissions unnecessarily...

Index Usage in current db

Use this to review index usage >

SELECT object_name(object_id), *
FROM sys.dm_db_index_usage_stats
WHERE object_id > 100
AND database_id = db_id()

Manual Uninstall of SQL 2005

I managed to get a corrupt SQL 2005 which in turn prevented the install of SQL 2008...


A useful post, that helped me out today >

http://www.gilham.org/Blog/Lists/Posts/Post.aspx?List=aab85845-88d2-4091-8088-a6bbce0a4304&ID=190

SQL 2008 : Security

Note: In sql 2008, the ’sa’ login has been replaced by ’sysadmin’

The 'sa' still exists but is to be deprecated, i.e. will not be there in future versions.

Networking Basics - Network Clients

Installing the Hyper-V role seems to make Windows 2008 a little forgetful at times about it's network settings. On a couple of occasions I've returned to my NIC configuration to find network settings missing.

If you cannot connect to a server, check it has 'File and Printer Sharing' enabled as below >


If your server cannot see others on the network, check 'Client for Microsoft Networks' is enabled.

SQL 2008 : Server Auditing Functionality

is looking good...

Enabling Auditing
http://sqlblogcasts.com/blogs/sqldbatips/archive/2008/06/27/new-in-sql-2008-server-and-database-auditing-part-i.aspx

Server Level Auditing
http://sqlblogcasts.com/blogs/sqldbatips/archive/2008/06/27/new-in-sql-2008-server-and-database-auditing-part-ii.aspx

Database Level Auditing
http://sqlblogcasts.com/blogs/sqldbatips/archive/2008/06/27/new-in-sql-2008-server-and-database-auditing-part-iii.aspx

SQL 2008 on Hyper-V : Whitepaper

Whitepaper released 2 days ago. Performance looking very promising...

http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/SQL2008inHyperV2008.docx

Currently not linked from the main whitepapers page for some reason >

http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx

Setup has detected a canonical discretionary access control list (DACL) on directory...

Got this error today during SQL 2008 install...



" Setup has detected a canonical discretionary access control list (DACL) on directory... "

Got round this by >

Adding 'Domain\Sql Service Accounts' (a domain group containing my 5 service accounts) to have full rights on c:\program files\microsoft sql server (& all subdirectories)

Syscacheobjects (Sql query plan reuse)

Viewing the contents of the Sql cache (and how many times plans have been reused).

select cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects

Dirty Reads - The NOLOCK hint

I googled ‘read uncommitted speed’ and followed a link for ‘dirty reads’ (database terminology for viewing uncommitted transactions).
Suffice to say, the page went somewhere i didn’t intend to go!

Anyway, enough waffling, what are Dirty Reads?

"a dirty read is where a query reads data from the database without lock protection. Without lock protection, you cannot be guaranteed that the data isn’t changing during the time that the query is running"

To perform 'Dirty Reads' in a query >
Use the WITH (NOLOCK) table hint after each table involved in the query.
eg;

SELECT * FROM Table1 WITH (NOLOCK) 
INNER JOIN Table2 WITH (NOLOCK) 
        ON Table1.PK = Table2.FK

To set 'Dirty Reads' as default read type for the connection, use >

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

This latter, connection level delaration is especially useful for reporting systems.
http://blogs.neudesic.com/blogs/phil_scott/archive/2005/12/05/11.aspx
http://www.databasejournal.com/features/mssql/article.php/3553281/Controlling-Transactions-and-Locks-in-SQL-2000-and-2005---Part-3.htm

PS : If I show up in the proxy logs, it wasn’t intentional!

Reorganize / Rebuild a single index

Reorganizing an index physically reorders the leaf nodes of an index >

ALTER INDEX indexname ON schema.table REORGANIZE

Rebuilding an index drops an index and recreates it >

ALTER INDEX indexname ON schema.table REBUILD

Rebuilding is more resource intensive.

If you're using a version that supports it (Developer/Enterprise), rebuilds can be done ONLINE.

ALTER INDEX indexname ON schema.table REBUILD WITH(ONLINE = ON)