Booting to a VHD in Windows 7

This is something I tried before but didn’t complete for some reason. Now that I’m on vacation this week and I’ve got some extra time to play, I went back to finish it.

As you probably know by now, Windows 7 has the ability to boot into a .vhd file. This is awesome, as you can create a virtual testing environment that you can run directly against you hardware.

There are a few gotchas, though. You’re limited in the OS’s you can run on the virtual side to Windows 7 and Windows 2008. I’ve seen post of people getting other OS’s to run but I haven’t tried. And I’ve seen warnings not to do this on a laptop, though I’ll try it, once I install my new bigger laptop hard drive next week.

Anyway, this is how I did it. I had created a Windows 7 virtual machine in Virtual PC 2007. I used the vhd from that VM instead of creating a new one, though you certainly could if you wanted to.

The first thing I did was to run sysprep inside my VM. I’m not an expert on sysprep, I just followed instructions I found on the web. Briefly, sysprep is a GUI tool that prepares the image to be configured to use the hardware on the new server. You’ll find it in C:\Windows\system32\sysprep. Run it as an admin. Choose the default target of out of the box experience and also choose to generalize. Also choose to shut down. After sysprep is finished it will power down your VM.

I didn’t do this, but it’s probably a good idea to make a copy of the vhd at this point.

The next thing I did was to set up Windows bootloader to see the vhd file. I opened a admin command prompt and ran the following: bcdedit /copy {current} /d “Win 7 VHD”. This returns a GUID I saved to notepad. “Win 7 VHD” is the description I wanted to see on the boot menu. After that I ran these three commands:

bcdedit /set {guid} device vhd=[C:]\VM\Win7\Win7.vhd

bcdedit /set {guid} osdevice vhd=[C:]\VM\Win7\Win7.vhd

bcdedit /set {guid} detecthal on

In my example I replaced guid with the guid I saved earlier. VM\Win7\ is the path to my vhd file, and Win7.vhd is the file I’m using. Note that the drive letter is in square brackets: [C].    

And that’s just about it. Once I restarted my computer I could see both my original Windows 7 installation and my new vhd boot option. When I chose the vhd, Windows started and applied the hardware changes. After that I just logged in and ran my Windows vhd. Once in the virtual environment, I can see all the drives on the computer, including those for my “real” Windows 7. Notice Disk 1 is a blue icon; this shows that it’s a vhd file. It also shows the reserved system partition. I can also see the files on the other physical drives.

I don’t get this if I’m running my physical Windows 7. I can mount the vhd file (on the menu go to Action > Attach VHD). But it doesn’t stay mounted between reboots. I haven’t tried mounting it with DISKPART yet, I’ll try that when I create my laptop VM.

 

The only drawback is the vhd is not portable, and I can’t run it in Virtual PC 2007 anymore. I can probably run sysprep again to get it back, but I think I’ll keep it as it is for now.

Year End Wrap-up

This is my wrap-up for 2009 and a preview for 2010.

A big chunk of my year was testing virtualizing our production servers with different configurations. After months of testing we learned that our workload wasn’t conducive to virtualization.

During the last few months I began testing SnapManager from IBM for use as a backup tool. While SnapManager is a great tool for bringing a server online quickly, I can’t see a way of restoring individual objects. Unfortunately in our shop it’s far more likely to have a table accidentally updated or deleted than have a whole database go corrupt. I’d like to find a way for both to work, having the best of both worlds.

I started to learn Microsoft’s BI stack. I’m already pretty familiar with Reporting Services, but I’ve only played around with Integration Services and I’ve done nothing with Analysis Services. So far I’ve just scheduled a simple package to collect database sizes so I can track growth. I’ve got a long way to go.

I’m on vacation this week, not going back to work until January 4th. I’m using my downtime to create a virtual Win 7 pc to run all the 2010 betas coming down. I also want to set it so I can boot directly to it, using the new functionality in Windows 7.

So what’s coming up next year?

• I’ll finish my testing of SnapManager and I’ll post my results.
• I’m going to attend the first SQL Saturday in the Chicago area in April. I’ll post info about this early next month.
• Continue my education into BI using the SQL tools.

One of my resolutions for next is to continue studying new technologies – new to me that is. I want to become more proficient in Powershell and Silverlight, among others.

Happy holidays to all, and I’ll see you next year.

Virtualization – final wrapup

This is another topic I want to wrap up before the end of the year.

A quick overview. earlier this year, I did some testing of virtualizing our production servers hosted on VMWare servers. I captured a typical workload from our busiest server and replayed it on servers set up for testing; a 32-bit physical server with 16 CPU set up as a production server, the same server with hyper threading turned off, a 32-bit and a 64-bit virtual server with 4 CPUs,and a 32-bit and a 64-bit virtual server using vSphere and 8 CPUs. All servers had 16 GB of RAM.

After running the workload multiple times on each server configuration we compared results. What we saw was understandable – neither the 4 CPU or 8 CPU servers matched the 16 core baseline. Even the physical server with HT off fell short.

What I didn’t show in earlier posts was the counter for latches. Latches are the #1 wait type on our servers, and this held true on all testing servers. Our servers aren’t running optimized and this is being magnified in a virtual environment. Having 16 cores lessens the performance hit from all those latches in production.

So we’re not going to virtualize,at least the production servers. While virtualization is useful and can be used for SQL, it’s not a good idea to try and virtualize servers with performance problems. We’re currently using VMWare to run our development and testing environments as well as a few smaller, less used SQL servers, and we’re not seeing any issues with them.

I’m not going to published a summary of all my counters, but I’ll make them available on request if anyone is interested.

Testing Google feeds - ignore

Testing Google cross-posting

What I learned today – temp file created when reading from multiple trace files

