Category Archives: SQL

Moving WSUS 3.0 database and content

If ever you wish to move this sizable chunks of data to a different partition, you can do it.  Each data type has its own method for moving.

The WSUS content is moved via the wsusutil command-line utility.  This is something I briefly covered yesterday …or was that this morning?  The days and nights are running together – ughh its only Monday! :O

The database, SUSDB, is a bit more involved being that it runs on SQL or the Windows Internal Database (the default SBS 2003 R2 location).  The general steps are to detach the database from its instance (default is MICROSOFT##SSEE), move the actual files – SUSDB.mdf & SUSDB_log.ldf, and then re-attach these files from their new placement.

Those steps each involve a lot of special knowledge.  Just gaining access to the database can be a challenge and may require using the named pipe path that I’ve blogged on before.  Once there you cannot merely detach the database as it will be in use.  To end these usage tentacles you need to fully stop the W3SVC & WsusService services – World Wide Web Publishing Service & Update Services respectively.  When stopped you should be able to gain the green light from SQL Server Management Studio Express to detach the database.  Once done then you move the files via your method of choice such as xcopy …etc or a simple cut and paste.  After moved, you again use the SSMSE tool to attach the database files.

image

That’s it.

References – different and not the exact same techniques but worthy of noting:

http://msmvps.com/blogs/bradley/archive/2007/08/11/detach-and-reattach.aspx

http://www.smallbizserver.net/Forums/tabid/53/forumid/11/postid/74702/view/topic/Default.aspx

**tip**  Use the search tool of this blog to find related topics previously blogged.

Re-index the WSUS 3.0 Database via a GUI

Combining forces and ideas you can use the scripting provided by TechNet with my blog post on using the SQL Server Management Studio Express (SSMSE 2005) to get this maintenance task done easily.  When done your WSUS database will be healthy and far more responsive.

This can have a significant beneficial impact when you consider how the clients submit sync queries to the WSUS 3.0 server especially at startup.  Be prepared for this script to run from anywhere between 5-30 minutes depending on amount of work needing done to get things to good health. Use at your own risk. Script below is copied from the TechNet source.

TechNet script source and info (recommending reading once before using script):
http://www.microsoft.com/technet/scriptcenter/scripts/sus/server/susvvb01.mspx?mfr=true

 /******************************************************************************
This sample T-SQL script performs basic maintenance tasks on SUSDB
1. Identifies indexes that are fragmented and defragments them. For certain
tables, a fill-factor is set in order to improve insert performance.
Based on MSDN sample at http://msdn2.microsoft.com/en-us/library/ms188917.aspx
and tailored for SUSDB requirements
2. Updates potentially out-of-date table statistics.
******************************************************************************/

USE SUSDB;
GO
SET NOCOUNT ON;

-- Rebuild or reorganize indexes based on their fragmentation levels
DECLARE @work_to_do TABLE (
objectid int
, indexid int
, pagedensity float
, fragmentation float
, numrows int
)

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @numrows int
DECLARE @density float;
DECLARE @fragmentation float;
DECLARE @command nvarchar(4000);
DECLARE @fillfactorset bit
DECLARE @numpages int

-- Select indexes that need to be defragmented based on the following
-- * Page density is low
-- * External fragmentation is high in relation to index size
PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)
INSERT @work_to_do
SELECT
f.object_id
, index_id
, avg_page_space_used_in_percent
, avg_fragmentation_in_percent
, record_count
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f
WHERE
(f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count 50 and f.avg_fragmentation_in_percent > 15.0)
or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)

PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20))

PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121)

SELECT @numpages = sum(ps.used_page_count)
FROM
@work_to_do AS fi
INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id

-- Declare the cursor for the list of indexes to be processed.
DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do

-- Open the cursor.
OPEN curIndexes

-- Loop through the indexes
WHILE (1=1)
BEGIN
FETCH NEXT FROM curIndexes
INTO @objectid, @indexid, @density, @fragmentation, @numrows;
IF @@FETCH_STATUS < 0 BREAK;

SELECT
@objectname = QUOTENAME(o.name)
, @schemaname = QUOTENAME(s.name)
FROM
sys.objects AS o
INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE
o.object_id = @objectid;

SELECT
@indexname = QUOTENAME(name)
, @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END
FROM
sys.indexes
WHERE
object_id = @objectid AND index_id = @indexid;

IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation = 5000 AND @fillfactorset = 0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';
ELSE
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command;
EXEC (@command);
PRINT convert(nvarchar, getdate(), 121) + N' Done.';
END

-- Close and deallocate the cursor.
CLOSE curIndexes;
DEALLOCATE curIndexes;

IF EXISTS (SELECT * FROM @work_to_do)
BEGIN
PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20))
SELECT @numpages = @numpages - sum(ps.used_page_count)
FROM
@work_to_do AS fi
INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id

PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20))
END
GO

--Update all statistics
PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)
EXEC sp_updatestats
PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)
GO

Blog on Connected to Windows Internal Database with SSMSE via named pipes:
https://duitwithsbs.wordpress.com/2008/09/01/getting-access-into-windows-internal-database-instance/

Another helpful tool is to identify which version of SQL your database actually is.  Is it SQL or Windows Internal Database?
https://duitwithsbs.wordpress.com/2008/07/17/what-sql-version-is-it/

Getting Access Into Windows Internal Database Instance MICROSOFT##SSEE

