Wednesday 29 September 2010

NEWSID breaks Windows Server 2008 R2

NEWSID and Windows Server 2008 R2 do not play nicely together.

Fortunately, I found out using VMs hence simply just restored the hard drive.

Link : NEWSID breaks Windows Server 2008 R2

The solution is to use Sysprep...
  1. Navigate to \Windows\System32\Sysprep
  2. Run Sysprep.exe
  3. Use the 'Generalize' tick bo.
  4. When you restart, you'll get the install prompts for country, language etc...
Links :
Using SYSPREP.EXE to Change SID in Windows Server 2008 and Vista
Sysprep in Windows Server 2008 R2
Replacement for NewSID when working with Windows 7?

Tuesday 21 September 2010

How to disable Windows Error Reporting (WER)

1) Go to Start , Run , type 'gpedit.msc' (or launch from AD if doing for a domain)
2) Computer Configuration\Administrative Templates\System\Internet Communication Management\Internet Communication settings
3) Enable 'Turn off windows error reporting'
 
Links:
Jordan Bortz : Losing space on Drive C? Check your WER ReportQueue
Technet : Windows Error Reporting

Sunday 19 September 2010

MCITP : Business Intelligence Developer 2008

Passed the BI MCITP Exam today. Am very relieved as it was the toughest of all the exams.

Am now 3 x MCITP in SQL 2008 Administration, Development and Business Intelligence :)

Tuesday 14 September 2010

Bookmark : How is fill factor impacting my indexes?

David Levy writes about Fill Factor for TSQL Tuesday and provides a useful script too...

http://adventuresinsql.com/2010/09/how-is-fill-factor-impacting-my-indexes/

SSIS : OLEDB Error Codes

Some error messages I (ssis) generated today - 

-1071607116 : A rowset based on the SQL command was not returned by the OLE DB provider.

-1073450982 : component "Component Name" (1) failed the pre-execute phase and returned error code 0xC02092B4.

I had a strange sense of deja-vu and hence have to point myself back to this post to sort it!

Solace : SSIS, OLEDB and Stored Procedures

Friday 10 September 2010

MCTS : SQL Server 2008, Business Intelligence Development and Maintenance

Just a quick note to say I passed 70-448 : Microsoft SQL Server 2008, Business Intelligence Development and Maintenance.

Not looking forward to the MCITP mind, that will be tough..

r

Bookmark : SSMS Templates

Templates are an overlooked feature of Management Studio.
I must remember to use them rather than jumping for Help / Books Online / Google !

Can't remember the syntax? Use templates!


Write your own SSMS Templates

Hyper-V : VHD size optimization #2

Time to revise an old post based on recent experiences with Hyper-V.
I posted on how to optimize the size of VHDs, but I'm revising that now to include step 2 below >

1) Defragment the VHD (Defraggler was better than the Windows tool)
2) Use Precompact.exe to a "zero out" i.e. overwrite with zeros available blank space. *
3) Shut down the VM and Shrink the VHD from Hyper-V.

* Precompact.exe is found in precompactor.ISO which in turn you get from the Virtual PC 2007 installation (in C:\Program Files (x86)\Microsoft Virtual PC\Virtual Machine Additions)

Link : Compacting Hyper-V files

Wednesday 1 September 2010

Blatent Plug : SQLWorkshops.com - Free Performance Monitoring/Tuning Webcasts

There is a lot of good training material and video blogs for people wishing to further their SQL skills.
Pragmatic Works (for BI) , Cuppa Corner from SQLServerFAQ are my favourites, as well as community webcasts from Quest and Redgate.

Most of  the free stuff simply gets you going. It isn't rocket science, it just saves you an hour or so reading (another manual). Ramesh Meyyappan's SQLWorkshops site is different. 
The videos are still FREE , But they are 'Level 400' (from attending conferences I know this to mean 'the clever stuff'! ) Anyway, if you're interested in performance monitoring and tuning you can download them from http://www.sqlworkshops.com/webcast.
 
Ramesh Meyyappan attended SQLBits V where I saw him present his 'Let's make SQL fly' talk.
After presenting 'Monitoring & Tuning Parallel Query Execution' at SQLBits VI he is back for more on October 2nd to present 'Monitoring & Tuning Parallel Query Execution - Part II at SQLBits VII

* (yes I was encouraged to write this post due to a potential freebie t-shirt, but I do genuinely rate the webcasts)

rich

Windows Group Membership Checker

SQLServerCentral.com have published a script I use to look at Windows Group Membership. This is good if you use AD groups to manage security...

SSC : Windows Group Membership Checker



March 2011 Update : Putting the function here too now as SSC exclusivity period over...


/*
Script  : SQL Server - Windows Group Membership Checker
Version : 1.0 (August 2010)
Author  : Richard Doering
Web     : http://sqlsolace.blogspot.com
*/

DECLARE @CurrentRow INT
DECLARE @TotalRows INT
SET @CurrentRow = 1

DECLARE  @SqlGroupMembership  TABLE(
    ACCOUNT_NAME      SYSNAME,
    ACCOUNT_TYPE      VARCHAR(30),
    ACCOUNT_PRIVILEGE VARCHAR(30),
    MAPPED_LOGIN_NAME SYSNAME,
    PERMISSION_PATH   SYSNAME
    )

DECLARE @WindowsGroupsOnServer TABLE(
   UniqueRowID int IDENTITY (1, 1) Primary key NOT NULL 
 , Name  SYSNAME
 )
 
INSERT INTO @WindowsGroupsOnServer (NAME)
SELECT [NAME] FROM master.sys.server_principals WHERE TYPE = 'G' 

SELECT @TotalRows = MAX(UniqueRowID) FROM @WindowsGroupsOnServer

DECLARE @WindowsGroupName sysname 


-- Loop Each Windows Group present on the server
WHILE @CurrentRow <= @TotalRows 
   BEGIN 
  
  SELECT @WindowsGroupName  = [Name] 
  FROM @WindowsGroupsOnServer
  WHERE UniqueRowID = @CurrentRow 
  
    BEGIN TRY
    -- Insert found logins into table variable
    INSERT INTO @SqlGroupMembership (ACCOUNT_NAME,ACCOUNT_TYPE,ACCOUNT_PRIVILEGE,MAPPED_LOGIN_NAME,PERMISSION_PATH)
       EXEC xp_logininfo @WindowsGroupName , 'members' 
    END TRY

    BEGIN CATCH
    -- No action for if xp_logininfo fails
       END CATCH
       
 SELECT @CurrentRow = @CurrentRow + 1   
 
   END 
   
-- Display final results
SELECT  @@servername AS Servername
    , [PERMISSION_PATH] AS WindowsGroup
    , Account_Name
    , Mapped_Login_Name
    , Account_Type
    , Account_Privilege
FROM @SqlGroupMembership ORDER BY [PERMISSION_PATH], [ACCOUNT_NAME]