I’m going to do some updating on past posts before moving forward on my BI learning. Today I want to close the book on using Profiler.

First, there’s an excellent series of three webcasts on Profiler on the MSDEV website. MSDEV is a great resource for anyone looking for webcasts on Microsoft developer products. Check out the site even if you’re familiar with Profiler, chances are you’ll find something you didn’t know before in one of the other webcast series.

Second, Profiler will create a temporary file when you read from multiple trace files. Like a lot of things, I found this out by accident. I had created a trace for replay on one of our production servers. It wasn’t until the trace had been running for a while that I noticed that I had forgotten to change the maximum size of the trace files. I let the trace complete, knowing I could read them all later. My trace ended up creating 369 .trc files totaling about 1.89 GB.

So I began reading all of the .trc files, meaning to save the results into one .trc file. While the trace was running, I noticed that the amount of free space on the C drive of my test server was dropping. It eventually bottomed out when the trace finished reading the files. And when I investigated, I found a file in my Local Settings\Temp folder that was the same size as the total size of the 369 .trc files. The temp file was deleted automatically when I closed the Profiler session. Finally I opened the single large .trc file I saved when reading the multiple smaller .trc files. This time no temporary files at all were created.

I wasn’t expecting the temp file to be created. Luckily this happened on my test server where i had enough free space. But it’s something to consider anyway. You may easily run out of space on your C drive without knowing why. I don’t know what would happen if I read the same trace with fewer, larger .trc files, that’s something I’ll need to look at for sure.

What I learned today – GID in sysusers table

I had a question from a colleague yesterday about the GID field in the sysusers table. He had a SQL 2000 database that he had moved to a SQL 2005 instance. When he did this, his third party application stopped working. When he investigated, he noticed that the app was using the GID field in sysusers to validate logins and set roles. After he restored the database on the new server the GID field was 0 for everyone. At this point no one could connect to the database through the app.

In SQL 2000, when you add a user to a database, the GID is 0 for the public role. If you add them to a database role, the GID becomes the UID of the role. I’m not sure what happens if you add a user to more than one database roles, that’s something I’ll have to check out. In SQL 2005 this has changed. The GID for all database users is 0. This is not a field that you can update.  

This is a good example of why you should never depend on the system tables being static. Microsoft always warns that they may be updated when you apply patches or when you upgrade.

SSIS Expressions Cheatsheet

Maybe you’re new to SSIS, like I am, or maybe you just need a little refresher on expressions in SSIS. I just ran across the SSIS Expressions Cheatsheet. This is a page on the PragmaticWorks website. It’s also available as a downloadable .pdf file. For me, anyway, it’s a great one-page reference. I’ve got it printed and pinned to my cube wall.

I learned about this resource from Dougbert.com

My first data warehouse – Part 1

I’m going to lay a little ground work for future posts here. My plan to learn BI, and also to create a useful data warehouse from scratch, is to build one based on reporting server performance for my company’s eventual use. I want to be able to drill down to an individual report to view performance data, as well as summarize report performance by locations, by dates, by users,by date ranges, as well as other factors that I don’t envision right now.

Today I’m going to discus the download I found on the CodePlex site, thanks to Tyler Chessman from SQL Magazine. This package and the accompanying reports will be incorporated in my data warehouse project.

To use the downloaded code you’ll need to create a database. The examples use RSExecutionLog as the database so I kept the same name. The download includes a script to create the necessary tables and relations. The tables it creates are used to store data extracted from the ExecutionLogStorage table in your SQL 2008 report server database. Mine has the default name of ReportServer, created when I configured Report Server for the first time. This database stores the report catalog and also useful information pertaining to when reports are run.

There are two Visual Studio solutions in the download; one SSIS solution called RSExecutionLog with a single package called RSExecutionLog_Update.dtsx, the other is a reporting solution with three sample reports to use against the extracted report server data and two to view schema and table information. I’m going to concentrate on the first two. 

I had some original issues with the package and reports. They were written using SQL 2005; I’m using SQL 2008. After converting the reports (basically just opening the report solution in VS 2008) I still had issues with the package and it’s connections. There were places where the connection to the source or destination seemed to be hard-coded inside different components of the package, even after I changed the source and destination connections. I ended up building a new package based on the original.   

The package is pretty sweet. Not only does it populate tables for users and reports, it also creates one for the report parameters. When the parameters is stored in the report server catalog, it’s something like “PARAM1=123&PARAM2=456”. The package shreds the report server string into individual values, so now I can easily search by parameters. I was thinking of doing the same thing, only storing the values as one record as opposed to one or more.

So I can use this package with very little customization. My next step is to pull data into my dw that is dependent on each report execution. This package should be fairly straight forward.

My next post will show my database design.     

What I learned today – Report Server Execution log package

My first attempt at a data warehouse is going to be collecting data from our report servers. In our environment we have two SQL 2008 instances that host the reports themselves, and they connect to four data centers for the report data. In the data centers we have a table that’s populated with parameters needed for each report; dates, locations, etc. The reports themselves only have two parameters; the datacenter where the data for the report is (we’re using dynamic connection strings) and a guid that identifies the other needed parameters in the data center.

My goal was to build my warehouse from the four data center report parameter tables and the Execution Log tables on the report servers. The report server logs information from each time a report is run; if it was successful, the parameters for the report, the user, and more that would be helpful in debugging a report performance. I wanted to be able to view summaries for each report; for instance how often each report was run for each location during a specified date range, average report durations, or the number of aborted reports.

I was going to build an SSIS package to gather the data for me. Then I read an article in the November 2009 issue of SQL Server Magazine by Tyler Chessman that defined about half of what I want to do. In his article SQL Server Reporting Services Questions Answered, Mr Chessman describes sample reports from Microsoft that you can find on CodePlex. The reports will be interesting enough and I’ll be able to use them, but the best part is Microsoft has already created a package to extract the data from the report server execution log!

