That stored proc generates server stats, and depending on the stat results, it will fire off the alerts in SQL Server. If that instance of that stored proc hangs or has abnormally high CPU, just kill it. The agent will fire it again when needed.

Also, by default, SQL Server comes with a set of alerts that the server can use (called, “Demo”). I would keep the alerts active if possible and see what alerts are being triggered and why – check logs.

There was also a bug on SQL Server 2008 that was fixed that stemmed from this proc causing high CPU, even if all Alerts are disabled. I’m not sure if this fix applied to SQL Server 2000 with latest SP.

More details: http://connect.microsoft.com/SQLServer/feedback/details/536354/execute-msdb-dbo-sp-sqlagent-get-perf-counters-hogs-cpu-although-no-alerts-enabled#details

I sometimes see people miss this. A very resourceful feature is the Template Explorer in SQL Server Management Studio. Here’s how to take advantage of it:

1. First you show the panel in SQL Server:

2. Double click on the snippet/template. We’ll double click on the Create Multi-statement table-value Function:

3. After you do so, it’ll open up a new query window with the source of the snippet.

4. Do CTRL+SHIFT+M to fill in the parameters:

Here’s a checklist of items to consider before migrating to a NoSQL db from SQL Server. If you come from SQL Server land, you may be used to these features and facilities. So before shifting over to a different world, consider if the db you’re jumping into (MongoDB, CouchDB, etc.) meets your needs by analyzing these items. Keep in mind that some features may not exist in NoSQL, not because of immaturity, but because it may not apply to the nature of a NoSQL database.

Handle Load

Compare against SQL Server:

Reading

Writing

Resource Monitoring

CPU Usage

Memory Use

Disk use

Transactions

Are there software design patterns to rollback

Locking

Reading

Programming

Syntax

SELECT / UPDATE / DELETE / INSERT / JOINS

Reusable Code Modules

Compiled Stored procs

Functions

Custom Data Types

Dynamic Management Views

Indexing

Clustered

Non-Clustered

Toolset / Resources

IDE

Administration

Profiler

Excution Planner

Community

Books

Administration

Complexity

Backing up

Full

Differentials

Transaction Log

Recovery

Replication / Clustering

Snapshots

Security

C# Integration

Facilities

Full Text Search

Snapshots

Kooboo is a CMS based on ASP.NET MVC. Recently, I got a chance to take it for a spin, and here are some of my thoughts. Keep in mind that the drawbacks here may come just from my ignorance of the tool use. I’ll update this as the more I learn about the inner workings.

Benefits

• Admin Panel’s UI is intuitive for designers/programmers
  • Easy to add pages
  • Easy to add your own themes/styles
  • Easy to create your own type of content
  • Easy to add content
• Lots of Features, more than Orchard
• Mature, has been around for a while (2008)
• Views are coded in Razor
• Can connect to MongoDB and other datasource types
• Versioning of any piece of content and view differences
• Manage website resources easily – images/documents/etc.

Drawbacks

May not be a drawback once I figure out the “how” and get better understanding.

• Once a site is created, when I migrated from XML to MongoDB, I lost all the website data from the XML files.
• Admin Panel’s UI may not be intuitive to non-designers/programmers.
• Site directory structure Kooboo generates is not the same as the traditional ASP.NET MVC.

Let’s take a look. For a site I created using Kooboo, named “batman”:

• When a content type is created, it does not create a C# class file. (I didn’t see one at least, in the directory structure.) It does, however, create a MongoDB collection for the content, there’s just no C# class mapped to it.
• There’s no clear way to bind a View to a model class as in traditional ASP.NET MVC since, Kooboo doesn’t create a C# class file. It doesn’t follow the traditional file/folder naming convention: for each View, you map that to a model.
• Community not as large as other CMS communities (Orchard, Umbraco, DNN).

I’ll keep exploring, but this is what I’ve found so far.

Sometime ago, I published some tips on upgrading to 2008. Here are some additional notes to keep in mind.

For upgrading, my plan of attack in the past has been as follows:

1. Run the Upgrade Advisor on all the databases (this doesn’t require much effort, just download, install, point to the db and run)

2. On production, do a custom install and only install what you need. You can do a side-by-side install and have SQL Server 2008 installed while SQL Server 2000 exists on the same server.

3. Analyze the Advisor’s reports and refactor where SQL objects that need to be refactored

4. Refactor TSQL as needed

