I just brought up a new Windows 2008 server and installed SQL Server 2005 (I know… should have loaded 2008).

After my new SQL Server was up and running… I tried to connect remotely to the SQL Server.

Error…

Oh yeah… I remember… The good ole’ Surface Area Configuration tool.

Applied my change, and restarted SQL.

Remote connections still would not connect.

Hum… Ah… Must be Firewall…

In case you have not seen the Windows 2008 Firewall settings… it’s kinda intimidating

I found this DOS command to run on the server that corrected my connection issue. I hope it saves you some time if you encounter this in the future…

netsh firewall set portopening TCP 1433 "SQLServer"

That fixed me right up.

(I found the answer on ServerFault.com)

Have you ever had a time when you needed to move a SQL database to a new server… made your backup… restored to new server… Go to setup the SQL user with permissions and get this…

Create failed for User ‘myuser’. (Microsoft.SqlServer.Smo)
User, group, or role ‘myuser’ already exists in the current database. (Microsoft SQL Server, Error 15023)

Huh… User already exist in database???

This is because the user in the database is orphaned. This means that there is no SQL login id or password associated with the database user.

This is really easy to correct in SQL 2000 SP3 or greater.

First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

(HT: FileFormat.info)

What happens when a database team is moving databases and the development team puts up a maintenance page to the company while the move occurs…

That’s right… they put me under the bus!

Just a little Friday humor… BTW… This really happened yesterday.

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'

How does one UPDATE a field in a table where the key provided is in another table?

It is actually pretty simple:

UPDATE alias1
SET alias1.MyField = 'NEWDATA'
FROM tblTable1 alias1
INNER JOIN tblTable2 alias2
    ON alias1.ProductId = alias2.ProductId
WHERE alias2.PresentationId = '911'

I hope this makes sense. It is easy to test before running the update by simply replacing the UPDATE with a SELECT for testing to make sure the query is returning the correct data.

SELECT *
FROM tblTable1 alias1
INNER JOIN tblTable2 alias2
    ON alias1.ProductId = alias2.ProductId
WHERE alias2.PresentationId = '911'

If you know a better way to accomplish this, please leave me a comment on this post.

Tuesday, November 11th, 2008

Topic: Open Spaces – Bring your questions and ideas!

Tonight is our next meeting of the Pee Dee Area .NET User Group. 

Open Spaces is the chance for you to discuss topics with your peers.  Any developer topic is fair game and welcome!  This is your chance to gain valuable insight from your peers, so come equipped with your ideas or problems!  This meeting is a general open conversation and there will be no presenters.  See you there!

Here is the tentative schedule:
6:00 PM – 6:20 PM Socializing / Free Dinner
6:20 PM – 6:30 PM Introduction, Sponsor Time, and News.
6:30 PM – 8:00 PM Open Spaces

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.

Today we noticed our transaction log backups had started failing for our user databases.

From previous experience, I had a hunch that someone had created a new database that was in SIMPLE recovery model. Just an FYI… That will cause the log backup to fail.

Hum… This server has hundreds of databases… Now which is the culprit.

Here is a query you can use to find the recovery model of all you databases.

SELECT 'DbName' = name  , 'RecoveryModel' = DATABASEPROPERTYEX(name, 'Recovery') FROM master.dbo.sysdatabases ORDER BY name

Now… To find which one is SIMPLE?

SELECT 'DbName' = name  , 'RecoveryModel' = DATABASEPROPERTYEX(name, 'Recovery') FROM master.dbo.sysdatabases WHERE DATABASEPROPERTYEX(name, 'Recovery') = 'SIMPLE' ORDER BY name

Sure enough… There was a database in SIMPLE recovery model.

Hope that helps someone…

I have been reading this blog for some time (one of the best on SQL Server).  Ya need to check out yesterday’s post (a list of Microsoft Whitepapers on SQL 2008).

If you want to learn more about Microsoft SQL Server and SQL coding… I HIGHLY encourage you to check out the SQL Server Code,Tips and Tricks, Performance Tuning blog.

I love the quote from the site… (being a big Yoda fan & SQL Server fan & Geek)

I sense many useless updates in you… Useless updates lead to fragmentation… Fragmentation leads to downtime… Downtime leads to suffering… Fragmentation is the path to the darkside…

DBCC INDEXDEFRAG and DBCC DBREINDEX are the force… May the force be with you”

And how their email address is protected… genius…

“run the code below in Query Analyzer/Management Studio to get my email”

SELECT CONVERT(VARCHAR(31),0×73716C736572766572636F646540676D61696C2E636F6D)