This post is meant to be an overview. I’ll post a review of the CodePlex samples soon, and I’ll start laying out my data warehouse design.

What I learned today – auto generate create table DDL in SSIS

I’m pretty new to SSIS, only having played around with it to see how things work. This is probably well known to SSIS developers but I just ran across it accidentally. I’m learning SSIS and I’m currently working through Brian Knight’s book “Knight’s 24 – Hour Trainer Microsoft SQL Server 2008 Integration Service”(I’ll review the book when I’m finished). Lesson 15 is walking me through loading the contents of a flat file into a new table in a SQL database with the OLE DB Destination.

When I’ve done this before, I’ve always written a DDL statement to create the table. This time, I connected the Flat File Source, which has my sample data, to the OLE DB Destination.This time, when I clicked to create a new table, SSIS generated the CREATE TABLE script based on the definition of the flat file! The only thing I had to change was the table name, and even this would probably have been handled if I had renamed my destination first.

 

I didn’t think the table would be created until the task was run. But after clicking OK, the table was listed in my test database. I hadn’t even finished defining the destination!

SQL Saturday in Iowa

SQL Saturday in Iowa has come and gone. It was a great conference, and it’s a shame that we can’t get a day like this somewhere in the Chicago – Milwaukee – Madison region. Hopefully soon.

But back to the SQL Saturday. Thanks to the East Iowa SQL User Group for putting this all together, bringing in the speakers, finding the venue, lining up the sponsors, and doing all the little things that make an event like this possible. There were only a few minor glitches with equipment (projectors mostly). Registration was probably the easiest I’ve ever seen for any event. Food and beverages were both tasty and plentiful all day long. And the sponsors were only there for the event, not to sell products.

But the best part of the event was the content. I can only comment on the sessions I attended but I’m sure that the rest were equally as good. I have a list of the sessions offered in an earlier post.

I didn’t go to Alex Kuznetsov’s sessions since I’ve seen them. The first session I went to was Janis Griffin’s Tuna Helper. She went over a process that Confio uses to spot issues on servers using wait types and DMVs. Next up were the three BI sessions that Jessica Moss presented. The session on reports was basic, probably because that’s the part of the BI stack I’m most familiar with. And her session on Database Dimensions in SSAS had a glitch with an overheated projector and became more of an overview of SSAS itself, which was fine with me as I know very little about SSAS. The session on SSIS was great. I may finally be getting a handle on data warehousing dimensions and facts thanks to her.

Tim Ford also had an issue with a bulky projector so his session wasn’t as informative as it would have been. But I have the links to his blogs where he’ll have his scripts. They’ll come in handy. And finally Louis Davidson gave his second session of the day, on database design patterns. This was my first chance to see Louis present live. He’s a great speaker. 

Sidekick data loss should never have happened

It was just a coincidence that I posted earlier on cloud computing. I took a long time writing a simple summary of the TechNet and MSDN events I went to on the Azure services. I was (and still am) impressed by the potential of cloud services.

Over the last few days some details are coming out about what happened with T-Mobile’s Sidekick. T-Mobile was using Microsoft’s cloud computing to handle Sidekick data. At some point Microsoft performed a SAN upgrade and outsourced the job to Hitachi. What is completely baffling is that there was no backups taken prior to the upgrade (you can read a better summary at HipTop3.com).

How in the world could Microsoft and/or Hitachi have neglected the backup step?  This should have been caught by the most junior member of the team running the upgrade. It’s going to be interesting to hear how they try to spin this; all parties are equally to blame.

And how does Microsoft try to sell their cloud now?

Microsoft Azure event summary

On October 1st I went to the Technet and MSDN events on Azure. I’ve never really read much into what Azure was all about so I wanted to learn more about it; what it was, and most important what it could do for me.

There are a few data centers that Microsoft built for their Azure services. They just finished one here in Chicago, there’s one in Dublin, and I believe the third is in Quincy, Washington. It’s pretty cool how they set it up. Dell loads up semi trucks with the components needed. The trucks are shipped to the data centers where they are plugged into the network. I don’t think they use USB cables. Each data center can hold, I believe, 350,000 nodes.

Azure is actually three parts. The first is Windows Azure. This is exactly what it sounds like, running on a Windows Server platform.

The second part is SQL Azure, again no surprises. There wasn’t much talk about SQL Azure other than to mention that it was available. However the costs seemed to me to be fairly high; 1 GB for $9.99 per month, or 10 GB at $99.99 per month. You would probably be better off finding a different hoster, there are cheaper options available.

The third part of Azure is .NET services. This would be your messaging services. Again, there are probably cheaper options. Microsoft wants $.15 per 100K per message.

On top of all this there are the bandwidth costs; $.10/GB inbound and $.15/GB outbound

West Monroe Partners also gave a demo at the end of the TechNet event. They build the website the City of Chicago used for the Taste of Chicago last summer. It was impressive, especially since this was 3 months ago on a CTP build.

At Microsoft today

I’m attending the TechNet and MSDN events on using Azure, cloud computing. Some cool stuff on tap. I’ll post about the individual sessions later.

Lessens learned – Report Server administration

It’s a long commute in the morning, so I thought I’d use this time to compile a few things I learned and noticed about administering a server hosting SQL 2005 or 2008 Report Services.

