Start of Week

Working with a developer last week to find for any given date the Monday of the week the date falls within. Several scripts online but none seemed to work reliably so the script below was written. Two parameters are used, @currdate, the date within the week, and @startdate, the first day of the year for @currdate.

DECLARE @currdate DATETIME
DECLARE @startdate DATETIME
SET @currdate = GETDATE()
SET @startdate = '01/01/' + CONVERT(CHAR(4),@currdate,112)
select DATEADD(wk, DATEDIFF(wk, 7, dateadd(dd,(datepart(dy,@currdate))-1,@startdate)), 7)
select DATENAME(dw,DATEADD(wk, DATEDIFF(wk, 7, dateadd(dd,(datepart(dy,@currdate))-1,@startdate)), 7))

Copy Objects Not Copying

As sited in past posts we are shop that is still in the process of migrating off SQL Server 2000. We have several jobs that use copy objects to replace production data with a minimum of downtime. The process flow is similar to the following:

• Load Data into Simple Recovery Database
• Massage Data doing updates/inserts to prepare for production
• Use DTS Copy Object Task to move tables to production database
• Rename current production tables to backup
• Rename copied tables to production name

 

This process works very well except when two copy object tasks execute in parallel mode. When this happens one of the two copies will fail referencing objects that are not in the database the task is working with.

 

In Microsoft's wisdom they default the script directory and filename and these are the same for each instance of the copy object task. If multiple tasks execute at the same time the files are overwritten with subsequent runs. When the process goes back to read the file for actions it picks up the new task, using objects not in the current connection.

 

The developer who was working on a solution to this issue found this URL that explains the situation and a fix:
http://www.sqlservercentral.com/articles/DTS/dtscopyobjectstask/1531/

 

Hope this will help someone else.

Clustering Gotchas

A couple of weeks ago my company clustered a SQL2005 install and I posted about the 6 core CPU issues that were experienced. This weekend we clustered a SQL2000 on SQL2003 environment. SQL2000 is end of life but we still use it until the financial guys approve our upgrades. So with one of our most critical servers running standalone we attempted the cluster.

This server had been clustered in the past under a design the previous DBA had created and sold using mountpoints. Mountpoints are not supported on SQL2000/Windows 2003 configurations. Timeout issues on the mountpoints caused the cluster to fail regularly.

The following issues and resolutions from this weekend are below, it was successful and within the outage window.
Temp and Tmp environment variables have spaces in the paths:
Microsoft sets the default location for the Temp and Tmp variables are in the folder of the current user. When installing SQL on a cluster this will cause the install to fail with a folder not found error. The folder exists but if the path has spaces the install fails. Change the environment variables to a path without spaces, I usually use c:\temp for both.

Timeout during install caused by multiple drives in the SQL cluster group;
Not sure what causes the timeout but to get around this issue move all physical drives, excluding those needed for the install to the Cluster group. Once the install is complete move them back to the SQL group. We have 20 drive resources in the SQL group, this was done to assist with EMC snapshots but that technology is not currently being used.

Clustering in SQL2000 is a far easier task than was SQL7.0. SQL2005 continues the improvement and hopefully so will SQL2008. As tasks become more and more straight forward DBAs will have to distinguish themselves by the other talents needed to excel. Performance tuning, database design etc. The administrative tasks will always be needed, but at least at my company, it takes more than just normal admin skills to succeed.

SQL2005 on 6 Core CPU

A couple of weeks ago we upgraded one of our enterprise systems to a clustered environment using Dell Hardware with 2 physical CPUS, 6 Core Hyperthreaded, showing 24 available CPUs in task manager, add in 32gb of memory and still stay under 10k. These were great boxes for the application a document storage retrieval system.

After windows was installed and we started on the SQL2005 initial install the setup would just fail and not report any usable error. What had started as a fairly simple upgrade turned into a nightmare.

After several attempts and several hours of scouring the web our systems guy found an article about SQL2005 not installing on a server where the cores were not divisible by 4. Who would have thought that MS would have been so short sighted, or that the error message would not have given some indication.

Once the system team modified the server to only show a single CPU the install went smooth and after applying SP2 all 24 processors put back into the pool.

Since this upgrade the server is running at under 10 percent CPU usage and very seldom has any type of wait condition.

Hope this helps someone else avoid a headache.

SSRS 2005 Migration

My company is working on a DR Plan that includes VM for recovery while new hardware can be ordered and systems built. Last week I had the task of moving our SSRS 2005 installation to new VM that in the end would have the same name as the original physical server.

Lets name the original server ServerA and the VM ServerB.

I found a couple of articles through Google which covered the process with good detail, the links can be found at the below.

After installing SQL2005 and SSRS on ServerB to match the versions on ServerA, I took a backup of the Source Databases, reportserver and reportservertempdb and restored those back to ServerB.
Ensured the configuration for both servers matched and then imported the Encryption Keys ServerB came up and worked, it would serve up the Reports, ServerB\Reports.

After shutting down ServerA we renamed ServerB to ServerA, performed the required updates on new ServerA for SSRS to be able to connect, link below and everything was good to go. So we thought.

Two issues arose after the migration, neither of which I could find a fix for through Google or other searches.

The first issue is that when IIS is installed two accounts are created IUSR_(servername) and IWAM_(servername), both of these retain their original name after the server rename. Our issue was that IUSR_ServerA owned subscriptions, these failed because the user did not exist. The system team renamed the account, IUSR_ServerB and IWAM_ServerB to IUSR_ServerA and IWAM_ServerA, this resolved the problem of the account not existing but it was missing some privileges so it was still unable to successfully execute the subscriptions, I ended up changing the owner of the subscription to the SQL Service Account and the subscriptions would generate. Reinstalling IIS may resolve this issue, but not sure what would happen to the SSRS configuration information.

The second issue arose when the subscriptions emailed links to the reports, the links included ServerB in the path, \\ServerB\Daily Reports...., resulting in a 404 error when following. Inside the config file referenced below there is a key named URLRoot, this required updating to the correct server name, C:\Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\Report Server\rsreportserver.cfg.
I didn't need to change the user/password as detailed in the first article.

Overall this was a fairly smooth process, the two issues were easy to resolve once found but caused unplanned report failures and production issues for users relying on the reports.

Migration Assistance:
http://blogs.egroup-us.com/?p=1600
http://support.microsoft.com/Default.aspx?id=842425

Renaming a SSRS Computer:
http://msdn.microsoft.com/en-us/library/ms345235.aspx

SQL2000

I work for an insurance company in North Texas that is running still running a majority of its SQL serves on SQL2000. We have requested budget for licensing of SQL2008 and have just received our first SQL2008 test server from the network group. It is in a segmented VLAN so only one user at a time will be able to test but it is a start.

I created this blog as a place where issues from SQL2000 to SQL2008 can be recorded and solutions posted. I'm not sure but I would think there are still quite a few SQL2000 installations out there.

As I find items to help out our developers be more effecient, they are a good bunch but very few have any database knowledge. With only 2 dbas at the company and 42 production servers, we serve in a consulting role.

xp_sendmail issues

After several weeks of wrestling with xp_sendmail failing with the error 'No such user or group (sqlusername)' it has been resolved. The error only showed up when the @query parameter was also in use. The solution was easier than I thought, each of the xp_sendmail commands that used the @query also needed the @dbuse parameter added.
This all started happening after I moved the system databases off of a mountpoint to a drive letter.
The company I work for runs on SQL2000 mainly, in the process of upgrading to SQL2008. Not sure what the actual cause of the change in behavior was but it has been resolved.