Very simple…

SQL 2000

USE master
GO

SELECT catalog_name
FROM information_schema.schemata
WHERE DATABASEPROPERTYEX(catalog_name,'RECOVERY') = 'SIMPLE' ;

SQL 2005-2008

USE master
GO

SELECT name
FROM sys.databases
WHERE recovery_model_desc = 'SIMPLE'

AWE? What is that?

If you are like me, you may have often wondered about the purpose of that check box in SQL Server. I know I did. Well, my friend Jayson Rowe and I read up on it and actually implemented AWE on one of our SQL Servers at work.

On a default 32 bit Windows Server setup, 2 GB of RAM is the most that a SQL instance can use. Let’s say a server has 10 GB of RAM that is running nothing but the OS and SQL. The operating system will allocate 2 GB to the OS and 2 GB for SQL. That means your server is wasting 6 GB of RAM.

There is a optional parameter (/PAE)that you can place in the boot.ini file to change that.

To edit the boot.ini file on a Windows 2003 server, open Control Panel, open System, then click Advanced, then settings for the Startup and Recovery, then Edit under System startup. Then you will see a file whose contents are similar to the following:

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="WindowsServer 2003, Standard" /fastdetect /NoExecute=OptOut

In order to enable the PAE switch, simply add /PAE to the end of the OS loader line, save, and reboot.

What is PAE? It is Physical Address Extension.

So… What is that? Here is a quote straight from this Technet article.

PAE changes the memory addressing from 32-bit addressing mode to 64-bit addressing mode, allowing the operating system and high performance drivers and applications access to the additional physical memory

So, we have discussed OS level setting of PAE.

After setting the PAE, a reboot of the server is required.

After the reboot, open SQL Server Management Studio, right click on the server instance and select properties. There is a check box to enable AWE after PAE is turned on (see picture in this post).

Restart the SQL Server instance.

But there is one more important piece of information to consider… We learned the hard way.

There is a setting in the registry called "Lock Pages in Memory." We first turned on PAE and AWE… and noticed the server doing some insane page swapping on the hard disk. We found after a little research that we needed to change the "Lock Pages in Memory" registry setting to not use the swap file but use physical memory. Here are the steps straight from the Technet article.

1. On the Start menu, click Run. In the Open box, type gpedit.msc.
   The Group Policy dialog box opens.
2. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
3. Expand Security Settings, and then expand Local Policies.
4. Select the User Rights Assignment folder.
   The policies will be displayed in the details pane.
5. In the pane, double-click Lock pages in memory.
6. In the Local Security Policy Setting dialog box, click Add.
7. In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.

WARNING: Quote from this AWESOME article (I encourage you to check it out… Jim knows more on this subject than I do… that’s for sure!)

It is always recommended to set a value for "Max Server Memory" to ensure that other processes running on the server have some memory available.  Leaving 1.5-2 GB is a good setting.  Additionally, when multiple instances of SQL Server are running on the one machine, "Max Server Memory" is necessary to prevent one instance from using too much memory and starving the other.

In recap… Here are the three steps needed to be able to use AWE on your SQL Server.

1. Add the /PAE switch to boot.ini to allow Windows 2000/2003 to access more than 4 GB of memory
2. Grant "Lock Pages in Memory" to the user account that runs the SQL Server process
3. Check the checkbox in the SQL Instance properties to allow AWE.

So… Back to our server.

Our server was only using 4 of the 8 GB of RAM on the server. After turning on PAE and AWE… SQL is now using 7 GB of RAM and the server is running extremely well.

I am not the expert, so if you can add anything to this post, please feel free to leave a comment.

Special thanks again to Jim McLeod and Jayson Rowe.

The other day, we noticed that when our CREATE DATABASE scripts were running… the databases were not created where we expected. Due to our setup, we want the databases and log files to use the SQL Server default locations.

We ran the following T-SQL to verify the default locations:

USE master GO DECLARE @SmoDefaultFile nvarchar(512) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT DECLARE @SmoDefaultLog nvarchar(512) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT SELECT ISNULL(@SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@SmoDefaultLog,N'') AS [DefaultLog]

Results…

DefaultFile	DefaultLog
——————————–	——————————–
D:\SQLDbs\Data	D:\SQLDbs\Logs
(1 row(s) affected)

Looks correct… why is it putting the database and log files in:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Just to be sure… we verified this setting on the Server Properties screen in SQL Management Studio…

Looks correct…

What we found through testing… In order for the default locations to work as expected… SQL Server must be restarted. I feel (not proven) that SQL Server loads these values from the registry at startup, and does not update when the change happens on Server Properties.

Last year, our local .NET User Group held a four session .NET University to cover the 3.0 Framework (WPF, WCF, WF, and CardSpace).

Now, thanks to Karl Rissland (special thanks to Doug for sharing the information), there are sessions for BizTalk.

You can get all the information over at the .NET University Site (Power Points, videos, labs, etc…).

If there is anyone that would like to present the material at our Pee Dee Area User Group, please let me know!!!

Russ Fustino is coming to Florence, SC this Wednesday! Russ is a MSDN Developer Community Champion for Microsoft. (Register for the event here) Russ is stopping by on his way to Raleigh from Tallahassee!

FYI: A copy of Office Ultimate 2007 will be given away!

Did you ever feel inundated with developer tools? Did you ever feel you are missing something that could make your life easier as a developer and as a power user? There are so many tools available to choose from and it keeps getting tougher to  keep up with. Tool familiarity continues to be one of the top developers challenges. This series of seminars is all about tools for developers, by developers.

