Zelis SQL and windows blog

SQL Server 2008 R2 Evalutation has expired

2011-11-17T21:14:00.000+01:00

I had an interresting case today. Someone had installed SQL Server 2008 R2 evalutation edition and then the server had gone into production after the installer had left the building. Nobody knew that it was evaluation version that were used. Today the evalutation period expired and the server stopped!!!!

After some troubleshooting we found what happened and started to think about what to do. Luckily there were an easy way out without using the backups and reeinstalling.

The link below explains what to do.
http://blogs.technet.com/b/sqlman/archive/2010/03/16/sql-server-2008-upgrading-to-standard-enterprise-edition-from-evaluation-edition.aspx

After doing the necessary steps the SQL Service started fine but the SQL Agent didn´t. I got an wmi error when i tried to change the agent service back to automatic. Apparently this is a bug in SQL Server which appears after the service have been set disabled and you can read about it here.

Maybee this can be of any help to somebody in the future.

Stefan

Msg 952, Level 16, State 1, Database 'Databasename' is in transition.

2011-10-25T11:20:00.000+02:00

Today I got an interesting error from one of our db servers. The index maintenance job had failed and in the log I got the following error: Msg 952, Level 16, State 1, Database 'Databasename' is in transition.
When I tried to access the database in SSMS it was not available.

When I ran sp_who2 I saw that there were one session blocking an alter database command from one user. The user blocking had a session open to the database that were unavailable.
I also saw in the sql server logs that the same user had set the database offline the day before. Now it were stuck in between online and offline.

To solve the problem I just killed the blocking session and then the database went offline without any problems.

SQL Powershell scripts

2011-04-16T21:36:00.001+02:00

I found some interresting powershell scripts that I wanted to run against some sql servers. However I haven´t worked with powershell before so I ran into a couple of problems. When the script want to use sqlcmd I got this error:

The term 'invoke-sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. At C:\Temp\wasted_server_memory.ps1:10 char:14
+ invoke-sqlcmd <<<< -Server $srv -Database master -Query $SQLQuery
+ CategoryInfo : ObjectNotFound: (invoke-sqlcmd:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

After some googling I found that Invoke-sqlcmd isn't included with PowerShell, but rather it's part of the SQL Server 2008 PowerShell Host (sqlps). You can get it if you download The Microsoft® SQL Server® 2008 R2 Feature Pack. After installing it didn´t work anyway and after some more googling I found that you must load it into powershell before it works. You can find a script to do it here.

Regards
Stefan

Recreate ctx_cpsuser

2010-09-21T23:07:00.001+02:00

Recently we have virtualized a lot of our machines and because of the overhead the virtualization creates we needed more citrix servers to handle the same amount of users we had. We cloned a couple of the citrix servers to get them up quickly and exact the same.

One problem we got was that the printers wouldn´t create and that was because we needed to recreate the ctx_cpsuser account that runs the citrix print manager service.

Here´s an article on how to handle it.

Stefan

SQL Server benchmarking series Introducing SSDs (Part 2: sequential inserts)

2010-07-10T18:29:00.000+02:00

Paul S. Randall has published the second part in benchmarking SSD disks with SQL Server. Great fun reading and these tests are real useful for us who don´t have the equipment our selves for testing.

Read the full blogpost here

How to uninstall a hotfix or Service Pack via the Recovery Console

2010-06-16T14:09:00.000+02:00

I had a problem with a computer after installing Windows XP servicepack 3 and the computer restarted and I couldn´t even log into safemode. Looked around and found this article, which can be useful sometime.

How to uninstall a hotfix or Service Pack via the Recovery Console

Regards
Stefan

SQL Server benchmarking series part 5 and introducing SSD

2010-05-18T23:56:00.002+02:00

You can read the 5th part in the Benchmarking series here.
The first post about the introduction to SSD disks.
The second post about SSD disks.

I recently bought a SSD to my stationary computer and the result were amazing, even the incredibly slow outlook were fast. I will increase the memory in the computer with 2gb and see what the result will be on the performance tests that I did.

Enjoy the reading I do.

Regards
Stefan

Top 5 things to get rid of in SQL Server?!

2010-05-18T23:51:00.000+02:00

Paul Randall wrote a blogpost about the top 5 things that he want to get rid of in sql server, and not surpisingly auto shrink was the top one.

You can read the full post and comments here

Regards
Stefan

SQL Server Benchmarking series part 4

2010-03-15T15:09:00.001+01:00

Paul S. Randall have published the 4 part in the benchmarking series. You can find the blogpost Here

Happy reading
Stefan

3 ways to recover a corrupted Excel workbook

2010-02-19T12:39:00.000+01:00

Found this tip on HP´s website. Could be useful some day.

Stefan

If a file is corrupted, Excel should normally perform an automated recovery. However, if that doesn’t work, there are a few other options you can try:

1. Recover or repair the file manually with Excel
The steps for manually recovering a workbook are quite simple:

1. Select “Open” from the File menu. In Excel 2007, click the Office icon button and select “Open”.
2. In the “Open” window, locate and specify the corrupted workbook.
3. From the Open button’s dropdown list, select “Open and Repair”.
4. If this is your first attempt to manually recover the workbook, click Repair in the dialogue box that appears.

Usually Excel will be able to recover the entire file, but it doesn’t always work. If you’ve tried this process and you can’t recover the entire file, you can still at least recover your data. To do so, click Extract Data in step 4.

2. Use the last saved version
If the file becomes corrupted while you’re working in the workbook, DON’T save the file. Instead, revert to the last saved version of the file. Doing so without saving first should get rid of whatever was corrupting the file. Be forewarned – you could possibly lose some data, but if you’ve been regularly saving your work (as you should be!) this is a lot better than losing the entire file.

To revert to the last saved version:

1. Choose “Open”from the File menu. In Excel 2007, click the Office icon button and select “Open.”
2. In the “Open” window, locate and specify the corrupted workbook.
3. Click Open.

3. Try opening the file in another program
If Excel can’t open the corrupted workbook, there are several other programs you can use to try to open it. Microsoft Tools is one option:

1. From the Microsoft® Windows® XP Start menu, choose “All Programs.”
2. Select Microsoft Office > Microsoft Office Tools > Microsoft Office Application Recovery.
3. In the dialogue box that appears, click Microsoft Office Excel.
4. Click Recover Application.

You can also try opening the workbook in Microsoft WordPad. The only caveat: WordPad will convert all the data into text, and it won’t recover formulas. However, it will at least restore your important data. It will also recover your VBA procedures (macros) – just search recovered text for “Sub” and “Function” to find them.

You may also be able to open the corrupted .xls file in Word, but again, the data is the only thing you’ll be able to recover − and even that may give you mixed results. So only try this as a last resort!

For more Excel troubleshooting tips and tricks, visit the Microsoft Excel home page.

SQL Server benchmarking series

2010-02-16T21:45:00.000+01:00

Paul S. Randall has published his 3:rd article in the benchmarking series. I really recommend it to you all.

SQL Server benchmarking series

Happy reading

Regards
Stefan

SQL Server agent alerts

2010-02-01T23:09:00.005+01:00

I reread an article by Paul S. Randall tonight about sql agent alerts and the error 825 which points to problems with the hardware, and started browsing around for other good articles about alerting.

I also found a great article by Tibor Karaszi (former teacher of mine :-)) , he have created a utility for creating Alerts on different severity levels.

As the sql server pro´s, say at least configure 823, 824 and as Paul suggest in the article above 825 so you catch the errors before they reach 823 and 824.

Regards
Stefan

SQL Server benchmarking series

2010-01-29T19:21:00.000+01:00

Paul S. Randall is making a benchmarking series of SQL Server performance. I can highly recommend it, very interesting and educating.

http://www.sqlskills.com/blogs/paul/default.aspx

Regards
Stefan

SQL Server Maintenance schedule

2010-01-26T11:02:00.002+01:00

A script to get the SQL Server agent scheduled jobs. Don´t know where I found it but the credit goes to the creator.

Regards
Stefan

DECLARE @weekDay TABLE (
mask INT
, maskValue VARCHAR(32)
);