• Check the recovery model of the databases you create on your report server, including the ReportServerTempdb (or whatever you named the SSRS temp database). By default they will be in full recovery mode unless you’ve changed your model database. If they are still in full, the log files will grow and may cause you space problems later. Consider using simple mode. If you use full remember to schedule log backups.
• Backup the SSRS tempdb as well as the database holding your catalog. You’ll need this database if you need to restore the server, it’s not recreated when SQL starts up like tempdb. I’ve seen a few maintenance plans that skip this.
• Speaking of backups, remember to backup the SSRS encryption key. You’ll need that key if you ever want to restore the catalog database and you don’t want to re-enter all encrypted content, like data source credentials. I’ve successfully moved catalogs from production to a testing server using the key to restore the connections. You can backup the key from the Report Server Configuration Manager. I suggest that you also copy the backup somewhere safe in case you can’t get to the server.
• If you don’t want your reports exported in a specific format then disable that format. For instance, not a lot of reports look good if you export them in a comma delimited format. Just remove or comment out the entry in the rsreportserver.config file. You’ll find this file in the path for your<instance path>\Reporting Services\ReportServer. Be careful removing the image format – this could cause some problems, especially if you open the report in the report viewer control available in Visual Studio.

<Render>
            <Extension Name="XML" Type="Microsoft.ReportingServices.Rendering.DataRenderer.XmlDataReport,Microsoft.ReportingServices.DataRendering"/>
            <Extension Name="NULL" Type="Microsoft.ReportingServices.Rendering.NullRenderer.NullReport,Microsoft.ReportingServices.NullRendering" Visible="false"/>
           <!-- <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering"/> -->
            <Extension Name="PDF" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PDFRenderer,Microsoft.ReportingServices.ImageRendering"/>
            <Extension Name="RGDI" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.RGDIRenderer,Microsoft.ReportingServices.ImageRendering" Visible="false"/>
            <Extension Name="HTML4.0" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>
            <Extension Name="MHTML" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.MHtmlRenderingExtension,Microsoft.ReportingServices.HtmlRendering"/>
            <Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering"/>
            <Extension Name="RPL" Type="Microsoft.ReportingServices.Rendering.RPLRendering.RPLRenderer,Microsoft.ReportingServices.RPLRendering" Visible="false" LogAllExecutionRequests="false"/>
            <Extension Name="IMAGE" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRenderer,Microsoft.ReportingServices.ImageRendering"/>
            <Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering"/>
</Render>





• You’ll find some good information in the ExecutionLogStorage (SQL 2008) or ExecutionLog (SQL 2005) tables. These tables can help you troubleshoot problems with the report as it shows the name of the person running the report, the time it took for retrieving data or rendering the report, the rendering format, and any parameters used. It will also show if the report was opened successfully or if it couldn’t open because of a rendering issue.



• Another place to look at during troubleshooting is the logs. You’ll find these at <instance path>\Reporting Services\LogFiles. You may need to clean these files up on occasion since some report errors will generate dump files.

What’s the best practice for starting SQL services?

An incident that occurred to us this morning causes me to rethink how I have SQL services set to start when the server is started or rebooted.

Early this morning we had a power failure at one of our data centers. It caused one SQL server to be rebooted and left another server online. But it caused the SAN controllers to reboot also. Server 1, which didn’t reboot, began reporting error 823 when it couldn’t see the databases, even after the SAN came back online. Restarting the SQL services cleared that up. Server 2, which did reboot, came up reporting no problems. Consistency checks on all databases showed no problems so luckily we were good to go.

I have the SQL services set to restart automatically when the server starts. But now I’m wondering if that is a good idea.What if the SAN wasn’t available when SQL first restarts? What if there is data corruption? Would it be better to always start the services manually so I could check the logs first, before I restart SQL?

I’m interested in hearing how you handle SQL service startups.  

SQL Saturday in Iowa Oct 17th

The East Iowa SQL User Group just listed the sessions for the SQL Saturday event in Iowa City on October 17th. There’s some good stuff scheduled. Go to SQLSaturday.com to register or email sqlsaturday#19@sqlsaturday.com for more information. They can always use more sponsors, too.

Track	Start Time	Session	Speaker
Admin / Platform	9:00 AM	High-Availability Options for Microsoft SQL Server	Tim Plas
App Dev I	9:00 AM	Tuna Helper - A Proven Process for Tuning SQL	Janis Griffin
App Dev II	9:00 AM	Defensive database programming	Alex Kuznetsov
Admin / Platform	10:00 AM	The Benefits of SQL Azure	Aaron King
App Dev I	10:00 AM	ABCs of CTEs	Jason Strate
App Dev II	10:00 AM	SQL Server 2008: The New and Cool...	Zakir Durumeric
BI / Relational Theory	10:00 AM	Building Reports in SQL Server Reporting Services	Jessica Moss
App Dev I	11:10 AM	Extended Events, Work Smarter Not Harder	Jason Strate
App Dev II	11:10 AM	2 Years of Database Unit Testing: Lessons Learned	Alex Kuznetsov
BI / Relational Theory	11:10 AM	Extract, Transform, and Load your Data Warehouse	Jessica Moss
Admin / Platform	1:00 PM	Service Broker: A Brief Introduction	Zakir Durumeric
App Dev I	1:00 PM	Introduction to PowerShell	Keith Dahlby
App Dev II	1:00 PM	SQL Server 2008 - Practical Uses for New Features	Chuck Heinzelman
BI / Relational Theory	1:00 PM	Introduction to SSAS Dimensions	Jessica Moss
Admin / Platform	2:00 PM	Tips Tricks For Administering SQL Server	Timothy Ford
App Dev I	2:00 PM	Drive Your Way to the DMV	Jason Strate
App Dev II	2:00 PM	SQL Server 2008 R2 - Moving to the Next Level	Chuck Heinzelman
BI / Relational Theory	2:00 PM	Database Design	Louis Davidson
App Dev I	3:10 PM	Improving Daily Imports with Partitioned Tables	Jason Strate
App Dev II	3:10 PM	An Introduction to ASP.NET MVC	Chris Sutton
BI / Relational Theory	3:10 PM	Database Design Patterns	Louis Davidson