5. Now, TSQL isn’t just in the DBs, it may also exists in the app code and other web files (.aspx, etc.). This is where it gets more challenging. Check to make sure if the analyzer can open .trc files. If so, run SQL Server profiler while the site is being used and those queries are being run. Then open that .trc file in the analyzer so it can check the validity.

If this doesn’t work, log the profiler results to a table. Then put the TSQL from output of the profiler in a sproc. Point the analyzer to that sproc to check the syntax.

You can automate this task by using Selenium or write a script using WGET to fetch and look for 500 HTTP error codes.

6. Refactor TSQL as needed.

7. Test the websites with 2008. Ideally you want to start testing with the latest compatibility. Having a lesser level of compatibility causes more overhead (since it has to support older features) and can allow the use of deprecated features that MS will abandon in the next release.

9. Optimize the settings for db server. Strip out all dbs you don’t need to avoid security problems, like: Northwind, AdventureWorks.

10. Before pushing it live, make sure you backup all dbs, including all system dbs.

11. As soon as the switch is flipped, keep an eye out on perform/SSMS monitor. Have handy the appropriate DMVs to monitor.

12. Even if the application(s) did not throw errors while testing on dev, there’s a chance that once you go to production, it starts throwing errors due to compatibility (maybe the more obscure pages were not tested). Be ready to flip the compatibility mode to 2000 – this takes place instantly.

Sometimes while testing on your local db, one has the habit (I admit to have this in the past) of renaming a table before replacing it. One does this as a quick backup. The problem with this strategy is that it doesn’t rename all of the dependencies. For example, if you had a table formsClients and renamed it formsClients_old, and then restored the formsClients table again (let’s say from an SSIS import), you’ll get something like this:

If we inspect the dependencies, we’ll see that in this case, it’s a foreign key constraint:

If you’ve ever tried to delete two rows that are identical from SSMS, you may come across this little beauty:

To get around this, you have to delete 1 row at a time and use SET ROWCOUNT. So you would do something like

SET ROWCOUNT 1
GO
DELETE FROM users_to_optin WHERE email = 'someone@yahoo.com'

That would delete one record at a time, even if there were multiple instances of ’someone@yahoo.com’.

After you’re done, make sure you set the rowcount back to 0, which turns the limitation off.

SET ROWCOUNT 0
GO

Check out more about ROWCOUNT.

If you’re still stuck working with a SQL Server 2000 database, there’s no reason why you shouldn’t be using SSMS 2008 R2. There’s lots of great add-ons for it and provides a rich set of features, aside from merging Query Analyzer and Enteprise Manager.

One thing to watch out for, is that if you script out your database objects, while you’re connected to SQL Server 2000, by default, it will script out TSQL that is compatible only with SQL Server 2008. To switch this, there are two ways:

Tools -> Options -> SQL Server Object Explorer -> Scripting

Database -> Tasks -> Generate Scripts

I’ve always found the SSIS import tool a bit clunky. I tried to import a CSV file and it get crapping out before completing.

- Copying to [dbo].[export] (Stopped)

Messages

• ·Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column “Email Address” returned status value 4 and status text “Text was truncated or one or more characters had no match in the target code page.”.
  (SQL Server Import and Export Wizard)
• ·Error 0xc020902a: Data Flow Task 1: The “output column “Email Address” (10)” failed because truncation occurred, and the truncation row disposition on “output column “Email Address” (10)” specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
  (SQL Server Import and Export Wizard)

I sifted through the data, only a few hundred rows, and I could not see anything wrong with it. I made the columns the right type and increased the column size more than the largest number of characters in the fields. I searched Google and Microsoft Forums trying a handful of solutions and nothing worked.

So what did I do? I had enough and just opened the file from CSV, into Excel, resaved it as an Excel 2007. Re-ran the SSIS import wizard to open an Excel file instead of a CSV, and voila. I’m still baffled though, as to what exactly the problem is.

Here’s another way to created a linked server using another provider, Microsoft Jet 4.0 OLE DB Provider. This is for SQL Server 2008. Check out my previous Linked Server tutorial if needed for SQL Server 2005.

For this setup, let’s use the login’s current security context. Make sure that you whatever SSMS you use to connect to the server uses the same user credentials as user that created the linked server.

So if I create that on a server, then I’ll have query it using the same credentials just used. If you want to know how to query it, check out my Previous Tutorial.