Several Microsoft applications require databases that utilize the Windows Internal Database aka SQL Server 2005 Embedded Edition.  These include WSUS 3.0 and WSS 3.0 (SharePoint Services).  The instance is noted by a name of ‘MICROSOFT##SSEE’.

If you ever need to get at the databases contained in the instance you will find that this isn’t conventional.  In SQL Server Management Studio UI you must use a named pipe identity in order to connect to this instance.  You optionally could also use SQLCMD or OSQL scripts.

  • Download and install the SQL Server Management Studio Express (if no SQL management tool is available)
  • open SQL Server Management Studio Express **As Administrator** (if in a newer OS where that is an option)
  • Connect the SQL Instance using named pipe
    • Server name: ‘\\.\pipe\mssql$microsoft##ssee\sql\query’  (no quotations)
      or for WID database:
      \\.\pipe\microsoft##WID\tsql\query
    • Server Type:  Database Engine
    • Authentication: Windows Authentication
    • options -> network protocol:  Named Pipes

***now months later I found this critically important to a SharePoint installation that was using this database instance.  What had occurred is that this separate and experimental lab server doesn’t have a regular backup routine; consequently, SQL’s log grows and grows until a backup is made.  My _log.ldf file was well over 30GB!  Also, the drive was less that 1% from full.  So a little Googling and up popped an Experts Exchange thread that had the information embedded – http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23983391.html  With the Express Management Studio GUI and a couple of clicks it was all sorted. 🙂  Lots of space released and I’m wiser for it.

a nice SQL reference:  http://blogs.mssqltips.com/forums/t/843.aspx

***editors note***
As I worked through an SBS 2008 migration, the Companyweb migration instructions specify to ONLY open AS ADMINISTRATOR (right click the shortcut) the SQL Server Management Studio Express (SSMSE) before entering the Named Pipe path.  DO IT.   Trust me, if you don’t you won’t connect to the …##SSEE database.

What SQL Version Is It?

How to identify your SQL Server version and edition

The short version is that you need to run either of the following queries in SQL Management Studio (Express):
1. SELECT @@VERSION
2. SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

If that hasn’t answered your inquiry then try this list of SQL Build numbers:  http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx

Inside the SQLServerCentral.com (registered users only) site is a forum with an ongoing thread on build number ID’s:  http://www.sqlservercentral.com/Forums/Topic356673-32-7.aspx

Jargons – GDR and QFE release?

http://blogs.msdn.com/gauravagg/archive/2007/04/27/jargons-gdr-and-qfe-release.aspx

BCM Mastery Series #1 – Server Shared – Location, Location, Location!

So where is it?  That is a great question that gets to the heart of an issue you need to change.  Do you want the database to survive if the original creator leaves the company?  YES!  Did you know that won’t happen by default?  Well if that user’s profile is deleted so goes the database.  Gotcha!

We are talking about a server located and shared Business Contact Manager (BCM)/Small Business Accounting (SBA) database created using the fantastically easy Business Contact Manager for Outlook 2007 Database Tool.

Yes, location is everything as it applies to real estate, locating a city in hurricane-alley ocean-front and below sea level, and in BCM, Business Contact Manager this is also true.  Let me explain that BCM was first designed to be run simply from a user(s) on a single desktop.  Later SBA, Small Business Accounting, came along and was integrated with this same database for obvious great common data access.  This design legacy still has an affect even as we can now move the BCM latest version’s database (.mdf & .ldf files) to the server for sharing, centralized management, and improved resources.  It’s installed default location is at:

C:\Documents and Settings\%username%\Local Settings\Application Data\Microsoft\Business Contact Manager

image

So for database continuity and protection it is moving time unlike those inured and unresponsive to New Orleans’ death trap locale.  Let’s put it in the same logical area as all other SQL databases under

“C:\Program Files\Microsoft SQL Server”

Since our database instance for BCM/SBA is MSSMLBIZ we will create a sub-folder there called “MSSQL$MSSMLBIZ” (imitating the other SQL folders).  Now continue the following steps as follows:

  1. in Microsoft SQL Server Management Studio Express (SQL 2005 Express Advanced) under Object Explorer expand the database MSSMLBIZ, right click the target database which is by default “MSSmallBusiness”, and then “Detach” under Tasks
  2. now detached we go to the current file location of the .ldf &  .mdf files (see above path) and “cut” them to start the move – note I only need the .ldf & .mdf files
  3. now we navigate to our newly created folder and “paste” them
  4. last step is to go back into the Studio and in the same place we did the “detach” we choose to “attach”, browse to our new location, and pick the .mdf file.
  5. Voila! It should complete and your now ready to open up an Outlook w/ BCM client and check that you are connecting. It’s all good now.

Later I’ll link to information on how to restore with just the .ldf & .mdf files if that was all you had since you were a bonehead and never did the BCM backup that Outlook Tasks reminds you of once a month.  Perhaps you can figure out that desired goal sufficiently with the Studio knowledge learned solely from this post and working knowledge of NTBackup or whatever else you are using.

BCM Mastery Series

  1. Location, Location, Location
  2. BCM for Outlook 2007 Database Tool overview (being written)
  3. BCM for Outlook 2007 Database Tool Logging
  4. SQL Management Studio for the BCM Database (being written)
  5. SQL 2005 Express Advanced installation Walk Through (being written)