Report Service error

Yesterday I helped a developer troubleshoot an issue he was experiencing with a report. The report is hosted on a SQL 2008 instance used for testing and was being viewed in a Win Form application using the Report Viewer control. We congigured the viewer control opens the reports as an image since this would be what the client sees when they print it. He couldn't tell me the exact error message but it referred to not being able to find the stream. He also stated that the report worked fine if he ran it with less data.

I first checked the execution log table in the report database and I saw he was getting a rendering error. Next I had him rerun the report in the viewer. This time he received a generic error saying that there was a problem. But if he clicked the page layout button to render the report in HTML the report ran fine.

My next step was to check the logs on the report server instance. Sure enough I found the error. It was an IO issue; not enough space on the drive. Luckily this was on a testing server and not production.

Now the testing server is not set up the same as a production server. This box is a jumble of programs and databases, running multiple SQL instances for 2000, 2005, and 2008. The report server temp database is on the same drive as the report server database and the error logs and dumps. At the time I saw this the drive had about 100 MB free. And while 100 MB isn't a lot of space nowadays, I would have thought it would have been enough to run the report (it had over 800 pages when we freed up space for it).

At this point I'm not sure where that image is being produced, in the reports database or the report temp database. I've never dug into the report temp db before. I'd like to head from anyone who has had similar problems, or can show me where else I should be looking.

Updates to previous posts

In my posts on virtualization I forgot to mention something. While I did say I was running with the multi-thread option instead of the replay in order option, I left the number of threads at the default of 4. I ran it with 64 threads earlier, but with the replay in order option. Those replays took well over 5 hours, as opposed to 2 1/2 – 3 hours with multi-thread at 4. But if I compare multi-threading at 4 with multi-threading at 64, then the times are much better – 3 1/2 hours compared to 2 3/4 hours on the same virtual server. I’ll summarize all my tests in a few weeks when I’ve completed all my tests.

The second post I need to update regards repeating data in a page header using SSRS. I solution was to use a shared variable. This had potential problems which I neglected to mention. If you use a shared variable then anyone running the report at the same time could overwrite each other values. I knew this at the time I put the report into production, but I thought the odds of that happening would be slim. Later I found I could produce that exact behavior in testing; when I ran the report simultaneously with another tester we did indeed cross values.

I changed the report to use a hashtable instead. The value in each record would be unique since it will depend on the user id of whoever is running the report. As an example of repeating a customer name on each page I

1. Added a hidden row to the top of the table control on the report. The table was already there and the grouping was already set on the customer name.
2. Added the customer name field from the table’s dataset to the hidden row.
3. In the report code section I added a shared hashtable – DIM htCustomer AS system.collections.hashtable = NEW system.collections.hashtable. The hashtable still needs to be shared.
4. Still in the code section, I added a new function to add a record to the hastable if it didn’t exist or update it if it did. The function takes three parameters; the value from the hidden field, a group name, and the user id. It returns the customer name for the current user

DIM key AS STRING = groupName & UserID
IF NOT group IS NOTHING
   DIM g  AS STRING= CTYPE(group, STRING)
      IF NOT (htCustomer.ContainsKey(key) THEN
          htCustomer.Add(key, g)
      ELSE
          IF NOT (htCustomer(key).Equals(g)) THEN
              htCustomer(key) = g)
          END IF 
      END IF
END IF
RETURN htCustomer(key)

5. Add a page header to the report. Add a new text box that uses an expression to call the function and pass in the parameters

=Code.SetCustomerName(ReportItems!txtCustomer.Value, “Customer”, User!UserID)

ReportItems!txtCustomer is the hidden field; Customer is my group name, and User!ID is a global parameter available to the report.  

Job Scheduled for Sunday

It's scheduled to run about 7:30PM

DBCC GoBears

Documenting servers

I'm starting to create a Run Book (not my term, but I don't remember where I heard it) to store all documents relating to the databases and their servers I'm responsible for. That list is growing, and before it gets out of hand I'd like to create a template of what needs to be included. I'm trying to determine just what needs to be included. Drafting the template should help me not to go back and redo what I've already done.

At this point I'm only documenting the servers, not any processes like database backups and restores. Those are important and I've already done most of those, just not formally. I'll do that also.

I'll start with the server itself. What I'm adding is the server name, ip address, if the server is physical or virtual (if virtual the name/location of the host server), the OS and any SPs of patches installed, number of CPUs, amount of memory, size/location of the page file, and the drives and total space. If the drives are on a SAN then I should include a mapping of the SAN.

For the SQL installation, the edition and any SPs or hot fixes, the instance name (we only run one instance per server), any non-default options that were configured, accounts used to run services, configured alerts, jobs and their schedules, if components like SSRS are installed and info about their configurations if they are.

For the databases, the db names (including the virtual name and physical path), compatibility level, recovery model, auto growth and size restrictions, and any db options.

For database backups, the type of backup, where it backs up to, and when it backs up. This info may be duplicated since our backups are handled by jobs.

Is there anything else I'm missing?

Alas, no booting to a .VHD for me

I’ve been reading quite a bit lately about attaching a virtual drive to a computer in Windows 7 and Windows 2008 Server R2. It would be nice to have the ability to boot directly into a virtual server. But I’m not seeing a way I can achieve it, at least right now.

My hardware would be my current laptop, an HP Pavilion TX 2000 (I wanted the tablet capabilities) running a 64 bit version of Windows 7 Ultimate. It has 2 CPUs, 4 GB of RAM and a 250 GB hard drive, certainly enough to run one virtual machine. I wanted to run a virtual 64 bit Windows 2008 Server R2 as my BI sandbox. Unfortunately, neither Virtual Server or Virtual PC support running 64 bit guests, only 32 bit. So I built my VM using VMWare’s Workstation. But those virtual disks can’t be mounted.

