Adding a machine to a domain

You'll have to excuse me for this (I know tis a simpiltons task) but the specials in support keep asking again... and again... and again!

Log into the machine either with a local user account.
When the desktop appears do the following:
2000 & Windows XP

• Right click on the My Computer and select Properties from the context menu.
• On the Systems Properties screen select the Computer Name Tab and click the Change button
• On the Computer Name Change screen, select the Domian option and then enter a name of a domain e.g. mydomain.local or mycompany.com
• If prompted for a username and password enter one that has privileges to add and remove computers from the domain (usually a domain admin account)
• When the computer is added successfully you will get a message box stating something along the lines of Welcome to the domain
• Press OK
• Back on the Computer Name Change screen press OK
• On the Systems Properties Press OK
• Restart the machine
• When the login screen appears press the Options button and select the newly added domain from the Domain dropdown list
• Log into the machine as a domain user

Vista / Windows 7

• Right click on the My Computer and select Properties from the context menu.
• On the Systems screen select the Change Settings button under Computer name, domain, and workgroup settings
• When the Computer Name screen click the Change... button
• On the Computer Name/Domain Changes screen, select the Domain option and then enter a name of a domain e.g. mydomain.local or mycompany.com
• If prompted for a username and password enter one that has privileges to add and remove computers from the domain (usually a domain admin account)
• When the computer is remove you will get a message box stating something along the lines of Welcome to the domain
• Press OK
• Back on the Computer Name/Domain Changes screen press OK
• On the Systems Properties Press OK
• Close the System screen and restart the machine
• When the login screen appears press enter the name of the added domain and a domain user e.g. mydomain.local\Administrator to log onto that domain

There, now please remember this and stop asking me

BBC iPlayer

Since my parents are both blind using the BBC iPlayer website with their screen reader is an absolute pain, so here is a text only version I created that strips the information from the pages.

http://www.tagtronics.co.uk/tonline/

Export to PST from Exchange 2007

There was a nice time in the past when you could run ExMerge and export users mailboxes to your hearts content although in exchange 2007 onwards they've done away with such GUI goodness and replaced it with some exchange powerscripts... The command we're interested in is Export-Mailbox and requires the following parameters.

Export-Mailbox –Identity <mailboxUser> -PSTFolderPath <pathToSavePST>

Pretty simple right???? WRONG!
You can't run this on a 64bit machine (exchange 2007 is 64bit only for production release), because of the outlook components required it has to be run on a 32bit machine which means installing the Exchange Management Tools software again on a separate machine along with outlook 2003 or 2007 :s

Pretty lame but it does allow you to export mailboxes larger than 2GB which I found a rather annoying constraint of Exmerge.

Defragmenting the field indexes

I came across an odd problem the other day when one of our 32GB enterprise databases started showing some odd behavior and slowing to a halt. After running my usual checks to make sure the indexes where intact and the transaction logs weren't chocka I was left scratching my head. I tried routing in the execution plans to see where the bottleneck was and it was all pointing to an index issue although I couldn't find anything wrong until I checked the fragmentation levels. They basically showed up as 100% on the primary index of the main table. After some research I found the commands needed to defrag the database and created a routine to do the hard work for me on the rest of the fields in the database. So without further ado .....