Welcome to this new, fresh and original ground breaking MSDN seminar series from Russ’ Tool Shed called … It’s All About The Tools.  This is the first installment of many more to come. This session is tech talk about tools for developers. Join Microsoft Florida Developer Evangelist Russ Fustino for his take on what’s hot in the developer’s toolbox. There’s something here for everyone, from beginners to experts. Oh, by the way, you might want to put your hard hat on for this one!

In this session you will see demonstrations the following tools and a few more surprises…

Fiddler
Web Stress Tool
IE Developer Toolbar
Visual Studio IDE tools
   code convertors
   obfuscator
   snippit manager (and the snippet tool in vista as a bonus)
   toolbox scratch pad
   Refactor
   Visual Studio Express tool’s
   Visual Web Developer Express:
   Web Administrator
   Membership and personalization
Visual Express C#
   XNA Express
Live Services
   Spaces
   Live Writer
   MSDN article on search provider (2007.08)

Silverlight 1.0/1.1 XAML Designer (Expression Blend)
Community Tool Web Site Review 
Top downloaded SDK’s
GotDotNet: http://www.gotdotnet.com/team/ide/
MSDN Dev Center: http://msdn.microsoft.com/vstudio/downloads/powertoys/
CodePlex home: http://www.codeplex.com/Default.aspx
Download center and more!

To find out more and register for the event go here. This is going to be an awesome event!

Florence, Columbia, Charleston, Conway, Myrtle Beach, Fayetteville, Wilmington, Charlotte (anyone from anyplace)…

You are ALL WELCOME to attend this FREE event.

I have known about this for a while, but just recently stumbled upon it again.

Here is a cool site at http://www.databaseanswers.org to assist with data model designs.

They have hundreds of database designs specific for certain types of needs such as…

• Blogs
• Cemeteries
• CD Collection
• Games Store
• Help Desk
• MP3 Music Site
• Tracking Software Problems (naturally I would never need this one…)

163+ data models…

Thanks Chris Craft for first sharing the site with me!

Windows 2008 RC0 was released yesterday. I am really looking forward to using IIS7. I am anxious to setup a few web sites with SQL 2008 and see how it works and performs.

Get it Windows 2008 RC0 here
Get SQL 2008 CTP here

Cool Benefits of Windows 2008 (taken from here)

• Cross-site copy allows you to easily copy Web site settings across multiple Web servers without additional configuration.
• Built-in virtualization to virtualize multiple operating systems—Windows, Linux and others—on a single server. With virtualization built into the operating system and with simpler, more flexible licensing policies, it’s now easier than ever to take advantage of all the benefits and cost savings of virtualization.
• Protection of your server with security innovations that reduce attack surface area of the kernel, resulting in a more robust and secure server environment.
• Protection against malicious software with User Account Control with a new authentication architecture. (NOOOOOOOOOOO)
• Simplified management of your IT infrastructure by using new tools that provide a one-stop interface for server configuration and monitoring, as well as the ability to automate routine tasks.
• Effectively pinpointing and resolving trouble spots with powerful diagnostic tools that give you ongoing visibility into your server environment, both physical and virtual.

And the one I really want to see…

• Delegated administration of applications and sites so you can give control to different parts of the Web server to those who need it.

My understanding is that this will allow web hosting companies to give more control of IIS to clients hosting their web applications on your server. Pretty cool!!!

Thanks Somasegar for the announcement.

Jacob Sebastian wrote a very cool article on ”power of the merge”…

How many times have you needed to merge data between tables… or mobile devices to central servers…

“MERGE, a new keyword which performs INSERT, UPDATE and DELETE operations at one go”

His example:

Old way to merge tables:

    1 /*
    2 Pseudo code used for saving sales order information with SQL Server 2005
    3
    4 — save order header information
    5 If OrderNumber found in OrderHeader
    6    Update the information
    7 Else
    8    Insert the information
    9 end
   10
   11 — save order detail information
   12 Delete from Order detail table all items not in the order info
   13 Update Order detail for all items present in the order info
   14 Insert into order details all new items in the order info
   15 */

New Way using Merge

    1 /*
    2 Pseudo code for saving the same order with the MERGE statement of SQL Server 2008
    3
    4 — save order header information
    5 MERGE order info to Order Header table
    6
    7 — save order detail information
    8 MERGE order info to order detail table
    9 */

“No, I did not miss anything. You can write the code in just 2 lines.”

Check out entire article here.

Tuesday, August 14, 2007, is our next meeting of Pee Dee Area .NET User’s Group. 

Chris Reeder (that’s me), from ACS Technologies, will be presenting an Introduction to SQL Server Data Partitioning. Learn how to better handle billions of records in one table or how to make better use of fast and slow drive combinations. Will also explore how to speed up disaster recovery and other maintenance.

We will see real world benefits and situations for using this new feature to SQL Server 2005.

Here is the tentative schedule:

6:00 PM – 6:20 PM Socializing / Dinner
6:20 PM – 6:30 PM Introduction, Sponsor Time, and News
6:30 PM – 7:45 PM Presentation on SQL Data Partitioning
7:45 PM – 8:00 PM Drawing and Wrap Up

Please subscribe to the PDANUG Upcoming Events and News Feed here.

Tuesday, July 10, 2007, is our next meeting of Pee Dee Area .NET User’s Group. 

Chris Craft, from Taylor Data Systems, and Page Brooks, from ACS Technologies, will be presenting a follow up introduction to Silverlight, Microsoft’s new platform for delivering rich internet applications (RIAs).

We will further explore Silverlight and related technologies.

Here is the tentative schedule:

6:00 PM – 6:20 PM Socializing / Dinner
6:20 PM – 6:30 PM Introduction, Sponsor Time, and News
6:30 PM – 7:45 PM Presentation on Silverlight
7:45 PM – 8:00 PM Drawing and Wrap Up

Please subscribe to the PDANUG Upcoming Events and News Feed here.