So I created another VM, this time a 32 bit version of Windows 2008. I created a fixed disk, installed the OS, and followed the directions on Charlie Calvert's blog. Mounting the .vhd file was simple, as was using bcdboot. When I rebooted both servers showed in the boot launcher. Everything good to go, right?

Wrong. When I tried to boot into the .vhd, I'd get an error message that the computer was not correct because of hardware changes. And the computer manager no longer showed the .vhd drive as mounted. That’s when I went back and reread the fine print.

Windows 7 only supports mounting Windows 7 or Windows 2008 Server R2. My VM was only 2008. At this point my options are to restore my VMWare VM (backed up luckily) or to try to install R2 directly into a vhd. But I don’t think that will work, either; it wouldn’t be compatible with Virtual PC.

Well, VMWare Workstation is still a great option. It’s just disappointing I haven’t figured out a way to do this yet.  

East Iowa SQL User Group meeting

This may be one of the nuttiest things I’ve ever done, at least from a technical side.Yesterday I drove to Cedar Rapids to attend the monthly meeting of the I380 Corridor (East Iowa) SQL User Group. That’s a 9 hour round trip drive for a 2 hour meeting, not a very efficient use of my time. But I was glad I did.

The East Iowa group is headed by Michelle Ufford, writer of the SQL Fool blog and Ed Leighton-Dick. Last night’s meeting included a tip of the month by Michelle on using Adam Machanic’s latest version of his who is active script. This was followed by a Live Meeting with Jessica Moss who gave an introduction to data warehousing. That’s what I came for. Ed taped the meeting and hopefully it will come out clean enough so they can post it. I’d love to see it again.

Jessica discussed just what data warehousing is. She went into the design and life cycle of a dw, the definitions of fact and dimension tables, the types of dimensions, the differences between a star and snowflake schema, and how SSIS, SSAS, and SSRS fit into all this. She also discussed the different methodologies, Kimball and Inmon. Finally she gave a demo of a dw, from ETL to reporting, using the AdventureWorksDW2008 database. There was only one question, from Michelle, about using procedures to load data as opposed to a Slowly Changing control in SSIS. Jessica recommended using the SSIS approach.

Jessica’s presentation was exactly what I was looking for, a beginning point for how to build a data warehouse. There’s a lot of information and discussions out there but most of it assumes some knowledge already. And classes usually focus on the tools, not the methods. I need to learn both if I’m to create a good design.

Everyone at the user group was friendly. It was a small gathering, maybe 15 people total. The only difference between them and the Chicago group is they cut their pizza in wedges rather than squares.  

There was one piece of bad news, however. The Iowa group is sponsoring a SQL Saturday on October 17th in Iowa City. But they are far behind what they need as far as registered attendees. They need to hit a certain level for funding from their sponsors to kick in. I think they are running into an issue where there’s another SQL Saturday scheduled the same day in Orlando and others in October. That’s a shame, because I really want to go to this one. Jessica is supposed to come and expand on last night’s presentation. They’ll make the decision Friday if they need to cancel.    

Replaying Traces – What I’ve Learned

So I’ve been running these replays for a while now, with mixed results. There are a number of  lessons I’ve learned along the way.

• Check what’s running inside the workload you’re replaying. There may be something that will affect the replay. In my case there was a collection trace that was started by the third party software I use for monitoring the production servers for performance. Since this trace had no corresponding stop, it kept running after the rest of my workload was completed, making me think there was a different issue. The only thing I don’t understand is why it stopped on it’s own in half the replays.
• After you load the workload, you can apply a filter and reload the workload. This is how I eliminated any call for sp_trace_setstatus.
• Restore the master database to eliminate any replay errors due to mismatched database id’s. This comes directly from BOL. I was ignoring it because at first I wasn’t seeing any errors. But later, especially after I started on the virtual servers, I was seeing more and more errors where SQL wasn’t running against the right database. So when I started the last round of replays I restored the master db first.

I’m in the process of replaying all scenarios again so the data is consistent. So far I’ve completed the 4 physical tests. Coming next are the 3 32-bit virtual, 2 64-bit virtual, and 1 final with VMWare’s 8 CPU server.

24 Hours of PASS

If you haven't already heard, PASS is hosting 24 hours of PASS on September 2nd. These are a series of free, one hour long webcasts on a variety of SQL 2008 subjects. You can check out the schedule on the PASS website. The first webcast is actually at 7 PM on September 1st Central Time. It's not too late to register.

First look at SQL 2008 R2

I’ve started to install the CTP of SQL Server 2008 R2. The first change I ran across was this screen during installation. I don’t have a copy of SharePoint Fourteen(?) so I chose the more familiar SQL Server Feature Installation. I’m installing this on a virtual server so I can always change options later.

Replaying traces, Part 5

So I’m now testing my workload on a virtual 64 bit server, again hosted by VMWare, again with only 4 CPUs. And again I find more issues.

I ran this replay 3 times so far. All three times the replay appeared to have hung at the 99% mark, similar to the 8 CPU physical test. The only difference was that, for the final test, I didn’t save the results to disk. I did this because on the second test there appeared to be SQL activity that was suspended by the TRACEWRITE wait type. The drive it was writing to was the only virtual drive; all databases are on SAN storage.

While all three hung at the 99% mark after about 3 hours, each test had different results. The first test was showing transactions against the database throughout the trace (the black line. The blue line is % Processor Time, the yellow line is % Privilege Time, the red line is transactions/sec against tempdb). The Processor time showed activity from the Profiler after the 99% mark .
The second trace showed no transactions after the 99% mark. This is the one that showed the TRACEWRITE wait type. Notice that there are no transactions against the db after the 3 hour mark this time.  
Test 3 shows the same pattern as test 1. There were no wait times for tests 1 and 3.