CREATE PROCEDURE [IndexDefrag] AS
BEGIN
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR(128)
DECLARE @execstr VARCHAR(255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag decimal
DECLARE @maxfrag decimal
DECLARE @IdxName varchar(128)

-- Set the maximum fragmentation level, if the current frag level is below this it will be ignored.
SELECT @maxfrag = 10.0

-- Get a list of all the user tables in the database
DECLARE tables CURSOR FOR SELECT CAST(TABLE_SCHEMA AS VARCHAR(100)) + '.' + CAST(TABLE_NAME AS VARCHAR(100)) AS Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

-- Create a table to store information on the tables index list
CREATE TABLE #fraglist (ObjectName CHAR(255), ObjectId INT, IndexName CHAR(255), IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity decimal, BestCount INT, ActualCount INT, LogicalFrag decimal, ExtentFrag decimal)

-- Open the cursor, then loop through the list of tables in the recordset
OPEN tables
FETCH NEXT FROM tables INTO @tablename
WHILE @@FETCH_STATUS = 0 
BEGIN
  -- Do the showcontig to get a list of all the indexes in the table
  INSERT INTO #fraglist 
  EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
  FETCH NEXT FROM tables INTO @tablename
END

-- Close and deallocate the cursor.
CLOSE tables
DEALLOCATE tables

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName FROM #fraglist WHERE LogicalFrag >= @maxfrag AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor.
OPEN indexes

-- Loop through the indexes and run the ALTER INDEX command to pull the fragmentation down
FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag, @IdxName 
WHILE @@FETCH_STATUS = 0 
BEGIN
  PRINT 'Now executing ALTER INDEX [' + RTRIM(@IdxName) + '] ON [' + RTRIM(@tablename) + '] REORGANIZE WITH ( LOB_COMPACTION = ON ) - Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'
  SELECT @execstr = 'ALTER INDEX [' + RTRIM(@IdxName) + '] ON [' + RTRIM(@tablename) + '] REORGANIZE WITH ( LOB_COMPACTION = ON )'
  EXEC (@execstr)
  FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag, @IdxName
END

-- Close and deallocate the cursor.
CLOSE indexes
DEALLOCATE indexes
 
-- Delete the temporary table.
DROP TABLE #fraglist
END

Compress and Backup SQL Database

Down the line you may come across a point when you need to compress an SQL Database by truncating the log files, an annoying task that cannot be completely undertaken from the GUI (the shrink file option does not truncate the log files). After I had several companies complaining of excessively large Log files on some of our larger more heavily utilised databases I came up with the following script that can be run whenever you need to compress the database.

-- Shrink the Log Files as much as we can, this is the same as the GUI option
DBCC SHRINKFILE (N'DatabaseLogFileName',100)
-- We are now going to truncate the log files, there are rear occasions when you may want to keep the transaction logs but I've never had to work in such an environment as of yet. 
ALTER DATABASE [DatabaseName] SET RECOVERY SIMPLE
CHECKPOINT
-- Once the checkpoint command is run the log files they go bye bye so we can now set ourselves back to our original state
ALTER DATABASE [DatabaseName] SET RECOVERY FULL
-- OK Shrink the main database file
DBCC SHRINKDATABASE (DatabaseName)
-- Back it up to a file
BACKUP DATABASE [DatabaseName] TO DISK = N'BackupFileLocation' WITH NOFORMAT, INIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
-- And while we're at it we might as well reindex the database, you can find this code in one of my previous articles
EXEC TableReindexer

Just make sure you replace the DatabaseLogFileName this is usually the name of the Database with _log stuck on the end, but can be located under logical name of the database file properties. DatabaseName and BackupFileLocation are pretty self explanatory.

You can also run this from a batch file using the SQLCMD command should you wish to set up a scheduled task to do such things as I do.

SET SQLSERVER=SERVERINSTANCE
SET DATABASE=DATABASENAME
SET BACKUPLOCATION=BACKUPLOCATION
SET BACKUPFILENAME=BACKUPNAME

DEL %BACKUPLOCATION%%BACKUPFILENAME% /S /Q

REM Reindex all the keys within the database
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "EXEC tag_tablereindexer"

REM -- Truncate the log file attached to the database, this can sometimes get a little large and slow down the system overall
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "DBCC SHRINKFILE (N'%DATABASE%_log',100)"
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "ALTER DATABASE [%DATABASE%] SET RECOVERY SIMPLE"
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "CHECKPOINT"
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "ALTER DATABASE [%DATABASE%] SET RECOVERY FULL"

REM -- Shrink the actual database itself
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "DBCC SHRINKDATABASE (%DATABASE%)"
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "DBCC SHRINKFILE (N'%DATABASE%_log',100)"
REM -- Backup the databse to our backup location
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "BACKUP DATABASE [%DATABASE%] TO DISK = N'%BACKUPLOCATION%%BACKUPFILENAME%' WITH NOFORMAT, INIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

Again SQLSERVER should be set to the name of the server instance e.g. SQLSVR03\DB12 DATABASE is the database name, BACKUPLOCATION is the path to the backup which also requires to be left with a leadin backslash e.g. c:\backups\ BACKUPFILENAME is the name you want for the backup e.g. SQLBackup.bak

Manually reindex the fields within a table

One of the strange an annoying aspects of SQL is the way it sometimes just completely messes up the indexing on the tables. It can on occasion just become confungulated for no apparent reason resulting in extremely slow query execution. To combat such things consider running the stored proc when you have quiet moments in your database, it will run through each table and update the table statistics on that table. Vola!

CREATE PROCEDURE TableReIndexer
AS
BEGIN
-- REINDEX ALL TABLES IN THE DATABASE
DECLARE @SQLExec VARCHAR(255)
DECLARE MyCursor CURSOR FOR 
SELECT 'UPDATE STATISTICS [' + b.name + '] WITH FULLSCAN' AS [Exec Proc]
FROM sysobjects b
where b.type = 'U' AND b.category=0

OPEN MyCursor
FETCH MyCursor INTO @SQLExec
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC (@SQLExec)
FETCH MyCursor INTO @SQLExec
END
CLOSE MyCursor
DEALLOCATE MyCursor
END
GO