INSERT INTO @weekDay
SELECT 1, 'Sunday' UNION All
SELECT 2, 'Monday' UNION All
SELECT 4, 'Tuesday' UNION All
SELECT 8, 'Wednesday' UNION All
SELECT 16, 'Thursday' UNION All
SELECT 32, 'Friday' UNION All
SELECT 64, 'Saturday';
WITH myCTE
AS(

SELECT sched.name AS 'scheduleName'
, sched.schedule_id
, jobsched.job_id
, CASE WHEN sched.freq_type = 1 THEN 'Once'
WHEN sched.freq_type = 4
And sched.freq_interval = 1
THEN 'Daily'
WHEN sched.freq_type = 4
THEN 'Every ' + CAST(sched.freq_interval AS VARCHAR(5)) + ' days'
WHEN sched.freq_type = 8 THEN
REPLACE( REPLACE( REPLACE((
SELECT maskValue
FROM @weekDay AS x
WHERE sched.freq_interval & x.mask <> 0
ORDER BY mask FOR XML Raw)
, '"/>
+ CASE WHEN sched.freq_recurrence_factor <> 0
And sched.freq_recurrence_factor = 1
THEN '; weekly'
WHEN sched.freq_recurrence_factor <> 0 THEN '; every '
+ CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' weeks' END
WHEN sched.freq_type = 16 THEN 'On day '
+ CAST(sched.freq_interval AS VARCHAR(10)) + ' of every '
+ CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' months'
WHEN sched.freq_type = 32 THEN
CASE WHEN sched.freq_relative_interval = 1 THEN 'First'
WHEN sched.freq_relative_interval = 2 THEN 'Second'
WHEN sched.freq_relative_interval = 4 THEN 'Third'
WHEN sched.freq_relative_interval = 8 THEN 'Fourth'
WHEN sched.freq_relative_interval = 16 THEN 'Last'
END +

CASE WHEN sched.freq_interval = 1 THEN ' Sunday'
WHEN sched.freq_interval = 2 THEN ' Monday'
WHEN sched.freq_interval = 3 THEN ' Tuesday'
WHEN sched.freq_interval = 4 THEN ' Wednesday'
WHEN sched.freq_interval = 5 THEN ' Thursday'
WHEN sched.freq_interval = 6 THEN ' Friday'
WHEN sched.freq_interval = 7 THEN ' Saturday'
WHEN sched.freq_interval = 8 THEN ' Day'
WHEN sched.freq_interval = 9 THEN ' Weekday'
WHEN sched.freq_interval = 10 THEN ' Weekend'
END

+ CASE WHEN sched.freq_recurrence_factor <> 0
And sched.freq_recurrence_factor = 1 THEN '; monthly'
WHEN sched.freq_recurrence_factor <> 0 THEN '; every '
+ CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' months' END
WHEN sched.freq_type = 64 THEN 'StartUp'
WHEN sched.freq_type = 128 THEN 'Idle'
END AS 'frequency'
, ISNULL('Every ' + CAST(sched.freq_subday_interval AS VARCHAR(10)) +
CASE WHEN sched.freq_subday_type = 2 THEN ' seconds'
WHEN sched.freq_subday_type = 4 THEN ' minutes'
WHEN sched.freq_subday_type = 8 THEN ' hours'
END, 'Once') AS 'subFrequency'
, REPLICATE('0', 6 - LEN(sched.active_start_time))
+ CAST(sched.active_start_time AS VARCHAR(6)) AS 'startTime'
, REPLICATE('0', 6 - LEN(sched.active_end_time))
+ CAST(sched.active_end_time AS VARCHAR(6)) AS 'endTime'
, REPLICATE('0', 6 - LEN(jobsched.next_run_time))
+ CAST(jobsched.next_run_time AS VARCHAR(6)) AS 'nextRunTime'
, CAST(jobsched.next_run_date AS CHAR(8)) AS 'nextRunDate'
FROM msdb.dbo.sysschedules AS sched
JOIN msdb.dbo.sysjobschedules AS jobsched
ON sched.schedule_id = jobsched.schedule_id
WHERE sched.enabled = 1
)

SELECT job.name AS 'jobName'
, sched.scheduleName
, sched.frequency
, sched.subFrequency
, SUBSTRING(sched.startTime, 1, 2) + ':'
+ SUBSTRING(sched.startTime, 3, 2) + ' - '
+ SUBSTRING(sched.endTime, 1, 2) + ':'
+ SUBSTRING(sched.endTime, 3, 2)

AS 'scheduleTime' -- HH:MM
, SUBSTRING(sched.nextRunDate, 1, 4) + '/'
+ SUBSTRING(sched.nextRunDate, 5, 2) + '/'
+ SUBSTRING(sched.nextRunDate, 7, 2) + ' '
+ SUBSTRING(sched.nextRunTime, 1, 2) + ':'
+ SUBSTRING(sched.nextRunTime, 3, 2) AS 'nextRunDate'

/* Note: the sysjobschedules table refreshes every 20 min,
so nextRunDate may be out of date */
, 'Execute msdb.dbo.sp_update_job @job_id = '''
+ CAST(job.job_id AS CHAR(36)) + ''', @enabled = 0;' AS 'disableScript'
FROM msdb.dbo.sysjobs AS job
JOIN myCTE AS sched
ON job.job_id = sched.job_id
WHERE job.enabled = 1 -- do not display disabled jobs
ORDER BY nextRunDate;

Backup sql express database

2010-01-18T12:19:00.000+01:00

How do you backup a SQL 2005 express database? Since it doesn´t come with sql server agent you need to script it if you want to schedule the backup. When I researched how to do it I found the following excellent article: Express backup

Regards
Stefan

Job Owner Reverts to Previous Owner when Scheduled Maintenance Plan is Edited

2010-01-15T10:47:00.000+01:00

Recently I inherited a Database from a retired DBA and after a while the maintenance jobs started to fail. When i excamined the log I got the following error:

The job failed. Unable to determine if the owner (domain\username) of job Log Backup Databasename.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user 'domain\username', error code 0x2. [SQLSTATE 42000] (Error 15404)).

Ok fine with that because I knew the account was disabled. I think no problem so I change the owner of the job, but no no. I ran the job with success after changing the owner of the job to sa, but when the schedule ran the job later at night it failed!

Why did it fail again? After examining the log I saw the error above was there again, how could that be I changed it the day before?

After some thinking it came back to me, after running the job successfully I edited the job and made a modification. When I saved the job it reverted back to the owner who created it from start.....

To fix the problem use the script below on sql 2005 if you set the dbo as owner you won´t have any problems with DBA´s leaving the company.

/*Here's how to change the owner of a maintenance plan to dbo in SQL Server 2005*/
--to find the name and owner of the maintenance plan
select * from msdb.dbo.sysdtspackages90
--to find the sid you want to use for the new owner
select * from sysusers

/*To update the owner to dbo use the script below
UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01
WHERE
[name] = 'MaintenancePlan'

Regards
Stefan

Check database recovery model

2010-01-15T10:33:00.001+01:00

A small script to check the database recovery model. Can be handy sometime.

SELECT name, databasepropertyex (name,'Recovery') FROM master..sysdatabases

Regards
Stefan

How to identify your SQL Server version and edition

2009-11-26T10:02:00.000+01:00

A useful sqlquery to determine which SQL version is installed on the server.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Stefan

Rename Database

2009-09-18T10:35:00.003+02:00

I had to rename a database in our testenvironment and the procedure to do it is like this:

Use the following T-SQL: command to make the database name change.
EXEC sp_renamedb 'oldName', 'newName'
This command works for both SQL Server 2000 and SQL Server 2005, but this feature will not be supported in future versions of SQL Server.

The new command that should be used for SQL Server 2005 and beyond is:
ALTER DATABASE oldName MODIFY NAME = newName

The full article can be read at http://www.mssqltips.com/tip.asp?tip=1122

Stefan

SQL Jobs monitoring

2009-09-05T00:45:00.002+02:00

We are montioring our sql servers for failed jobs and get notice of it in to our helpdesk system, however if you don´t have a monitoring system this article can be a good help for you.

http://www.sqlservercentral.com/articles/SQL+Server+2005/61887/

Regards
Stefan

windows 7

2009-08-17T22:23:00.002+02:00

There are a lot of news nowadays on the MS OS front. Windows 7 is made available a week ago on technet/msdn and for enterprise customers. A couple of days ago windows 2008 R2 was made available on the same channels.

I installed windows 7 when it was available on technet and started to discover it. Here are some goodies on the aero interface that Jason Zandri wrote about in an article on petri.co.il.
http://www.petri.co.il/new-aero-features-in-windows-7.htm

I can add the excellent show off feature windows key + tab.

Enjoy
Stefan

2009-08-06T12:55:00.003+02:00

A user lost the password to a database login and when i changed the password I got the following message in the face.

Msg 15128, Level 16, State 1, Line 1The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.

The solution is to change the password using the following query and then remove the flag.

ALTER LOGIN MyLoginName WITH PASSWORD='yourpassword'

SQL Backup Monitoring and Reporting

2009-06-04T23:19:00.003+02:00

A task that all DBA´s has is backup and restore and to ensure they works. I found an excellent article by Chad Miller on Sqlservercentral that provides great scripts and a good explanation how it works. Enjoy and make use of it in your environment.

http://www.sqlservercentral.com/articles/Backup/66564

Regards
Stefan

Database restore last time

2009-04-18T01:20:00.003+02:00

Ever wanted to know when your database was last restored and from where? I found the script to do it on mssqltips.com
The information you will get is the following:
Restored database, restored by, restore type, restore start, restored from and restored to.
You never know when your boss gets really crazy and wants to know everything and then this can get useful and sometime it will get useful to you as well.

Stefan

Linked Oracle servers on x64

2009-04-18T00:19:00.002+02:00

A couple of weeks ago i installed a server with windows 2003 x64 and SQL 2005 for one of our production servers that will be migrated to a new domain. In the old domain it was installed on a x86 system but now it had grown so much so we needed the extra memory usage of x64.
The system is running fine on x64 but we have a linked server to a oracle database and that wouldn´t work when we set it up in the same way as on the x86 system. After some googling I found a excellent article on mssqltips.com which explains every steps on the way and it works perfectly.

Regards
Stefan

Maintenance script

2009-03-14T16:45:00.004+01:00

I found a great maintenance script for backup, integrity check and index optimization in SQL Server 2005 and SQL Server 2008. The solution is based on stored procedures, functions, sqlcmd and SQL Server Agent jobs.

You can read about it and download it here

Paul S Randall has an interesting article here about validating backups. It´s good to have a reminder about that sometimes, even if we know how important it is.

Regards
Stefan

Default trace in SQL Server 2005

2009-03-09T23:08:00.003+01:00

Have you ever had a problem with your sql server and the boss wanted to know who or what was responsible?
Of course you didn´t have a trace running, because you haven´t had any problems with the DB before.
You are not completely lost if you haven´t disabled the default trace which is on by default. The following query will tell you if it´s enabled or not.
select * from sys.configurations where configuration_id = 1568

In the following articles you will find a good beginners guide and which events are captured in the default trace.
http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/#
http://blogs.technet.com/vipulshah/archive/2007/04/16/default-trace-in-sql-server-2005.aspx

Regards
Stefan

Too many VLF:s or to few?

2009-02-11T19:53:00.003+01:00

I found an excellent blog post by Kimberly L. Tripp yesterday about transaction log performance and VLF:s. I later investigated a couple of our own databases that I have taken over and they are clearly in need of a little bit more maintenance then I have done so far.
The recommendations are maybe around 50 or a little bit more depending on the size of the db/log. I found out that we had over 3000 VLF:s in one medium sized database. This is however quite easy to fix. The secret is capacity planning to avoid this problem.

Here are the full article from Kimberly.

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

Happy reading
Stefan

Moving tempdb Error

2009-01-26T23:51:00.002+01:00

Here´s a good article about when you fuck up with the move of tempdb, forgetting the file extension and the Sqlserver won´t start.

http://jmkehayias.blogspot.com/2009/01/error-5123-severity-16-state-1-when.html

Stefan

Extending partitions on vmware server with diskpart

2008-10-21T16:19:00.004+02:00

I had to extend a virtual disk on a vmware server and this is the procedure to do it.

Shut down the virtual machine.
Navigate to the VMware application's installation directory and open a commandprompt.
Locate the full path to a hosted (non-ESX Server host) virtual machine's virtual disk (.vmdk) file.
Type vmware-vdiskmanager -x 100Gb vm.vmdk and press Enter (replace vm.vmdk with the actual filename and path. If you have multiple .vmdk files with the same base name, use the file that does not include "-flat" or "-s0" in its file name). If you want a different size on the disk, just change the GB to your needs.
http://support.microsoft.com/kb/325590 describes how to use diskpart.

Enjoy the extra space you have on the virtual machine. Of course diskpart works with physical disks as well.

Stefan

What to do when the SA account password is lost in SQL Server 2005

2008-10-05T14:21:00.001+02:00

I found this article on Raul Garcia's blog and it could really be useful some time.

You may have faced the issue of losing the SQL Server SA password. Perhaps you followed the security best-practice of removing the builtin\Administrators from the sysadmin server role, and no one you can find is in the sysadmin role. At this point you may think that your only options are to reinstall SQL Server and attach the databases, or to directly access the master database files, which may potentially damage the data.

SQL Server 2005 provides a better disaster recovery option for this scenario that is non-intrusive for master DB and that will help you preserve any objects and data stored in master DB (such as logins, certificates, Service Master Key, etc.) intact. Members of the Windows Administrators group now have access to SQL Server when SQL Server is in started in single-user mode, also known as “maintenance mode “.

Using the single-user mode, SQL Server 2005 prevents a Windows Administrator to abuse this privilege to act on behalf of the sysadmin without being noticed. This allows Windows Administrator accounts to perform certain maintenance tasks, such as installing patches.

In order to start SQL Server in single-user mode, you can add the parameter “-m” at the command line. You can also use the SQL Server Configuration Manager tool, which provides proper controls for the file access and other privileges. To use the Configuration Manager tool to recover your system, use the following steps:

1. Open the Configuration Manager tool from the "SQL Server 2005 Configuration" menu
2. Stop the SQL Server Instance you need to recover
3. Navigate to the “Advanced” tab, and in the Properties text box add “;–m” to the end of the list in the “Startup parameters” option
4. Click the “OK” button and restart the SQL Server Instance

NOTE: make sure there is no space between “;” and “-m”, the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user mode.”

5. After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as "sp_addsrvrolemember" to add an existing login (or a newly created one) to the sysadmin server role.
The following example adds the account "Buck" in the "CONTOSO" domain to the SQL Server "sysadmin" role:

EXEC sp_addsrvrolemember 'CONTOSO\Buck', 'sysadmin';
GO

6. Once the sysadmin access has been recovered, remove the “;-m” from the startup parameters using the Configuration Manager and restart the SQL Server Instance

Important Security Notes:
This process should only be used for disaster recovery when no other method to access the system with a privileged (i.e. sysadmin or equivalent) is available.

This process allows a Windows Administrator account to override their privileges within SQL Server. It requires explicit and intrusive actions that can be monitored and detected, including:
· Stop SQL Server and restart it in single use mode
· Connecting to SQL Server using Windows credentials

Upgrading to 2005 and stuff to do afterwards

2008-09-28T23:21:00.002+02:00

This is just a couple of stuff you should do after upgrading to sql 2005 from sql 2000.

Register your servers - Upgrade removes registry settings for the previous SQL Server instance. After upgrading, you must reregister your servers.
Repopulate full-text catalogs - The upgrade process marks your databases as full-text disabled. Catalogs must be repopulated, but this operation is not run automatically by Setup because it can be time-consuming. As this operation enhances the performance of your SQL Server 2005 installation, administrators should plan to repopulate full-text catalogs at a convenient time.
Update statistics - To help optimize query performance, we recommend that you update statistics on all databases following upgrade. Use the sp_updatestats stored procedure to update statistics
in user-defined tables in SQL Server 2005 databases.
Update usage counters - In earlier versions of SQL Server, the values for the table and index row counts and page counts can become incorrect. To correct any invalid row or page counts, we recommend that you run DBCC UPDATEUSAGE on all databases following upgrade.
Configure your new SQL Server installation - To reduce the attackable surface area of a system, SQL Server 2005 selectively installs and activates key services and features. For more information on how to activate SQL Server 2005 features, see SQL Server Surface Area Configuration [ http://technet.microsoft.com/en-us/library/ms173748(printer).aspx ] .

Just use the sql books online to get examples that u can follow.

Regards
Stefan

Top 10 hottest IT jobs in Sweden

2008-08-15T00:27:00.002+02:00

This is the top 10 list from IDG in Sweden.

.Net Developer
SAP Consultant
Java Developer
Testing engineer
Database specialist (my current/future track;-)
System architect
IT Manager (my boss current position ;-))
Exchange Server consultant
Security expert
System engineer? (don´t know if the translation is correct)

If you´re looking for other work you know what to look for.

Regards
Stefan

Circumventing Group Policy Settings

2008-07-21T15:24:00.002+02:00

Sometimes it can good to be able to circumvent group policy settings, and if you are a local admin on your computer it is no problem. Read the article on Mark Russinovich´s blog.

Circumventing Group Policy Settings

Regards
Stefan

Rename logical file names

2008-07-04T13:27:00.008+02:00

When we changed a db server I wanted to clean up the file names that wasn´t consistent so here is the procedure in a MS Support article for changing the logical file names on sql databasefiles.

Rename Logical File Name

Regards
Stefan

How to transfer logins and passwords between instances of SQL Server

2008-06-30T20:22:00.002+02:00

This is a must know procedure if you are DBA or try to become one.

How to transfer logins and passwords between instances of SQL Server

Regards
Stefan

Rename a SQL Server or MS failover cluster.

2008-06-30T17:28:00.008+02:00

Here are a couple of good articles about renaming a SQL Server or cluster

Rename a Computer that Hosts a Stand-Alone Instance of SQL Server 2005

Summary about renaming a computer that hosts a stand-Alone instance of sql server 2005:
When you change the name of the computer that is running Microsoft SQL Server 2005, the new name is recognized during SQL Server startup. You do not have to run Setup again to reset the computer name. The following steps cannot be used to rename an instance of SQL Server 2005. These steps can be used only to rename the part of the instance name that corresponds to the computer name. For example, you can change a computer named MB1 that hosts an instance of SQL Server named Instance1 to another name, such as MB2. However, the instance portion of the name, Instance1, will remain unchanged. In this example, the \\ComputerName\InstanceName would be changed from \\MB1\Instance1 to \\MB2\Instance1.

You can connect to SQL Server using the new computer name after you have restarted SQL Server. However, to ensure that @@servername returns the updated name of the local server instance, you should manually run one of the following procedures, depending on whether you are updating a default or named instance

For a renamed default instance, run the following procedures:
sp_dropserver
GO
sp_addserver , local
GO
Restart the SQL Server instance.

For a renamed named instance, run the following procedures:

sp_dropserver 'old_name\instancename'
GO
sp_addserver 'new_name\instancename', local
GO

Restart the SQL Server instance.

To verify that the renaming operation has completed successfully:
Select information from either @@servername or sys.servers. The @@servername function will return the new name, and the sys.servers table will show the new name.

select @@servername

If the computer has any remote logins, running sp_dropserver may generate an error similar to this:
Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44There are still remote logins for the server 'SERVER1'.

To drop remote logins for a default instance, run the following procedure:
sp_dropremotelogin old_name
GO

To drop remote logins for a named instance, run the following procedure:
sp_dropremotelogin old_name\instancename
GO

Renaming a cluster (virtual server) is really quite easy, just do the following:

Start Cluster Administrator, right-click the Network Name resource, and then click Take Offline.Note Access to the VirtualServer by clients cannot now occur because the Network Name resource is offline.
Double-click the Network Name resource where you want to enable the DNS Registration Must Succeed option, and then click the Parameters tab.
Click the DNS Registration Must Succeed option, click OK, right-click the Network Name resource, and then click Bring Online. When the Network Name resource comes online, it will verify that it can register the VirtualServer with the DNS server.

Note: The Network Name is registered in DNS under the Cluster service account. Make sure that the Cluster service account has correct permissions to register records in DNS or the registration will not work.
However renaming a SQL Server 20000 Virtual Server is not supported by Microsoft
Rename a SQL Server 2005 Virtual Server seems to be a little bit easier
Regards
Stefan

Getting more memory for SQL server

2008-06-26T07:46:00.005+02:00

If you´re stuck on a 32Bit system and need more memory recources for your database there´s the option to use either the /3GB switch in boot.ini or /PAE together with AWE. Here´s the procedure to make this work.

First, here are the memory limitations for a 32Bit windows server 2003:

Windows Server 2003, Standard Edition supports physical memory up to 4 GB.
Windows Server 2003, Enterprise Edition supports physical memory up to 32 GB.
Windows Server 2003, Datacenter Edition supports physical memory up to 64 GB.

If you use windows server 2003 standard edition and have 4GB in your box you can use the /3GB switch to get 1GB extra for sqlserver.

Edit the boot.ini file and add /3GB to the following string: multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Standard" /fastdetect /3GB /NoExecute=OptOut
Open up sqlserver enterprise manager and rightclick the server and click properties.
Go to the memory tab and tick the checkbox "lock pages in memory" and then click ok.
After this you need to allow the user that is running sqlserver.exe to lock pages into the memory. To do it do the following:
On the Start menu, click Run. In the Open box, type gpedit.msc.
The Group Policy dialog box opens.
On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
In the pane, double-click Lock pages in memory.
In the Local Security Policy Setting dialog box, click Add.
In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.
When you have done this reboot the server and enjoy the extra 1Gb you got for sqlserver.

If you´re using Windows Server 2003, Enterprise Edition or Datacenter Edition you can take advantage of AWE, and for activating on a box with 8GB memory you do the following:

Instead of adding the /3GB switch you add /PAE to boot.ini
Open up sqlserver enterprise manager and rightclick the server and click properties.
Go to the memory tab and tick the checkbox "lock pages in memory" and then click ok.
After this you need to allow the user that is running sqlserver.exe to lock pages into the memory. To do it do the following:
On the Start menu, click Run. In the Open box, type gpedit.msc. The Group Policy dialog box opens. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.Expand Security Settings, and then expand Local Policies.Select the User Rights Assignment folder. The policies will be displayed in the details pane. In the pane, double-click Lock pages in memory.In the Local Security Policy Setting dialog box, click Add.In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.
Now we will configure the AWE with the following code which you run from a query window in enterprise manager:

First we show the advanced options

sp_configure 'show advanced options', 1
RECONFIGURE
GO

Then we enable AWE

sp_configure 'awe enabled', 1
RECONFIGURE
GO

Then we set the min and max values so we leave some memory for the operating system.

sp_configure 'min server memory', 1024
RECONFIGURE
GO
sp_configure 'max server memory', 7168
RECONFIGURE
GO

After this you reboot the server and enjoy the extra memory and hopefully your sqlserver will run a lot smoother with the extra memory available.

Note. you should read the following articles before making any changes to your production environment and not take my word for it. Be careful!

Regards
Stefan

Using AWE
Enabling AWE Memory for SQL Server
How to: Enable the Lock Pages in Memory Option (Windows)
Enabling Memory Support for Over 4 GB of Physical Memory

Best Practices for Recovering a Database to a Specific Recovery Point

2008-06-25T18:31:00.003+02:00

I read in an article about how to become a DBA and what tasks are important to be able to perform and of course a solid backup plan of the db is the main thing. Everything else than that is bonuses for the company.

If you have a backup that works you also need to know how to restore it and here´s a good article from books online about this.

"Best Practices for Recovering a Database to a Specific Recovery Point
This topic is relevant only for databases that use the full or bulk-logged recovery models.
This topic presents some best practices for restoring a database to a specific recovery point.
Use STANDBY to Find Unknown Point in Time

Sometimes, you want to restore a database to a specific recovery point, but you do not know the target time, LSN, or marked transaction, if any. One solution is to incrementally roll forward the primary data file and, optionally, other data files by specifying WITH STANDBY = standby_file_name instead of WITH RECOVERY. Using the STANDBY option recovers the database for read-only access. This lets you read the database and look for the point-in-time information that you want. The standby file lets you undo the effects the recovery process, in case the database is rolled forward beyond the target point-in-time. If the database has not yet reached your target point in time, you can restore successive log backups using WITH STANDBY, specifying the same standby file in each restore statement.
After you have identified the target recovery point, you can roll forward again to that point in time. On reaching the target point in time, restore the partial database using the RECOVERY option. The remaining files, if any, can then be restored and recovered to be consistent with the database. After you restore the last full log restore, restore the first log backup taken after the target recovery point.
For more information about the STANDBY option, see RESTORE Arguments (Transact-SQL).
Specify the Point in Time Early in a Restore Sequence

A restore sequence consists of one or more restore operations that move data through one or more of the phases of restore. The more information that you supply about the intent of the sequence, the less likely you are to experience errors that could require that you start over. For point-in-time restore, the SQL Server Database Engine uses information specified at the start of (and during) a restore sequence to identify the point-in-time goal of the restore. If you wait too long in the sequence, the sequence fails. You must provide the stop-at information early enough in the restore sequence to make sure that restore does not go beyond your point-in-time goal.
If you intend to stop at a particular recovery point, you should communicate this intention on every RESTORE LOG statement (by using STOPAT, STOPBEFOREMARK, or STOPATMARK), together with the RECOVERY option. When the RECOVERY and STOPAT options are used together, if the transaction log backup does not contain the requested time (for example, if the time specified is beyond the end of the time covered by the transaction log), a warning is generated and the database remains unrecovered. When the recovery point is reached, the database is recovered, and an attempt to restore another log backup fails. Using the RECOVERY and STOPAT options together when restoring a log backup ensures that no log restore ever goes beyond the stop-at time.
Example: Point-in-Time Restore
In the following example, the restore sequence starts to declare the intention to stop at a particular time when applying the first log backup. In this example, the stop-at time occurs in the first log backup after the differential backup.
Copy CodeRESTORE DATABASE database_name FROM full_backup
WITH NORECOVERY;
RESTORE DATABASE database_name FROM full_differential_backup
WITH NORECOVERY;
RESTORE LOG database_name FROM log_backup
WITH STOPAT = time, RECOVERY;
RESTORE LOG database_name FROM log_backup
WITH STOPAT = time, RECOVERY;
Whether a specified log restore succeeds depends on whether time is in the interval captured by a log backup, as described in the following table.
Relationship of time to the interval captured by the log backup:
Effect
time is before the interval.
The restore fails, and no roll forward occurs.
time is during the interval.
The last restore succeeds, and the database is recovered.
time is after the interval.
Roll forward succeeds, but the database is not recovered, because time has not yet been reached.
Declare the Stopping Point on Every Subsequent RESTORE Statement

A stopping point is specific to the statement that specifies the STOPAT, STOPBEFOREMARK, or STOPATMARK option. If you omit that option from a RESTORE statement, the complete backup is restored.
As a point-in-time restore sequence progresses, you can change the recovery point by specifying a new value for time, assuming that the database has not yet gone beyond the new time.
Note:
The STOPBEFOREMARK and STOPATMARK options have two parameters, mark_name and lsn_number. The mark_name parameter, which identifies a transaction mark in a log backup, is supported only in RESTORE LOG statements. The lsn_number parameter, which specifies a log sequence number, is supported in both RESTORE DATABASE statements and RESTORE LOG statements. "

Regards
Stefan

Regedit from Internet Explorer?

2008-06-02T22:42:00.003+02:00

Here comes a tip on a small ie add-on for reaching a registry key in regedit directly from internet explorer via a right click.

http://www.petri.co.il/open_in_regedit_in_ie.htm

Stefan

How can I transfer some or all of the FSMO Roles from one DC to another?

2008-05-31T17:48:00.002+02:00

This article I found on Petri´s website.

The transfer of an FSMO role is the suggested form of moving a FSMO role between domain controllers and can be initiated by the administrator or by demoting a domain controller. However, the transfer process is not initiated automatically by the operating system, for example a server in a shut-down state. FSMO roles are not automatically relocated during the shutdown process - this must be considered when shutting down a domain controller that has an FSMO role for maintenance, for example.
In a graceful transfer of an FSMO role between two domain controllers, a synchronization of the data that is maintained by the FSMO role owner to the server receiving the FSMO role is performed prior to transferring the role to ensure that any changes have been recorded before the role change.
However, when the original FSMO role holder went offline or became non operational for a long period of time, the administrator might consider moving the FSMO role from the original, non-operational holder, to a different DC. The process of moving the FSMO role from a non-operational role holder to a different DC is called Seizing, and is described in the Seizing FSMO Roles article.
You can transfer FSMO roles by using the Ntdsutil.exe command-line utility or by using an MMC snap-in tool. Depending on the FSMO role that you want to transfer, you can use one of the following three MMC snap-in tools:
Active Directory Schema snap-in
Active Directory Domains and Trusts snap-in
Active Directory Users and Computers snap-in
To transfer the FSMO role the administrator must be a member of the following group:
FSMO Role
Administrator must be a member of
Schema
Schema Admins
Domain Naming
Enterprise Admins
RID
Domain Admins
PDC Emulator
Infrastructure
Transferring the RID Master, PDC Emulator, and Infrastructure Masters via GUI
To Transfer the Domain-Specific RID Master, PDC Emulator, and Infrastructure Master FSMO Roles:
Open the Active Directory Users and Computers snap-in from the Administrative Tools folder.
If you are NOT logged onto the target domain controller, in the snap-in, right-click the icon next to Active Directory Users and Computers and press Connect to Domain Controller.
Select the domain controller that will be the new role holder, the target, and press OK.
Right-click the Active Directory Users and Computers icon again and press Operation Masters.
Select the appropriate tab for the role you wish to transfer and press the Change button.
Press OK to confirm the change.
Press OK all the way out.
Transferring the Domain Naming Master via GUI
To Transfer the Domain Naming Master Role:
Open the Active Directory Domains and Trusts snap-in from the Administrative Tools folder.
If you are NOT logged onto the target domain controller, in the snap-in, right-click the icon next to Active Directory Domains and Trusts and press Connect to Domain Controller.
Select the domain controller that will be the new role holder and press OK.
Right-click the Active Directory Domains and Trusts icon again and press Operation Masters.
Press the Change button.
Press OK to confirm the change.
Press OK all the way out.
Transferring the Schema Master via GUI
To Transfer the Schema Master Role:
Register the Schmmgmt.dll library by pressing Start > RUN and typing:
regsvr32 schmmgmt.dll
Press OK. You should receive a success confirmation.
From the Run command open an MMC Console by typing MMC.
On the Console menu, press Add/Remove Snap-in.
Press Add. Select Active Directory Schema.
Press Add and press Close. Press OK.
If you are NOT logged onto the target domain controller, in the snap-in, right-click the Active Directory Schema icon in the Console Root and press Change Domain Controller.
Press Specify .... and type the name of the new role holder. Press OK.
Right-click right-click the Active Directory Schema icon again and press Operation Masters.
Press the Change button.
Press OK all the way out.
Transferring the FSMO Roles via Ntdsutil
To transfer the FSMO roles from the Ntdsutil command:
Caution: Using the Ntdsutil utility incorrectly may result in partial or complete loss of Active Directory functionality.
On any domain controller, click Start, click Run, type Ntdsutil in the Open box, and then click OK.
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\WINDOWS>ntdsutil
ntdsutil:
Type roles, and then press ENTER.
ntdsutil: roles
fsmo maintenance:
Note: To see a list of available commands at any of the prompts in the Ntdsutil tool, type ?, and then press ENTER.
Type connections, and then press ENTER.
fsmo maintenance: connections
server connections:
Type connect to server , where is the name of the server you want to use, and then press ENTER.
server connections: connect to server server100
Binding to server100 ...
Connected to server100 using credentials of locally logged on user.
server connections:
At the server connections: prompt, type q, and then press ENTER again.
server connections: q
fsmo maintenance:
Type transfer . where is the role you want to transfer.
For example, to transfer the RID Master role, you would type transfer rid master:
Options are:
Transfer domain naming master
Transfer infrastructure master
Transfer PDC
Transfer RID master
Transfer schema master
You will receive a warning window asking if you want to perform the transfer. Click on Yes.
After you transfer the roles, type q and press ENTER until you quit Ntdsutil.exe.
Restart the server and make sure you update your backup.

Database stuck in a restoring state

2008-05-27T12:41:00.002+02:00

During our scheduled restore of a database to a test server there were some kind of network maintenance which caused the restoring server to loose network connection and contact with the .bak-files. This caused the restore to be hanging in a restore state which I couldn´t break even if I restarted the SQL service. I tried to run "restore database dbname with recovery" but that didn´t work either.

The solution was to drop the database ("drop database dbname") and then restore from a fresh copy with the regular script.

The Case of the Missing AutoPlay and Circumventing Group Policy Settings

2008-05-25T23:45:00.002+02:00

Two great articles by Mark Russinovich about using the sysinternals process explorer and filemon/regmon.

The Case of the Missing AutoPlay
Circumventing Group Policy Settings

I can also really recommend his blog

Stefan

Installing Remote Server Administrative Tools on Windows Vista

2008-04-28T22:00:00.002+02:00

Finally the RSAT is released for Vista SP1 for remote server administration. In this article from Daniel Petri he explains how to install it. Read the full article here.

Regards
Zelis

Solving a pesky resource problem in Vista

2008-03-29T17:38:00.003+01:00

I found this tip on Ed Botts blog on zdnet, and it can be useful some time for people who has a large numbers of windows open on Windows Vista.

Have you experienced any of these problems with Windows Vista? After opening a large number of programs and windows, you try to launch a new program or open a new browser tab or even switch back to an already running program and instead:
You get a strange “out of memory” message, despite the fact that you’re using only a fraction of the RAM installed on your system.
The window opens but its contents refuse to load.
The window opens, but menus are missing, dialog boxes are empty, or buttons don’t work.
In my case, I experienced this problem regularly on multiple Vista systems when I opened Outlook 2007, Forte Agent, Adobe Acrobat (editing three or four large documents), BlogJet, Windows Live Writer, Word (working with several large documents), and IE7 with more than 30 tabs. The problem vanished for me, as it does for most people, when I closed a few windows, but that’s obviously not the ideal solution. After all, what’s the point of having all that RAM if you can’t use it?
The problem, as it turns out, is as old as the Windows NT family. I’ve found references to this issue that date back to the mid-1990s and Windows NT 3.1 and 3.5. The fix for Vista, just as for those much older versions of Windows, involves editing a key in the Windows Registry.
First the problem: Windows sets aside a blob of system memory called the desktop heap, which it uses to store user interface objects such as windows, menus, and hooks. The Microsoft Advanced Windows Debugging and Troubleshooting Blog offers a dense, but still readable explication of the problem and why it occurs (it’s a two-part series: read the Desktop Heap Overview first and if your eyes haven’t glazed over read the shorter Desktop Heap Part 2 for details that are specific to 64–bit Windows, systems with 3GB of RAM, and Windows Vista).
The fix for 32–bit Windows Vista is simple: The interactive desktop heap size needs to be bumped up to a value greater than its default setting of 3072KB. I recommend a conservative approach: increase the value to 4096 and try that for a while. If you continue to bump into the problem, try a higher value. On one system here, I’ve been running without issues using a value of 8192KB.
Before I explain how to make the change, I offer the following disclaimers: Editing the Registry is not a trivial task. If you make a mistake, or if your system doesn’t work the way mine does, you could end up causing damage to data or render your system unbootable. You do this at your own risk.
OK, with that out of the way, here are the step-by-step instructions:
Click Start, type regedit in the Search box, and click the Regedit icon that appears at the top of the Start menu.
Click OK in response to the UAC prompt.
In Registry Editor, navigate to the following key:HKEY_LOCAL_MACHINE_SYSTEM_ CurrentControlSet_Control_Session Manager_SubSystems
In the contents pane to the right, double-click the Windows value from the bottom of the list. This opens an Edit String dialog box containing a very long text string.
Scroll through this text value until you find the section that begins with SharedSection. Change the second value from its default of 3072 to a higher number. Do not change any other values.

Restart the computer. If your experience is like mine, you’ll find that those odd error messages are gone and that you’re able to open many more windows without any display issues.

Automatically Create an Outlook Profile on Logon

2008-03-27T21:15:00.002+01:00

You do need to take care of the clients sometime and then this tip can become handy.
Automatically Create an Outlook Profile on Logon

Stefan

The SQL configuration for SQL is inaccessible or invalid

2008-03-19T10:15:00.001+01:00

Event id: 1 Source: SQLBrowser

In my case, this event occurred after the SQL Server 2000 databases had been deleted from the same box where SQL Server 2005 was installed as well. The SQL Browser service is part of the SQL Server 2005 and it was still looking for them. After a restart of the sql browser service, the error was gone.

SQL Server Alias

2008-03-17T23:39:00.003+01:00

If you need to install a named instance and point your client to that one you can do in the following way. The full article can be read below on MS, I have only supplied the 2005 way.

How to connect to a named instance of SQL Server 2005 or SQL Server 2000 by using the client tools in the earlier version of SQL Server

Configure a server alias to use TCP/IP sockets

Note To configure a server alias to use TCP/IP sockets, you must provide the server name and the TCP/IP port number.
1.
Determine the TCP/IP port number of the instance of SQL Server. SQL Server 2005
a.
Open SQL Server Configuration Manager, and then expand SQL Server 2005 Network Configuration.
b.
Click Protocols for InstanceName, and then double-click TCP/IP in the right panel.Note InstanceName is a placeholder for the named instance of SQL Server 2005.
c.
On the Protocol tab, notice the value of the Listen All item.
d.
Click the IP Addresses tab:
•
If the value of Listen All is yes, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item under IPAll.
•
If the value of Listen All is no, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item for a specific IP address.Note If the value of the TCP Dynamic Ports item is not set, you must set it yourself. For more information about how to configure a server to listen on a specific TCP port, visit the following Microsoft Developer Network (MSDN) Web site: http://msdn2.microsoft.com/en-us/library/ms177440.aspx (http://msdn2.microsoft.com/en-us/library/ms177440.aspx)
e.
Click OK.

Audit account management

2008-03-11T23:16:00.003+01:00

We had a small incident when a technician added a user to the wrong distribution group and therefore I looked through the helpfile for Auditing account management on DC´s and found some useful codes that we can use through our monitoring software.

Account Management Events
624 A user account was created.
627 A user password was changed.
628 A user password was set.
630 A user account was deleted.
631 A global group was created.
632 A member was added to a global group.
633 A member was removed from a global group.
634 A global group was deleted.
635 A new local group was created.
636 A member was added to a local group.
637 A member was removed from a local group.
638 A local group was deleted.
639 A local group account was changed.
641 A global group account was changed.
642 A user account was changed.
643 A domain policy was modified.
644 A user account was auto locked.
645 A computer account was created.
646 A computer account was changed.
647 A computer account was deleted.
648 A local security group with security disabled was created. Note: SECURITY_DISABLED in the formal name means that this group cannot be used to grant permissions in access checks.
649 A local security group with security disabled was changed.
650 A member was added to a security-disabled local security group.
651 A member was removed from a security-disabled local security group.
652 A security-disabled local group was deleted.
653 A security-disabled global group was created.
654 A security-disabled global group was changed.
655 A member was added to a security-disabled global group.
656 A member was removed from a security-disabled global group.
657 A security-disabled global group was deleted.
658 A security-enabled universal group was created.
659 A security-enabled universal group was changed.
660 A member was added to a security-enabled universal group.
661 A member was removed from a security-enabled universal group.
662 A security-enabled universal group was deleted.
663 A security-disabled universal group was created.
664 A security-disabled universal group was changed.
665 A member was added to a security-disabled universal group.
666 A member was removed from a security-disabled universal group.
667 A security-disabled universal group was deleted.
668 A group type was changed.
684 Set the security descriptor of members of administrative groups.Note: Every 60 minutes on a domain controller a background thread searches all members of administrative groups (such as domain, enterprise, and schema administrators) and applies a fixed security descriptor on them. This event is logged.
685 Name of an account was changed.

Event ID number 2003 is logged in the Application log after you install Windows Server 2003 Service Pack 2

2008-03-10T23:23:00.002+01:00

The solution was found in the following article: http://support.microsoft.com/kb/932813

After installing server 2003 sp2 I got the following warning in the eventlog on a citrix server:

ERROR
Event Source: PerflibEvent Type: WarningEvent ID: 2003Description: The configuration information of the performance library "C:\WINDOWS\system32\perfts.dll" for the "TermService" service does not match the trusted performance library information stored in the registry. The functions in this library will not be treated as trusted.

CAUSE
This problem occurs because the size and the date of the Perfts.dll file in Windows Server 2003 SP2 do not match the size and the date that were stored earlier in the registry for the Perfts.dll file .

RESOLUTION
To resolve this problem, follow these steps after you install Windows Server 2003 SP2:
1. Click Start, click Run, type cmd, and then click OK.
2. At the command prompt, type the following command, and then press ENTER.
lodctr /T:TermServiceNote
This command will modify the size and the date that are stored in the registry for the Perfts.dll file.

MORE INFORMATION
You can safely ignore event 2003. However, when this event is logged, the performance collection of Terminal Services performance counters is slightly slower than before Windows Server 2003 SP2 was installed.

31 SQL Server Tips That Can Save Your Butt

2008-03-07T13:29:00.001+01:00

The text is from InformIT and the full article can be read here.

Want to get the most out of SQL Server 2000? We've collected more than two dozen tips and techniques that can make any DBA's life easier.
Want to get the most out of SQL Server 2000? Here are 31 short-and-sweet tips to make your life just a little bit easier.
Tips 1-10
Full Text Search Needs a Single Unique Index
You can set up a full-text index using system-stored procedures (including sp_fulltext_database, sp_fulltext_table, and so on), or through the SQL Enterprise Manager. To create a full-text index on a table using Enterprise Manager, you select Full-Text Indexing from the Tools menu. This loads the Full-Text Wizard. The wizard leads you through the steps for enabling a full-text index on the table.
It is important to note that full-text search needs a single unique index on a table to work. A composite primary key consisting of two or more columns will not work. If you have tables that do not have a single column unique index, you need to add a new, unique column to the table. Identity columns work well for this. If the table has more than one unique index, use the smallest, most narrow available index to get the best performance. For example, if you had a table with a unique GUID column and a unique integer 4 bytes each, instead of the GUID at 16 bytes each.
Handling Special Tags Used Inside XML Templates
XML templates are handy. You don't want to use URL queries when you have many lines of T-SQL code to execute. However, you do need to be smart about handling the special tags used inside these templates. Enclose your sql:query statements and sql:param values between (known as a CDATA section) to avoid having to manually encode any special characters.
This makes things much easier because it instructs the parser to treat comparison characters such as <>, &, ', and ". Convert them to the strings (known as entities) <, >, &, ', and " when you need to use them as element or attribute values.
XML Parsers Are Case Sensitive
One of SQL Server 2000's major enhancements is the inclusion of native XML support, enabling developers to execute queries that return results as XML-formatted data, rather than standard rowsets. One importamt rule to keep in mind before delving into this much-awaited feature is that XML parsers are case sensitive with respect to element and attribute names. When running your XML data through a parser after you get it back from a SQL Server 2000 query, be sure to remember that in the mind of the parser, is not the same as , nor is Myattribute the same as myattribute.
Viewing Locking Activity with SQL Enterprise Manager
Although locking provides isolation for transactions and helps ensure their integrity, it can also have a significant impact on system performance. Keep transactions as short, concise, and non-interfering as possible. One of your goals should be to define transactions to minimize locking performance problems.
As you view and monitor locking behavior, sometimes you need to see output more directly, or you don't like the way the information is presented. Use SQL Server Enterprise Manager to display locking information. To see the output from the Enterprise Manager, expand the server items, expand the Management folder, expand the Current Activity item, and click on either Locks/Process ID or Locks/Object to display the locking information in SQL Server.
To see more information when viewing the lock activity in Enterprise Manager, be sure to go to the EM View menu and choose the Detail option. EM will then display detailed information about the locks, beyond just the process ID or object name. The information displayed includes the lock type, lock mode, lock status, and index involved.
Choosing the Optimal Method to Defragment Data and Indexes
There several methods you can use when you need to defragment your data and indexes. One method available is the DBCC INDEXDEFRAG command. DBCC INDEXDEFRAG eliminates the internal defragmentation in an index, but does not hold locks long term while it runs and doesn't lock the entire table. As a result, it can be run online and will not block concurrently-running queries or updates.
Alternatively, you can rebuild indexes by manually running a series of DROP INDEX and CREATE INDEX commands. However, this can be a tedious process that runs the risk of an index not getting rebuilt if it's missing from the SQL script. Also, if you run out of space while rebuilding an index, the CREATE INDEX command fails leaving you without that index on the table.
A better way of rebuilding all indexes is to use the DBCC DBREINDEX command. Using DBCC DBREINDEX keeps you from having to specify all the indexes to drop and re-create on a table (if you specify just the table name, it automatically rebuilds all indexes). In addition, if DBCC DBREINDEX fails while processing for some reason (out of space, out of locks, and so on), the rebuild is rolled back and the original indexes are left in place.
CAUTION
Caution! Unlike DBCC INDEXDEFRAG, DBCC DBREINDEX applies table-level locks while rebuilding indexes. It should not be run online, as it would block other queries and other updates on the table. This should only be run during off-peak hours or during the normal maintenace window.
Using AWE with SQL Server 2000 to Allocate More Memory
When running the Enterprise Edition of SQL Server 2000 on either the Windows 2000 Advanced Server or Windows 2000 Datacenter Server platforms, you can allocate more than the default maximum of 4GB of memory by enabling the Windows 2000 Address Windowing Extensions (AWE) API. When this option is enabled, a SQL Server instance can then access up to 8GB of physical memory on Advanced Server and up to 64GB on Datacenter Servers.
Although standard 32-bit addressing supports up to only 4GB of physical memory, the AWE API allows the additional memory to be acquired as nonpaged memory. The memory manager can then dynamically map views of the nonpaged memory into the 32-bit address space.
You must be careful when using this extension because nonpaged memory cannot be swapped out. SQL Server allocates the entire chunk requested and does not release it back to the operating system until SQL Server is shut down. Other applications or other instances of SQL Server running on the same machine might not be able to get the memory they need.
Also keep in mind that when using AWE with SQL Server 2000, SQL Server can no longer dynamically allocate RAM. By default, it grabs all available memory, leaving only 128MB available for Windows and other applications. You also need to configure the max server memory option to limit the amount of memory that SQL Server allocates. Be sure to leave enough memory for Windows and any other applications running on the server, usually at least 500MB.
Cascading Referential Integrity: A New Feature
SQL Server 2000 added a new feature that allows you to define cascading actions on your foreign key constraint. When defining the constraints on a table, you can use the ON UPDATE CASCADE or the ON DELETE CASCADE clauses, which cause changes to the primary key of a table to cascade to the related foreign key tables.
To illustrate the usefulness of this, consider a pair of related tables: employee and department. The employee table has a foreign key on the dept column that references the dept column of the department table. If it was created with ON UPDATE CASCADE, any changes to the dept column in the department table would cascade to the employee table. Therefore, if dept 20 has 5,000 employees, and you change the dept number to 200 to comply with a business rule, all 5,000 employee records are automatically updated as well. If ON DELETE CASCADE were specified for the table, then deleting dept 20 would result in the deletion of all 5,000 employees!
This is a powerful and dangerous feature. If you plan to utilize the cascade feature,you should work closely with the application developers to ensure that checks and balances are in place to prevent accidental deletion of data. It is also important to note the potential overhead generated by Cascading Referential Integrity.
Using the inserted and deleted Tables For Testing Purposes
In most trigger situations, you need to know what changes were made as part of the data modification. You can find this information in the inserted and deleted tables. For the AFTER trigger, these tables are actually views of the rows in the transaction log that were modified by the statement. With the new INSTEAD OF trigger, the inserted and deleted tables are actually temporary tables that are created on-the-fly. The tables have identical column structures and names to the tables that were modified.
To be able to see the contents of these tables for testing purposes, create a copy of the table, and then create a trigger on that copy. You can perform data modification statements and view the contents of these tables without the modification actually taking place. Use the following listing to create a copy of the tables and then create a trigger on the copy.--Create a copy of the titles table in the Pubs database
SELECT *
INTO titles_copy
FROM titles
GO
--add an AFTER trigger to this table for testing purposes
CREATE TRIGGER tc_tr ON titles_copy
FOR INSERT, UPDATE, DELETE
AS
PRINT 'Inserted:'
SELECT title_id, type, price FROM inserted
PRINT 'Deleted:'
SELECT title_id, type, price FROM deleted
ROLLBACK TRANSACTION
Clearing the syscacheobjects Table
Because query plans in SQL Server 2000 are re-entrant, it's typical that no more than one copy of an execution plan for a stored procedure is in cache memory. However, sometimes multiple query plans can be created and exist in procedure cache at the same time. One of the more likely causes is when users run the same procedure with different settings for specific session options.
How does SQL Server know what plans are currently in memory and what settings were in effect when they were created? This information is contained in the syscacheobjects table in the master database. syscacheobjects keeps track of all the currently compiled plans in the procedure cache.
A large number of entries can exist in the syscacheobjects table. To clear the procedure cache buffers, and subsequently, the syscacheobjects table, you can issue the DBCC FREEPROCCACHE procedure, which removes all cached plans from memory.
Alternatively, you can use the undocumented command, DBCC FLUSHPROCINDB(dbid), to flush all procedure query plans for the specified database from memory. Needless to say, you shouldn't execute these commands in a production environment because they can impact the performance of the production applications running at the time.
Protect Source Code of Stored Procedures Using the WITH ENCRYPTION Option
To protect the source code of your stored procedures and keep its contents from prying eyes, you can create a procedure using the WITH ENCRYPTION option. When this option is specified, the source code stored in the syscomments table is encrypted. If you use encryption when creating your stored procedures, be aware that while SQL Server can internally decrypt the source code, no mechanisms exist for the user or for any of the end user tools to decrypt the stored procedure text for display or editing.
With this in mind, make sure that you store a copy of the source code for those procedures in a file in case you need to edit or re-create them. Also, if you use the WITH ENCRYPTION option, you can no longer use the Transact-SQL Debugger on the encrypted stored procedure. Don't use the WITH ENCRYPTION option unless you have a good reason to hide the stored procedure code.

Tips 11-20
Choose the Correct Outlook 2000 Configuration
If you use Outlook 2000, when you set up your initial mail profile you have the option to set it up for Internet Only or for Corporate or Workgroup. Even though you might be setting up Outlook to connect only to an Internet Mail server, choose the Corporate or Workgroup mode. You don't have to add an Exchange Server mail account. However, your mail profile will behave differently.
In Corporate or Workgroup mode, SQL Mail will send e-mail messages immediately when they are generated, whether or not the Outlook client is running. For some reason, when configured for Internet Only mode, the mail messages go into limbo and are not sent unless the Outlook 2000 client is running, is online to the mail server, and is configured to automatically send and receive mail on a schedule (for example, every 2 minutes). You may find similar behavior using the Outlook XP mail client when it is configured with only Internet mail accounts.
Back Up Your Transaction Log Before You Do a Recovery
Any time you think you might have to do a recovery, the first thing you should do is back up the transaction log. If you skip this step and restore the database, you will have unnecessarily lost all the transactions since the last log backup.
There was a bug in SQL 7.0 that prevented the NO_TRUNCATE option from working if the primary data file for the database was damaged. Those of you who found this out the hard way will be happy to know it is fixed in SQL Server 2000.
Good Things Come to Those that Wait
After you run the sp_addumpdevice stored procedure, don't panic when you go looking for the physical files on disk and can't find them. The files aren't created until the first time you actually use the backup device.
Also, the physical pathname to the dump file is not validated until the first time you use the backup device. If a bad pathname is entered when creating the dump device, you receive an error message similar to the following when attempting to back up to it:Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'mydump'. Device error or device off-line.
See the SQL Server error log for more details.
Constraints Are The Key to Integrity
Constraints are the primary method used to enforce integrity. Constraints can be created in the CREATE TABLE statement or after the table has been created using ALTER TABLE.
Create your tables first, and then use ALTER TABLE to create the constraints. This has the twofold effect of keeping your CREATE TABLE syntax simpler and providing ALTER TABLE scripts that can be rerun or modified as the need arises.
Changing Datatypes Can Affect Your Data
Changing some datatypes can result in changing the data. For example, changing from nchar to char could result in any extended characters being converted.
Similarly, changing precision and scale could result in data truncation. Other modifications (such as changing from char to int) might fail if the data doesn't match the restrictions of the new datatype. When changing datatypes, always validate that the data conforms to the new datatype.
Don't Shrink If You Don't Have To
Unless there is a definite need for the space that would be made available by shrinking a database, you shouldn't shrink the database files if the space will be used again in the future.
For one thing, having free space available in the database files avoids the overhead of having to autogrow the database files when they become full. Also, if a database file is repeatedly shrunk and expanded, the database file itself can become fragmented within the file system, which can degrade IO performance for the file.
Spread It Around
If too many outstanding I/Os are causing bottlenecks in the disk I/O subsystem, you might want to consider spreading the files across more disk drives. Performance Monitor can identify these by monitoring the PhysicalDisk object and Disk Queue Length counter. Consider spreading the files across multiple disk drives if the Disk Queue Length counter is greater than three.
Avoid Direct System Table References
Avoid writing queries that reference the system tables directly. Microsoft tries to provide backward compatibility whenever possible, but it is not guaranteed. What this means is that Microsoft reserves the right to change the names of the system tables at any time.
If you have applications that call these tables, they might not migrate to the next release. The information schema views, on the other hand, will remain consistent.
There's Good Info in the Error Logs
The SQL Server error log is a good place to look to determine the protocols that SQL Server on which listening. You can view the error logs via the Management node in Enterprise Manager, or you can open the error log file using any text editor. The latest error log file is found by default in Program Files\Microsoft SQL Server\Mssql\Log\Errorlog.
Open the error log file with a text editor and search for the word Listening in the error log text to find the relevant messages. The Server Network utility shows you what has been enabled, but there are instances when the server is unable to listen on the enabled protocol at startup.
Choosing the Best Trace Save Operation in SQL Profiler
Two distinct Save operations are available in the SQL Profiler. You can save trace events to a file or table, or you can save a trace definition in a template file. The Save As Trace Table and Save As Trace File options are for saving trace events to a file. The Save As Trace Template option saves the trace definition. Saving a trace template saves you the trouble of having to go through all the properties each time to set up the events, data columns, and filters for your favorite traces.
However, if you are executing a long-running trace, you should save to a file or table, as the trace runs to be sure that you capture the entire trace. Saving to a file allows you to clear the current contents of the trace window and not lose the trace information. Also, if you save the trace to a file or a table, you can open it later and specify whatever grouping you want to reorganize output. This flexibility gives you almost endless possibilities for analyzing the trace data.
If you are saving to a trace file as the trace runs, and the trace is expected to be large, then you can use the Enable File Rollover option, which creates a new event file when the file reaches the size that you specify. This allows you to have smaller, more manageable trace files for your review.

Tips 21-31
SQL Server 2000 Allows You to Schedule when a Trace Stops
Basic properties of a trace are defined in the General tab of the Trace Properties window. While these are straight-forward, there is a new feature. The General Properties screen contains a new option, Enable Trace Stop Time. This is a scheduling-oriented feature that allows you to specify a date and time at which you want to stop tracing. This is handy if you want to start a trace in the evening before you go home. You can set the stop time so that the trace will run for a few hours but won't affect any nightly processing that might occur later in the evening.
Save Trace Definitions as a Template File
SQL Server 2000's trace templates contain predefined trace settings that address some common auditing needs. These are not actual traces; they simply provide a foundation for you when creating your own traces.
When using these templates, it is important to note that the Trace Name is a relatively unimportant trace property for future traces. When you create a new trace, you can specify a name for the trace; however, this trace name will not be used again. You will not be selecting the trace to run based on the Trace Name that you entered originally. Trace Name is useful only if you are running multiple traces simultaneously and need to distinguish between them more easily. The Trace Name is used as the window title.
If you have a trace definition that you like, you can save it as a template file. If you want to run the trace again in the future, you can create a new trace and select the template file that you saved. This saves you the trouble of having to go through all the properties each time you set up the events, data columns, and filters for your favorite traces.
Create Your Own Custom Shortcuts in Query Analyzer
Query Analyzer provides up to 12 keyboard shortcuts for quickly executing stored procedures in Query Analyzer. SQL Server provides three predefined shortcuts:
Alt+F1—sp_help
Ctrl+1—sp_who
Ctrl+2—sp_lock
You can view or modify these shortcuts, or even add your own by selecting the Customize option from the Tools menu. On the Custom tab, you can enter the name of the stored procedure in the Stored Procedure column next to the desired keyboard shortcut you want to use to execute that stored procedure. You might want to add your frequently executed procedures to the Custom menu to save you the trouble of having to type them every time you want to run them.
Here's a bonus tip: if you have a word, data value, or comma-separated list of values highlighted when you invoke a keyboard shortcut, Query Analyzer passes the highlighted value(s) as parameters to the associated stored procedure.
Use a Batch File to Save Time when Executing Command-Line Utilities
SQL Server command-line utilities provide administrators with a set of powerful tools for tasks such as automating routine maintenance procedures and verifying client connectivity. A batch file, typically a text file with the .bat extension, can be a real timesaver when executing the command-line utilities. You can create the batch file in the same directory as your executable file and specify the appropriate parameters.
One helpful technique: A PAUSE command at the end of the batch file allows you to view the results in the command prompt window before the window disappears.
Connecting with ISQL Utility Using Named Instances
According to the Books Online documentation, the ISQL utility (which is based on Db-Library) does not support named instances. Contrary to the documentation,you can connect with ISQLusing named instance, but you might encounter problems.
If you do experience problems, one workaround is to set up an alias for the named instance using the Client Network utility. After this alias has been created, then the alias name can be used with the \S parameter in ISQL. Another alternative is to use OSQL, which does support named instances and all other SQL Server 2000 features.
Windows 2000 Clients Can Use PathPing to Troubleshoot TCP/IP Connectivity Problems
One way to verify that a client's ODBC connectivity is properly configured is to use the obdcping utility. However, there is another option for Windows 2000 clients. A new utility named PathPing is also available for troubleshooting TCP/IP connectivity problems. Given a target server name, this utility displays the computer name and IP address for each router as it moves toward its destination. If successfully connected to the destination server, the utility echoes reply packets (messages) to the client machine to indicate that TCP/IP connectivity exists.
For Windows 9.x and NT clients, the Ping utility, which has less functionality, is available instead.
Using the rebuildm Utility to Rebuild System Databases Requires Caution
The rebuildm lets you rebuild system databases, including the master, msdb, and model databases that are shipped with SQL Server 2000. The rebuildm utility is typically used if the following situations exist:
A current backup of the master database is unavailable. If the backup were available, the server would need to be started in single user mode first.
Microsoft SQL Server 2000 cannot start because the master database is severely damaged.
However, rebuildm needs to be used with extreme caution because it essentially copies the original versions of these databases over the copies that you have in your database. After rebuildm has been run, all of the information that you had in these databases prior to running rebuildm (including user database information, scheduled tasks, and default database settings) will be gone.
Alternative Methods for Starting and Stopping Services
Usually, SQL Server can be stopped and started easily from the EM console by right-clicking a server, which displays a pop-up window. The available options are to stop the server, which stops the SQL Server service and any dependent services, to pause the server, in which case the server remains running but no new connections are allowed, and to start the server in the case that the server has been stopped. If the server has been paused, then a Continue option appears in the pop-up menu to resume SQL server in a normal connection state.
However, occasionally, Enterprise Manager and the taskbar Service Manager get confused as to what state the server is in and won't allow services to be stopped or started. In this case, go to the Services applet in the Control Panel, or use the net stop/net start commands to stop and restart the appropriate services.
Change Server-Level Settings Cautiously
Changing server-level settings affects all databases on the server and can adversely affect performance. When changing configuration options, consider the effect the change might have and carefully document your changes so they can be undone if required.
Free Text Searches: CONTAINSsbm vs. FREETEXTsbm
While the SQL engine does support basic text searches against specific columns, you may find this method too slow and inflexible. The Microsoft Full Text Search Services provides full text searching capabilities, which is useful for searching large text fields, such as movie reviews, book descriptions, or case notes. You can specify tables or entire databases that you want to index. When you use full text indexing to perform a search for text, your query will use either the CONTAINSsbm or FREETEXTsbm function.
On the flip side, CONTAINS is a better performing function and returns more exact results. FREETEXT returns looser results based on the meaning of the search phrase you enter. It does this by finding alternate word forms in your queries. For example, FREE TEXT (*,'work independently') would match 'work independently",'Independent work preferred', and 'Independence in my work'.
Run SQL Profiler to Trap SQL-EM Commands
SQL Enterprise Manager interacts with SQL Server using standard Transact-SQL commands. For example, when you create a new database through the SQL-EM interface, behind the scenes it generates a CREATE DATABASE SQL command. Whatever you can do through SQL-EM, you can do with the Query Analyzer or even the command line ISQL or OSQL programs.
If you're curious how EM is accomplishing something, you can run SQL Profiler to trap the commands that SQL-EM is sending to the server. You can use this technique to discover some interesting internals information. You can also use this tactic to capture SQL scripts, and then repeat tasks using the script instead of a few dozen interface clicks.

Advantages of a 64-bit Environment

2008-02-28T00:10:00.002+01:00

What are the performance advantages of migrating to a x64 plattform? This is of course a should know for a DBA! When should you do it? Why should you do it?

Here is an article that explains the differences between x86 and x64.

http://www.microsoft.com/sql/techinfo/whitepapers/advantages-64bit-environment.mspx

Stefan

Event Id 10016 Source DCOM

2008-02-26T23:27:00.003+01:00

After changing the account for running the sqlserveragent service I got the following error:

The application-specific permission settings do not grant Local Launch permission for the COM Server application with CLSID
{ABF05265-635E-44B0-A28F-AEA45247ACA0}
to the user domain\sqlserveragent SID (S-1-5-21-3925573443-80846547-1646150170-2122). This security permission can be modified using the Component Services administrative tool.

The key {ABF05265-635E-44B0-A28F-AEA45247ACA0} is a little difficult to read in my eyes, however search for it in the registry and you will get a readable name of the dcom service that you need to change.

The solution was the following:

Click start and run and write dcomcnfg and hit enter
Open component services/computers/my computer/dcom config
Right click MsDtsServer and choose properties
On the security tab click the button edit under launch and activation permissions
Add the user that runs sqlserveragent or the SQL server service. You should choose the one that leaves the error in the system log.
Give the user local launch permission and then you shouldn´t have the error in the logs.

I guess there are other scenarious where this could be applicable.

Stefan

Good article series about AD

2008-02-25T23:26:00.003+01:00

I love the articles on http://www.petri.co.il/ You can find this on MS but they aren´t at all as easy to understand as Daniels articles.

These one are a must know, that a domain admin will have use for some day.

Manually Undeleting Objects in Active Directory
Recovering Deleted Items in Active Directory
How to Restore Windows Server 2003 Active Directory

The following tips I have practised myself after a hardware failure on a DC and everything worked very well following the guide.
How can I delete a failed Domain Controller object from Active Directory?

How can I manually delete a server object from the Active Directory database in case of a bad DCPROMO procedure?

This is really an easy one but I put some thought and consideration before I tried it. One example when this could be on the agenda is when you make a merge with another company and you have to follow some naming policy on the servers and workstations.
How can I rename my Windows 2003 Domain Controllers?

This is it for now.

Stefan

Storage Top 10 Best Practices

2008-02-21T21:54:00.004+01:00

I was looking for some best practices for setting up and optimising SQL server and found this from MS.

Storage Top 10 Best Practices
Published: October 17, 2006
Proper configuration of IO subsystems is critical to the optimal performance and operation of SQL Server systems. Below are some of the most common best practices that the SQL Server team recommends with respect to storage configuration for SQL Server.

Understand the IO characteristics of SQL Server and the specific IO requirements / characteristics of your application.

In order to be successful in designing and deploying storage for your SQL Server application, you need to have an understanding of your application’s IO characteristics and a basic understanding of SQL Server IO patterns. Performance monitor is the best place to capture this information for an existing application. Some of the questions you should ask yourself here are:

What is the read vs. write ratio of the application?

What are the typical IO rates (IO per second, MB/s & size of the IOs)? Monitor the perfmon counters:
1. Average read bytes/sec, average write bytes/sec
2. Reads/sec, writes/sec
3. Disk read bytes/sec, disk write bytes/sec
4. Average disk sec/read, average disk sec/write
5. Average disk queue length
How much IO is sequential in nature, and how much IO is random in nature? Is this primarily an OLTP application or a Relational Data Warehouse application?
To understand the core characteristics of SQL Server IO, refer to SQL Server 2000 I/O Basics.

More / faster spindles are better for performance

Ensure that you have an adequate number of spindles to support your IO requirements with an acceptable latency.
Use filegroups for administration requirements such as backup / restore, partial database availability, etc.
Use data files to “stripe” the database across your specific IO configuration (physical disks, LUNs, etc.).

Try not to “over” optimize the design of the storage; simpler designs generally offer good performance and more flexibility.

Unless you understand the application very well avoid trying to over optimize the IO by selectively placing objects on separate spindles.
Make sure to give thought to the growth strategy up front. As your data size grows, how will you manage growth of data files / LUNs / RAID groups? It is much better to design for this up front than to rebalance data files or LUN(s) later in a production deployment.

Validate configurations prior to deployment

Do basic throughput testing of the IO subsystem prior to deploying SQL Server. Make sure these tests are able to achieve your IO requirements with an acceptable latency. SQLIO is one such tool which can be used for this. A document is included with the tool with basics of testing an IO subsystem. Download the SQLIO Disk Subsystem Benchmark Tool.
Understand that the of purpose running the SQLIO tests is not to simulate SQL Server’s exact IO characteristics but rather to test maximum throughput achievable by the IO subsystem for common SQL Server IO types.
IOMETER can be used as an alternative to SQLIO.

Always place log files on RAID 1+0 (or RAID 1) disks. This provides:

better protection from hardware failure, and better write performance. Note: In general RAID 1+0 will provide better throughput for write-intensive applications. The amount of performance gained will vary based on the HW vendor’s RAID implementations. Most common alternative to RAID 1+0 is RAID 5. Generally, RAID 1+0 provides better write performance than any other RAID level providing data protection, including RAID 5.

Isolate log from data at the physical disk level

When this is not possible (e.g., consolidated SQL environments) consider I/O characteristics and group similar I/O characteristics (i.e. all logs) on common spindles.
Combining heterogeneous workloads (workloads with very different IO and latency characteristics) can have negative effects on overall performance (e.g., placing Exchange and SQL data on the same physical spindles).

Consider configuration of TEMPDB database

Make sure to move TEMPDB to adequate storage and pre-size after installing SQL Server.
Performance may benefit if TEMPDB is placed on RAID 1+0 (dependent on TEMPDB usage).
For the TEMPDB database, create 1 data file per CPU, as described in #8 below.

Lining up the number of data files with CPU’s has scalability advantages for allocation intensive workloads.

It is recommended to have .25 to 1 data files (per filegroup) for each CPU on the host server.
This is especially true for TEMPDB where the recommendation is 1 data file per CPU.
Dual core counts as 2 CPUs; logical procs (hyperthreading) do not.

Don’t overlook some of SQL Server basics

Data files should be of equal size – SQL Server uses a proportional fill algorithm that favors allocations in files with more free space.
Pre-size data and log files.
Do not rely on AUTOGROW, instead manage the growth of these files manually. You may leave AUTOGROW ON for safety reasons, but you should proactively manage the growth of the data files.

Don’t overlook storage configuration bases

Use up-to-date HBA drivers recommended by the storage vendor
Utilize storage vendor specific drivers from the HBA manufactures website
Tune HBA driver settings as needed for your IO volumes. In general driver specific settings should come from the storage vendor. However we have found that Queue Depth defaults are usually not deep enough to support SQL Server IO volumes.
Ensure that the storage array firmware is up to the latest recommended level.
Use multipath software to achieve balancing across HBA’s and LUN’s and ensure this is functioning properly
Simplifies configuration & offers advantages for availability
Microsoft Multipath I/O (MPIO): Vendors build Device Specific Modules (DSM) on top of Driver Development Kit provided by Microsoft.

Shrink the database or the databasefiles?

2008-02-18T22:13:00.003+01:00

Shrinking the database or the database files is one thought that will struck any dba at some time. The reasons can vary but some time the available disk space will come as an variable.

Tibor Karaszi has a good article why you should be restrictive with shrinking the database. His thoughts are logical and he should know what he´s talking about as he became the the 7th SQL Server MVP in the world 1999. Here´s the link to the article.

However in certain situations it´s ok to shrink the db files, for example when you have made an bulk insert or some kind of upgrade of the database that uses the logfile more than the daily work does.

We have just made an upgrade to the newest version of our software and that included a lot of conversion of the historic database, and therefore the logfile grew to 17Gb instead of 100Mb. In this case it is not only OK to shrink the file, it is in my opinion recommended.

As you see you can newer say that shrinking the database is wrong, you must always take other actions in consideration.

Optimize disk configuration in SQL Server

2008-02-18T00:22:00.001+01:00

This article was found on http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1287710,00.html#

Optimize disk configuration in SQL Server

One of the easiest ways to improve the lifetime performance of a SQL Server database is proper setup of the physical and logical drives. While it's an easy technique, proper disk subsystem configuration is often overlooked or handled by a member of the IT staff other than a SQL Server DBA. All too often the disk subsystem, including the disk array, is configured based only on storage capacity, with no question of drive performance. Let's go beyond storage capacity requirements and design for drive performance.
Tips for optimizing disk configuration in SQL Server
Storage specific terms that everyone should know What's the best RAID level for your system? Best practices for drive and physical file layout configuration Drive layout examples for SQL Server systems

Storage specific terms that everyone should know
Return to Table of Contents
Before you sit down with a storage administrator or engineer to map out your plans for disk configuration, here are basic preparation steps to take. Start by getting familiar with the terms below and communicate your requirements much easier.
RAID – Redundant Array of Inexpensive Disks, also known as Redundant Array of Independent Disks.
Disk subsystem – A general term that refers to the disks on the server.
Spindle – Spindles are another way to refer to the physical disk drives that make up the RAID array.
I/O Ops – Input/Output operations, usually measured per second.
Queuing – Number of I/O Ops that are pending completion by the disk subsystem.
SAN – Storage area networks are collections of storage devices and fibre switches connected together along with the servers that access the storage on the device. SAN has also become a generic term, which refers to the physical storage drives such as EMC, 3PAR and Hitachi.
LUN – Logical Unit Number – This is the identification number assigned to a volume when created on a SAN device.
Physical drive – How Windows sees any RAID array, single drive or LUN that is attached to the server.
Logical drive – How Windows presents drives to the user (C:, D:, E:, etc.).
Block size – The amount of data read from the spindles in a single read operation. This size varies per vendor from 8 KB to 256 MB.
Hardware array – A RAID array created using a physical RAID controller.
Software array – A RAID array created within Windows using the computer management snap-in.
Hot spare – A spindle that sits in the drive cage and is added to the array automatically in the event of a drive failure. While this does not increase capacity, it does reduce the amount of time that the array is susceptible to data loss because of a second failed drive.
Recovery time – Amount of time needed for the RAID array to become fully redundant after a failed drive has been replaced, either manually or automatically via a hot spare.

What's the best RAID level for your system?
Return to Table of Contents
There are many RAID levels available on modern RAID controllers. Only a subset of these is most useful when configuring a Microsoft SQL Server. Each RAID level has a specific use and benefit. Using the wrong type of RAID level can not only hurt system performance, but also add more cost to your server configuration. Experts recommend that you never use software arrays on a database server. Use of software arrays requires additional CPU power from Windows in order to calculate which physical disk the data is written to. In hardware arrays, this overhead is offloaded to a physical PCI, PCIe or PCIx card within the computer (or within the SAN device), which has its own processor and software dedicated to this task.
RAID 1 – Mirror. A RAID 1 array is most useful for high write files, such as the page file, transaction logs and tempdb database. A RAID 1 array takes two physical disks and creates an exact duplicate of the primary drive on the backup drive. There is no performance gain or loss when using a RAID 1 array. This array can survive a single drive failure without incurring any data loss.
RAID 5 – Redundant Stripe Set. A RAID 5 array is most useful for high read files such as the database files (mdf and ndf files) and file shares. It is the most cost-effective, high-speed RAID configuration. With a RAID 5 array, there is a performance impact while writing data to the array because a parity bit must be calculated for each write operation performed. For read performance, the basic formula is (n-1)*o where n is the number of disks in the RAID 5 array and o is the number of I/O operations each disk can perform. Note: While this calculation is not perfectly accurate, it is generally considered close enough for most uses. A RAID 5 array can survive a single drive failure without incurring any data loss.
RAID 6 – Double Redundant Stripe Set. Like a RAID 5 array, a RAID 6 array is most useful for high read files such as the database and file shares. With RAID 6, there is also a performance impact while writing data to the array because two parity bits must be calculated for each write operation performed. The same basic formula is used to calculate the potential performance of the drives (n-2)*o. A RAID 6 array can survive two drive failures without incurring any data loss.
Because of the dual parity bits with RAID 6, it is more expensive to purchase than a RAID 5 array. However, RAID 6 offers a higher level of protection than RAID 5. When choosing between RAID 5 and RAID 6, consider the length of time to rebuild your array, potential loss of a second drive during that rebuild time, and cost.
RAID 10 – Mirrored Strip Sets. A RAID 10 array is most useful for high read or high write operations. RAID 10 is extremely fast; however, it is also extremely expensive (compared to the other RAID levels available). In basic terms, a RAID 10 array is several RAID 1 arrays stripped together for performance. As with a RAID 1 array, as data is written to the active drive in the pair, it is also written to the secondary drive in the pair. A RAID 10 array can survive several drive failures so long as no two drives in a single pair are lost.
RAID 50 – Stripped RAID 5 Arrays. A RAID 50 array is an extremely high-performing RAID array useful for very high-load databases. This type of array can typically only be done in a SAN environment. Two or more RAID 5 arrays are taken and stripped together and data is then written to the various RAID 5 arrays. While there is no redundancy between RAID 5 arrays, it's unnecessary because the redundancy is handled within the RAID 5 arrays. A RAID 50 array can survive several drive failures so long as only a single drive per RAID 5 array fails.

Best practices for drive and physical file layout configuration
Return to Table of Contents
With the introduction of larger and larger hard drives, storage administrators have been presented with a new issue: increased recovery times. In the past, 172 GB drives were the standard size deployed. Even if you had five drives in a RAID 5 array, the total storage of the array was only ~688 Gigs. At that size, recovery from a failed drive would take only hours. With those five drives now closing in on the 1TB mark per drive, they now create a 4 TB array. This larger array could take up to a day to recover (depending on system load, disk speed and so on).
Because of increased recovery time and, therefore, increased risk of data loss, it is recommended that smaller disks be used in greater numbers. Instead of a single 2 TB array, use smaller drives and make several 500 GB arrays. Not only will this improve your recovery time, but your system performance will increase, too, as you now have many more disks in your arrays. Whenever possible, connect each RAID array to its own RAID controller. That will minimize the possibility of overloading the RAID controller with I/O operations.
When setting up your disk subsystems, there are five basic groups of files to keep in mind. These are the data files (mdf and ndf files), log files (ldf files), tempdb database, the SQL Server binaries (files that are the actual SQL Server software) and Windows. Windows and
More on storage and disk arrays topics in SQL Server:
Configure RAID for maximum SQL Server I/O throughput
SAN considerations for your SQL Server environment
the SQL binaries will perform nicely on a single RAID 1 array, which should include the Windows page file. Note: Although it can be moved to another RAID 1 array, it's not necessary and will not provide any additional performance benefit.
Data files should be placed on one or more RAID 5 or RAID 6 arrays (based on system needs). In certain systems, you should place the data files on RAID 1 or RAID 10 arrays, but those systems are in the minority. Place the transaction log files on one or more RAID 1 or RAID 10 arrays (again, based on size and performance needs). The tempdb database should be placed on its own RAID 1 or RAID 10 array. None of these file groups (other than Windows and the SQL binaries) should share physical drives. By separating your files into these groups, you will see a definite improvement in performance.
To determine the number of spindles, you need to first know a few things about your hardware and your database and application. For the hardware, find out the number of IOPs each physical drive can handle. For the application, you need to know the typical high watermark that your database will need to function. The number of spindles you have times the number of IOPs that each drive can handle must be higher than the high watermark number of IOPs that your database requires.

Drive layout examples for SQL Server systems
Return to Table of Contents
Now that we have covered the basics, let's put all this information together and look at some server drive layouts. These systems all have a RAID 1 drive holding the Operating System, page file and SQL Server binaries:
The small database server. This would be a database system having a small number of users and a low number of transactions per second. For a system of this type, putting the data files, transaction logs and tempdb database on a single RAID 5 array will be fine. With a load this low, there should be minimal to no performance loss because of having the files on the same array.
The medium database server. This would be a system that has a larger number of users and no more than 100 transactions per second. At this system size, you will begin to separate parts of the database. Start by separating the database files and log files onto different RAID arrays. The tempdb database can probably remain on the same array as the database files. But, if the tempdb database is heavily used for things like lots of temporary table usage, it should be moved to its own drive array.
The large database server. This system is for hundreds of users and hundreds or thousands of transactions per second. When designing systems of this size, break apart not only the database files, log files and tempdb database files, but also want database into smaller data files on multiple drives. This includes moving the indexes to separate files on RAID 1 or RAID 5 arrays, moving blob data (data stored in TEXT, NTEXT, IMAGE, VARCHAR(MAX) and NVARCHAR(MAX) data types) to a separate RAID 5 array. You can also place different types of tables on different drives by assigning them to different file groups.
It is recommended that for larger systems, each file group of the database should have between .25 to one physical file per physical CPU core in the server. The tempdb database should have one data file per physical CPU core in the server.
It is highly recommended that all partitions are configured by using the DISKPAR (Windows 2000) or DISKPART (Windows 2003) commands. When using DISKPAR, adjust the alignment by 512 bytes, and when using DISKPART, the disk should be aligned to 64. The reason for this is due to the original master boot record design of WinTel based systems. The master boot record for all drives is 63 blocks (1 block = 512 bytes).
The physical disks want to read and write data in 64 block chunks. Because the master boot record is only 63 blocks, this puts the first block of actual data in block location 64, where it should be in block location 65. That forces the disk to read 128 blocks for each 64 blocks read to the disk, thereby increasing the work needed to be done and decreasing performance.

It is so highly recommended that volumes be created with this 64 block offset that Microsoft is including this procedure as the standard when creating partitions starting in Microsoft Windows 2008 Server. There are no published figures on what sort of performance improvement will be seen by creating your disks using this method. It's because any numbers would be relevant to only the system they were taken against, as all databases are different. Unfortunately, once a partition has been created without the alignment offset, there is no easy way to change the offset. The only method for doing that is to create a new volume and partition with the offset, take down the SQL Server and manually migrate the files to the new drive in an offline manor.
When designing larger database servers, it is important to pay careful attention to the disk configuration. An improperly laid out disk configuration can easily have an adverse impact on your database performance.
Even though laying out the disk subsystem of a SQL Server appears to be a simple task, take great care to get the maximum amount of performance from your drive subsystem. A poorly configured array can and will have a detrimental effect on the performance of your database system. When planning and purchasing your storage hardware, think not only of the capacity, but also about the amount of I/O operations that the disks will need to handle -- not only today, but far into the future. Proper planning will give you a high-performing SQL Server today and years from now. When designing a system, your goal should be that it performs well beyond the expected lifetime of the hardware it resides on.

How can I add the Internet Explorer icon back to the Windows Vista desktop?

2008-02-17T10:06:00.004+01:00

To add the Internet Explorer icon back to the Windows Vista desktop follow these steps:

Open Registry Editor.
In Registry Editor, navigate to the following registry key: HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\HideDesktopIcons\NewStartPanel
Create the following key (DWORD 32-bit):
{871C5380-42A0-1069-A2EA-08002B30309D} (just copy/paste, including the brackets)
Close Registry Editor. There is no need to reboot. Just wait a few seconds and see how the icon appears. Maybee you have to press F5 to update the desktop.
Done!

Note: As always, before making changes to your registry you should always make sure you have a valid backup. In cases where you're supposed to delete or modify keys or values from the registry it is possible to first export that key or value(s) to a .REG file before performing the changes.

Windows server sp2 on cluster

2008-02-15T22:11:00.003+01:00

I need to upgrade a w2k3 cluster to sp2 and researched any potential problems. I found a potential problem with the ms dtc service. I have read a lot of postings and kb´s but no one is 100% clear. The supported solution seems to make the ms dts service a clustered resource even if you don´t need it.

Steps to enable network DTC access
1. Click Start, point to Control Panel, and then click Add or Remove Programs.
2. Click Add/Remove Windows Components.
3. Select Application Server, and then click Details.
4. Select Enable network DTC access, and then click OK.
5. Click Next.
6. Click Finish.
7. Restart the computer.

If you are running Windows Server 2003 Service Pack 1 (SP1), you must follow these additional steps:
1. In Control Panel, open "Component services".
2. Expand Component Services, expand Computers, right-click My Computer, and then click Properties.
3. On the MSDTC tab, click Secuity Configuration under Transaction Configuration, click to select the Network DTC Access check box under Secuity Settings, and then click to select the following check boxes under Transaction Manager Communication: . Allow Inbound . Allow Outbound
4. You cannot select Mutual Authentication Required. Therefore, click to select one of the following check boxes: . Incoming Caller Authentication Required . No Authentication Required Note For more information about these options, click the following article number to view the article in the Microsoft Knowledge Base: 899191 (http://support.microsoft.com/kb/899191/) New functionality in the Distributed Transaction Coordinator service in Windows Server 2003 Service Pack 1 and in Windows XP Service Pack 2 5. Make sure that the Logon Account is set to NTAUTHORITY\NetworkService.
6. Click OK. A message box explains that the MS DTC Service will be stopped and restarted, and that all dependent services will also be stopped and restarted. Click Yes. Note If this is a Majority Node Set (MNS) cluster, do not use the MNS resource as the storage device for MS DTC. MS DTC requires a storage resource such as a physical disk.
7. Restart the computer.This section documents how to install MS DTC through Cluster Administrator on all nodes in a cluster. You only have to install the MS DTC once in Cluster Administrator and MSDTC will be configured on all nodes in the cluster. You do not have to install MS DTC manually on each node.

1. Start Cluster Administrator. To do so: a. Click Start, and then point to All Programs b. In Administrative Tools, click Cluster Administrator.
2. Create a Group Named "MSDTC Group" that contains a Physical Disk, Network Name, and an IP Address. To do so: a. In the File Menu select New, and then click Group. The New Group Wizard is available. b. Follow the instructions that the New Group Wizard provides to create the MSDTC Group. Note When creating the MS DTC, moving the resource group into a group other than SQL Server or Exchange Server group is highly recommended. Creating the MS DTC resource in its own resource group and assigning it to a separate cluster group keeps the resource highly available. Note If you create the resource in the same cluster group, and if Resource A has failed over, Resource B will also failover. When you create the resouce in a different cluster group, even when Resource A has failed over, the resource B does not failover. Creating Cluster Resouces in the different cluster groups decreases unnecessary failovers of the Cluster Resouces. Important After the new group for MS DTC is created you must create the Network Name, IP address and Physical Disk for Msdtc. You may already have the physical disk resource that can be moved into the new group. After the MS DTC Group is created you must create the IP address resource, Network Name resource and the Physical disk resources. You may already have a physical disk resource that must be moved into the new MS DTC group. 3. Create an IP address resource: a. Right-click the MS DTC group, and then click New\Resource. b. Type a descriptive name such as MSDTC IP Address. c. In Resource Type, click IP Address, click to select the MSDTC Group check box, and then click Next. d. In Possible Owners, click Next unless you do not want MS DTC to run on a particular node. e. In Dependencies, do not add any dependencies, and then click Next. f. In TCP/IP Address Parameters, select the public network, type the unique static IP address for MS DTC, and then click Next. g. Click Finish, and then click OK to confirm that the resource has been created.
4. Create a Network Name resource: a. Right-click the MS DTC group, and then click New\Resource. b. Type a descriptive name such as MSDTC Network name. c. In Resource Type, click Network name, click to select the MSDTC Group check box, and then click Next. d. In Possible Owners, click Next unless you do not want MS DTC to run on a particular node. e. In Dependencies, add the MS DTC IP address as a resource dependency, and then click Next. f. In Name, type the network name for the MS DTC resource, and then click Next. g. Click Finish, and then click OK to confirm that the resource has been created.
5. Create a "Physical disk" resource. Note You may already have a physical disk resource created. If so, you will have to move this physical disk resource into the MS DTC group and go to the next step. a. Right-click the MS DTC group, and then click New\Resource. b. Type a descriptive name such as MSDTC Physical disk. c. In Resource Type, click Physical disk, click to select the MSDTC Group check box, and then click Next. d. In Possible Owners, click Next unless you do not want MS DTC to run on a particular node. e. In Dependencies, do not add any dependencies, and then click Next. f. In Disk Parameters, click the physical disk that you will use for MS DTC. g. Click Finish, and then click OK to confirm that the resource has been created.
6. Right-click the MS DTC Group, and then click New\Resource.
7. Type a name, such as MSDTC Resource.
8. In Resource Type, click to select Distributed Transaction Coordinator, verify that the MSDTC Group is selected, and then click Next.
9. In Possible Owners, click Next unless you do not want MS DTC to run on a particular node. 10. In Dependencies, press and hold the CTRL key on the keyboard, select both the Physical Disk and Network Name that you created in step 2, and then click the Add button.
11. Click Finish, and then click OK to confirm that the resource has been created. Note You should not put the MS DTC resource online before you follow the instructions in Microsoft Knowledge Base article 817064 to enable Network DTC Access.
12. Complete the steps that are documented in Knowledge Base article 817064. 13. Right-click the MSDTC Resource, and then click Bring Online.

Failed SQL 2005 installation

2008-02-15T13:49:00.003+01:00

When trying to install SQL 2005 from disk you can get the following error: “There was an unexpected failure during the setup wizard” - Error 1603

A workaround for this is attached below.

Solution:To work around this problem, set the folders in the correct layout for the SQL Server 2005 installation. The SQL Server 2005 installation uses the following two folders:
Servers
Tools
These two folders must be under the same level of a folder or the root folder of a drive. The names of these folders must be exactly Servers and Tools. The Servers folder contains all the files that are required to install major SQL Server 2005 components, such as database engine. The Tools folder contains tools components and Books Online for SQL Server 2005.Then try installing sql from the path\servers\setup.exeSP2 fails to install database services - MSP Error: 29512 “Unable to install Windows Installer MSP file”Error Snippet : MSP Error: 29512 SQL Server Setup was unable add user Clusteruser@FQDN (where FQDN is the actual fully qualified domain name - abbreviated here for security) to local group DOMAIN\SQL2005SQLAgents (where DOMAIN is the actual name of the domain). MSP returned 1603: A fatal error occurred during installation.
Solution: KB 926622 – This article is for SP1 and also applicable to SP2 also.To install SP2, try creating the following path, and restart SP2 setup:DRIVE:\Microsoft SQL Server\MSSQL.2\MSSQL\FTData

Optimizing SQL Server CPU Performance

2008-02-14T10:02:00.003+01:00

Optimizing SQL Server CPU Performance

An interesting article when you have cpu problems on SQL server. Will test this in our environment as soon as possible and see if it gives any effect. For the moment we have peaks on the Context Switches/sec up to 4.5 millions on one system.

Hyper-Threading
Hyper-threading is a topic that is worth discussing a bit more because of the way that it affects SQL Server. Hyper-threading actually presents two logical processors to the OS for each physical processor. Hyper-threading essentially leases time on the physical processors so that each processor ends up more fully utilized. The Intel Web site (intel.com/technology/platform-technology/hyper-threading/index.htm) gives a far fuller description of how hyper-threading works.

On SQL Server systems, the DBMS actually handles its own extremely efficient queuing and threading to the OS, so hyper-threading only serves to overload the physical CPUs on systems with already high CPU utilization. When SQL Server queues multiple requests to perform work on multiple schedulers, the OS has to actually switch the context of the threads back and forth on the physical processors to satisfy the requests that are being made even if the two logical processors are sitting on top of the same physical processor. If you are seeing Context Switches/sec higher than 5000 per physical processor you should strongly consider turning off hyper-threading on your system and retesting performance.

In rare cases, applications that experience high CPU utilization on SQL Server can effectively use hyper-threading. Always test your applications against SQL Server with hyper-threading both turned on and off before implementing changes on your production systems.

Boost SQL server

2008-02-12T20:59:00.000+01:00

We had some performance problems with a SQL server for a while ago, and was looking for a way to tweak it a little bit. I found a couple of ways and will share it here.

The data had grown from 10GB a couple of years ago up to 35Gb at present. It shouldn´t be a problem to handle this by the hardware used, but the querys used by the application were poorly written and generated extremely big reads and that caused the cpu to go on 100% most of the time. The only explanation we have found is that the db crossed some magical size and started to cause the problems. The vendor were of course contacted and was aware of some of the problems and worked on getting the querys optimised.

The first one was to give SQL server access to more memory and using the /3GB switch in boot.ini. That means that windows release 1GB extra for applications, so in that way SQL server can gain access to 1GB extra. For this to work you must have at least 4GB RAM in the server. A guide about the boot.ini options you can find here.

One other thing we did was reserving physical memory for the SQL server. This is done by right clicking the SQL server and choose properties. Click on the memory tab and tick the box "reserve physical memory for SQL server". Both these tips will need a reboot.

Defragmenting the disks whiped out the problem with a little to high "average disk queue length" that we also had. Defragmenting and reindex of the database is also strongly suggested.

The root problem were to much data in the db and a poorly written application that were to much for the hardware used. When we installed a new cpu the problem is under control, so we can plan and purchase a new hardware configuration.

This were a couple of nervous and intense days.

Stefan

Temporarily Bypass Windows Server 2008 and Windows Vista Activation

2008-02-12T20:50:00.000+01:00

I was playing around with Vista on a virtual machine for a couple of weeks ago, but I didn´t have the time to get really serious. The VM was shut down and the next time I wanted to use it the grace period for activation was over. The only thing that worked was activating it on the internet.
I needed to change the network settings to gain access to the internet for activation. Searching around the internet to find a way to bypass the activation I found this. Of course you should activate and use a legally bought license, but this is a way to have it working so you can activate it on the internet.

Stefan

How to remove data in Active Directory after an unsuccessful domain controller demotion

2008-02-12T10:06:00.000+01:00

This is a very good article when the accident has already happened and one of the dc´s have gone south. How to remove data in Active Directory after an unsuccessful domain controller demotion

Have done it myself one time and it did work perfectly.

Change ipaddress on domain controller

2008-02-11T23:56:00.001+01:00

I don´t know where I found this one but it can become handy some day when the boss gets the idea of changing the internal ip-plan.

Stefan

Changing your IP address on an Active Directory domain controller can cause many issues with the DNS server on that machine. To
change the IP address, complete the following steps:
Select: Start -> Settings -> Network and Dial Up Connections
Select: Your Local Area Connection
Select: Internet Connections (TCP/IP) Properties
Change: Your IP Address and Subnet Mask and Gateway
Change: Preferred DNS server's address to the new server address
Select: OK -> OK -> Close
Your server's address is now changed.
Select: Start -> Programs -> Administrative Tools -> DNS
Double click Forward Look Up Zones. Then double click your server name.
Delete: Your type A records
Reset your information in your SOA and NS records and exit DNS. Now drop down to an MS-DOS
prompt and type the following:
ipconfig /flushdns Enter
Net Stop DNS Enter
Net Start DNS Enter
Net Stop Netlogon Enter
Net Start Netlogon Enter
ipconfig /registerdns Enter
You can now go back to DNS and make sure the records were all created and they should have changed the address to the correct address
on their own.
Now run NSLookup from an MS-DOS prompt and see if all is resolved OK or not. It the names and IP addresses all resolve correctly,
you're all set. If not, then go back to NSLookup and type set root=servername (the name of your DNS domain) and hit Enter. When the
prompt returns, type exit to exit out of NSLookup. When you type NSLookup, it should resolve the name correctly.

Redirect Favorites special folder

2008-02-11T22:23:00.000+01:00

I was looking for how you could redirect the Favorites folder along with "My Documents" via a gpo and found a very good explanation on Windowmaker's blog and also a dowloadable custom gpo. The full explanation can be found on the windowmaker´s blog.

Remember that it will not redirect current favorites in the local profile, rather create a new favorites folder where you must copy your current ones.

Stefan

"Redirecting IE Favorites via GPO
I have seen this question multiple times at different web forums and newsgroups. Sometimes you want to redirect user's favorites to a network share, letting user access his favorites when he logs on from different workstations (or just for the sakes of backups).

The location of the user's favorites is determined by the following registry value:
Key: HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell FoldersValue: FavoritesType: REG_EXPAND_SZDefault: %USERPROFILE%\Favorites

The fact that the registry value has a type of REG_EXPAND_SZ is actually very convenient - you can use environment variables when pointing to the location of the Favorites.

Now you can solve the issue of redirecting the folder using either logon script or something similar, but being GPO addict I decided to create an administrative template to handle the task.
Here is what I came up with: (you can download the ADM from here. No need to cut&paste)

CLASS USER CATEGORY !!WindowsComponentsCATEGORY !!InternetExplorerCATEGORY !!IE_Favorites KEYNAME "Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders" POLICY !!IE_Favorites #if version >= 4 SUPPORTED !!SUPPORTED_IE5 #endif EXPLAIN !!IE_Favorites_Location_Explain PART !!IE_Favorites_Location_Tip1 TEXT END PART PART !!IE_Favorites_Location EDITTEXT EXPANDABLETEXT VALUENAME Favorites DEFAULT "%USERPROFILE%\Favorites" REQUIRED END PART END POLICY END CATEGORY ;; IE_FavoritesEND CATEGORY ;; Internet ExplorerEND CATEGORY ;; WindowsComponents[strings]WindowsComponents="Windows Components"IE_Favorites="Favorites"IE_Favorites_Location="The path to the favorites folder"IE_Favorites_Location_Explain="Specify the path to the location of favorites. You can use variables like %USERPROFILE%, %USERNAME%, etc... Both local and UNC paths are valid."IE_Favorites_Location_Tip1="Specify the UNC path to the favorites location"InternetExplorer="Internet Explorer"SUPPORTED_IE5="at least Internet Explorer v5.01"