There is one more issue with these three tests. After stopping the replays, I had to manually kill the Profiler process because profiler became unresponsive. The third test stayed alive and was stopping the process and I let it go. However the next morning I checked and it was still “stopping” after 13 hours. And the Profiler was still responding.

The more I run these replays the more confused I get. I’m going to try one more time. This run will be from a remote machine, I’m only going to capture the counters on the virtual server.

(Mostly) disappointing meeting

Last night I went to the monthly meeting of the Chicago SQL User group, held on the second Thursday of the month. You can check them out and register for meeting notifications here. This is a good resource for anyone wishing to attend. Anyway,there were two topics last night. The first was on SQL and Powershell, the second on clustering with SQL 2008 and Windows Server 2008.

I don’t have any experience with clustering since we don’t use it at work but it’s one of those topics I feel I should know more about. But last night I was really looking forward to the discussion on Powershell. I’m looking for reasons why I should use Powershell over other methods like SMO or WMI to monitor servers I’m responsible for. I wanted to see some amazing Powershell cmdlets in action.

Unfortunately the presenters of the Powershell segment were not prepared very well. It started with having problems connecting a laptop to the projector and went downhill from there. The demos were filled with errors; most flatly didn’t work. The first presenter dwelled too long on the basics of Powershell itself, the second barely did more than read from the slide deck. Their presentation ate into the second segment on clustering. I wonder if their book was written better.

The second presenter did a much better job. He knows his topic extremely well, being a Microsoft MVP on clustering, and you can tell he is passionate about the subject. And he’s an effective speaker. But since he didn’t have much time he had to cut his demos short.

Don’t get me wrong, I appreciate everyone who presents at these meetings. It’s not easy getting up in front of a room of 80 strangers and talking about a deep subject in a short period of time. I just wish they could have presented their material in a more effective manner.           

SQL Server 2008 R2 CTP is now available

Earlier this week Microsoft announced that the first CTP for SQL Server 2008 R2 is available for download. You can get a copy through TechNet or MSDN, or on the site page here.

According to Mary Jo Foley, R2 is tied tightly in with the upcoming Office 2010 release. Both can be expected in the first half of 2010, around May or June.Personally I’m hoping for April, before my TechNet subscription runs out.

Technorati Tags: SQL 2008 R2,CTP,SQL

Replaying traces, Part 4

So far we’ve replayed the same workload on 3 virtual servers, each with 4 CPUs, and 3 physical servers, 2 with 4 CPUs and 1 with 16 CPUs. We also ran one with 8 CPUs but that replay appeared to hang at 99%.

All the physical replays showed the same trends. A 16 CPU server performed better than 4 CPUs, Hyper-threading off performed better than hyper-threading on. The average for IO and transactions/sec followed the same curve; the longer the replay time the lower the average.

But the virtual server replays showed a different trend. All of these completed in less time

than the 4 CPU tests on a physical server. And the averages for IO and transactions/sec were noticeably less. Since the workload was the same in each case we expected the averages to be consistent with the trend set by the physical since the total number of transactions and IO should have bee the same across the board.

I’m guessing that there were more deadlocks on the virtual than the physical. In all cases the CPU usage was the same, averaging higher than 80%. If deadlock victims are rolled back it would explain the decrease in total IO. But I don’t know if a rolled back transaction is still considered a transaction by the Windows counters. This is going to take some research.

We have some more tests scheduled using a 64 bit virtual server and one test using VMWare’s newest host that supports virtual servers running 8 CPUs. 

Technorati Tags: SQL,replays

SQL Server 2008 Training Kit is available

One of the things I like about Microsoft is the wealth of training they make available for their products. There was a training kit for ASP.NET, one for Visual Basic 2008, and now there’s one for SQL Server 2008. You can get yours at http://www.microsoft.com/downloadS/details.aspx?familyid=E9C68E1B-1E0E-4299-B498-6AB3CA72A6D7&displaylang=en

Replaying traces, Part 3

I had a few surprises when I began replaying a production workload on a virtual server. I'm using the same workload I captured from a production server and replayed on a physical server in different scenarios. The first virtual test was on a 32 bit server hosted on a VMWare box with CPU affinity turned on. A limitation of virtual servers (at least for now) is that they are limited to 4 CPUs.

The first surprise was that the replay was faster than the 4 CPU test on a physical server, 3:07 virtual compared to 3:40 physical. The second surprise is that the total number of transactions appeared to have dropped.

I'll be looking into this to see what I'm missing, and I'll post more details soon.

Replaying traces, Part 2

When I left off, I was discussing using SQL Profiler to replay a trace taken on a production server on different test boxes with different configurations. The end result is to see if we can virtualize our production OLTP database servers.

Our next step in testing was to test replaying a trace on the same server it was captured on. We couldn’t do this on a production server so we used our testing server and assigned our developers to hit the database with our application for one hour. Afterwards we restored the databases to the same state they were in and replayed the trace using the same options we use on the other server tests; using multi-threading, not displaying results, and not replaying server SPIDs. The replay of the trace took only 45 minutes. We probably didn’t capture a large enough workload. But we did rule out the overhead of running the replay when comparing to performance of the production server when the workload was captured.

Back to testing. Since we can’t exactly duplicate the replay of the workload to match the production server, we decided that running the replay on the test server would be our new baseline since we could duplicate that for every test. Testing on the physical box showed the following:

	Production	16 CPU (new baseline)	4 CPU, HT ON	4 CPU, HT OFF
Time	1:07	2:37	3:40	2:44
Avg CPU	17%	24%	83%	77%

We expected the 4 CPU tests to take longer and use more CPU. We were surprised that the 4 CPU test with hyper-threading turned off performed better than with hyper-threading on. So we ran one more test, using all physical processors and turning ht off. This gave us 8 physical processors to work with. This test, though, was incomplete. We ran it twice and both time the replay appeared to hang after completing 99%. Both times we needed to kill the replay after 5 hours. The counters we captured weren’t valid.

I’ll post on testing on a virtual server in a future post.

Technorati Tags: SQL,Trace replay

Testing posting with Live Writer and a proxy

I’m seeing if I can get through the firewall.

Using Live Writer

I’ve seen quite a few blogs about Windows Live Writer. Opinions seem to vary as to the usefulness of the tool. So I thought it was time I tried it out for myself.

This is the first post I’ve made with it. I’m just using basic word processing so it shouldn’t be too different than what I use now. That would be OneNote and Word, sometimes just entering right from blogger. But this looks to have most, if not all, the same capabilities. So we’ll see. 

Technorati Tags: Live Writer

Replaying traces with SQL Profiler

One of the projects I've become involved with at work has been the virtualization of our regional databases. However, when we hooked up a copy of one of the databases in a virtual environment we noticed a huge performance degradation, mainly in regard to CPU usage. The only part of the server that was virtualized was the drive containing the OS. User databases were mainly placed on a SAN so the IO looked to be acceptable, at least based on the small workload we generated. We know that the servers were not comparable; the current, physical server has 16 CPUs, where the virtual server is limited by VMWare to 4. Since performance was much worse than expected, we decided to try and replay a workload from one of our production servers on a virtual server and compare the results.
 
We thought of a few different ways to do this, but we eventually decided on using the replay ability of SQL Profiler. So we captured a trace from production, set up a copy of the databases on a second physical server configured identically to the production server, then used the different options inside profiler for the replay. What we saw was surprising.

The original trace was run for an hour. Using the multithreading option on the test box, the replay took twice as long, and the CPU usage was at least 10% higher for the length of the replay. When we set the number of threads to 64, the replay took 5 hours, and the CPU usage was maybe 25% of the original trace.

So the next step is to replay a trace on the same server to see what the results are. I'll post more on this in a few days, after a few more test cases. I'll also include some of the actual numbers.

How to change the display name of mail sent with DB Mail

Recently I had a manager complain that emails from some of our SQL 2005 servers were displaying different "From" aliases. These emails were being sent via DB Mail in jobs that were on all servers. I thought all the servers were configured the same; the same email account was being used on all, the same Exchange server, all the profiles had the server name as the display name.

 
 

However I missed one thing. Two servers were using Windows Authentication of the database engine and two were using Anonymous. The two using Windows Auth picked up the display name from Exchange, the 2 using Anonymous used the display name of the DB Mail profile.

 
 

A small thing, but useful in the future if I need to change the display name of email sent from a server.

Start with the basics when troubleshooting

Recently I began troubleshooting an issue on one of our servers. The issue is that the alerts I set up for jobs and on the server itself weren't sending emails to the operator I configured. This was happening for a while but I never had time to look at it in detail until now. And besides, I kept a close eye on the server so I would know if there were any problems. I had our other servers set up the same way, and none of them were having any issues. All the servers were using the same email account, and I set up all the database profiles to be identical. And I never had issues sending emails using send_dbmail.

So of course I checked and rechecked all the email account settings against a server that worked. I even went so far as deleting the email profile and creating a new one. Nothing I did had any effect. Finally I did what I should have done in the first place; check to see if the SQL Agent had email enabled for alerts. Sure enough if didn't.

The moral of my story is to start at the beginning when troubleshooting, no matter how basic it seems.

One of the first things you'll probably notice is the Surface Area Configuration Tool, introduced in SQL 2005, is not there. Actually it's still part of the server, only its moved. Now you access it by connecting to the server using the Management Studio, right clicking on the server name and choose "Facets" from the menu. When the View Facet screen opens, select "Surface Area Configuration" from the Facet combo box. Notice that by default all options are turned off; I specifically turned Database Mail on.

Moving a reporting database

Another task I just finished in regards to SSRS was moving the reporting database. We have an older server we were using for developing the report on. The system team gave us a virtual server (we're going to run virtual when we go live). I wanted to copy the existing catalogs to the new server so I wouldn't have to re-deploy them all. These are the steps I took. In my case the new report server was already installed and configured

1. Backup the encryption key on the source server using the Report Server Configuration Manager. This should be done regardless.
2. Backup the reporting database on the source server.
3. Restore the backup from the source to the destination server.
4. Apply the encryption key from the source onto the destination.

After I completed these steps all the reports and their data sources and credentials appeared on the new server's Report Manager. Hopefully I haven't missed anything.

Getting data into a page header in SSRS

As I've mentioned before, we're moving MS Access97 reports to SSRS 2008. Recently I came across another issue, repeating data in a page header. You may or may not know it, but you can't directly add a data field to a page/report headers and footers. If you want your data to repeat you need to employ a workaround. One way is to use parameters and use a second data set for the parameters. I tried this method, but I had a problem when the page changed; the header didn't always mirror what was in my group. I also tried group headers that repeated on each page, but the report I'm working with also has sub reports that may or may not span pages. I found when that happened the group headers didn't repeat properly.

The solution I came up with is to set the values in code. The first step was to declare a public variable for each field I needed on my page header. The second step was to create a function that set the value of the variables to a parameter passed into the function. I then added a hidden text box on the body of my report that called my functioned and passed in the values I needed on the page header. Finally I added the page header section, added a text box for each field I needed, and used an expression to call the public variable I declared in the first step.

This isn't my invention; there are examples of this on the web. I don't have a source handy but I'll cite a few that I used later.