SQL Server 2005

SQL 2005 SP3 Failing to install with password length error

When installing SQL Server 2005 SP3 you may get an error about the password being to short, and it may have nothing to do with your sa password.  It’s because the Install Scripts are trying to create a certificate with a password which doesn’t meet your domain’s password requirements.

If you get this error navigate to the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Upgrade folder.  Find the script for upgrading the SQL Agent.  Open the script and find the CREATE CERTIFICATE statement.  Make the password longer.  In my case I added 123456@ to the end of the password.  The password is in two different places in the script.

Then open Add/Remove Programs and select SQL Server 2005 and select Change.  Then run though the wizard to complete the installation of the service pack.

Denny

Whitepaper on why database maintenance is important

Today I’m publishing a white paper which I wrote for our Customer Service department to give to our customers. It explains why database maintenance is so important in keeping your database happy and healthy. Continued »

Changing the default owner when creating objects

When a user that doesn’t have sysadmin rights creates objects by default they will be created in the schema that is the users default schema.  Now the catch to this is that if you grant the user rights into the database via a domain group that domain group then the user doesn’t have a default schema.

So, now how do you fix this?  Unfortunately the only fix to this is to grant the users Windows login as a separate login, then grant this login rights into the database.  You can then grant the user which is mapped directly to the users Windows login a default schema of dbo.

Because of this the user should specify the schema when creating objects.

The downside to this is that they won’t be able to use the object editor to create new tables.  All new tables will need to be created in T/SQL directly.

Denny

Setting up SQL Server Service Broker for secure communication

Today a new article that I wrote was posted up on SearchSQLServer.com.

The article is about setting up SQL Service Broker to use Certificates to secure the traffic between the instances.

Denny

Who’s been logging into my SQL Server?

Knowing who has been logging into your SQL Server is one of the key things to know about your SQL Server.  It lets you know quite a bit about who’s using your server, and about who’s been trying to break into your SQL Server, and most importantly if they have succeeded.

Obviously you can setup a SQL Profiler trace to capture this information but that requires the overhead of running SQL Profiler, and who wants that.

All version of SQL Server (from 2000 and up at least) provide some level of logging about who has tried to log into the SQL Server.  Within Enterprise Manager or SQL Server Management Studio’s Object Explorer right click on the Server and select properties (if using Enterprise Manger select properties not connection properties).

Select the Security Tab and find the Login Auditing section.  By default SQL Server only logs the failed logons which is good as it tells you who hasn’t been able to log into the server.  However it doesn’t tell you it they have been successful which is why you may want to change this to both failed and successful logins.

Now changing this setting has an upside and a downside.  The upside is that you know who has been successfully broken into your database using a brute force attack and when.  The downside is that every client that successfully connects to the SQL Server will also log an entry, making it very hard to find the correct entry you are looking for.

Where do these entries get logged to you ask?  That’s the other downside.  They get logged to the SQL Server ERRORLOG file and the Windows Security log file.  Which means that these files will fill up fast.  And if you have a large enough client base logging into the database VERY FAST.

In a perfect world, I’d set this screen to both failed and successful logins.  In reality failed is probably all I can do.

SQL Server 2000 didn’t provide a whole lot of information about what is happening as it only says that Login n has tried to connect and failed.  Not exactly helpful as you don’t know who was trying to login to the SQL Server using the sa account over and over again.  SQL Server 2005 and up include a little piece of helpful information, the IP Address of the person who tried to connect to the SQL Server.  This will help tell you who is connecting to the SQL Server so that you can smack them around.

Denny

How do I change from push to pull subscriptions in SQL Replication?

The official answer is to delete the subscriber and recreate it pushing a new snapshot to the subscriber.

The much quicker and easier method is as follows.

1. Stop the distribution agent on the machine that it’s currently running on.

2. Disable the SQL Agent job that runs the distribution agent.

3. Script out the SQL Agent job from the old server and create it on the new server.

4. Enable the job on the new server.

Done.  You have just changes replication from being a push to a pull (or from being a pull to a push).

If you wanted to you could even setup your distribution agent on a third computer, but it is easier to keep track of everything if it’s running on the distributor or the subscriber.

Happy replicating.

Denny

Pull vs. Push Subscriptions? Which one should you use?

This is a “it depends” sort of question.

These are my recommendations, your mileage may vary.

Your distributor is on the same system as your publisher - Pull is probably for you

Your Subscribers are a very high transaction count - Push is probably for you

You need to manually copy the subscription over the network to the subscriber and load it up from the local drive - Pull is probably for you

Your distributor is on a separate from the publisher - Push is probably for you

The distributor is on the same server as your subscriber - Either, as the agent will be running on the distributor either way

You have a slow network link - Either, slow networks aren’t overcome with either technique

If you have specific’s you’d like to ask about, post your questions below, or in the ITKE forum.

Denny

How can I remove duplicate records in my tables?

All to often we end up with duplicate rows in a table.  The best way to keep duplicate rows out of the database is to not let them in.  But assume that they are there.  This bit of sample code shows how to delete those duplicate rows quickly and easily in a single statement.  No temp tables required (I use a temp table to put the data into for example purposes).  This code is for SQL 2005 and up as it uses some features which were introduced in SQL Server 2005.  SQL Server 2000 would require a totally different technique.

CREATE TABLE #DuplicateRows /*Create a new table*/
(Col1 INT,
Col2 INT,
Col3 INT)

INSERT INTO #DuplicateRows /*Load up duplicate rows*/
SELECT 1,1,1
UNION ALL
SELECT 1,1,1
UNION ALL
SELECT 1,1,1
UNION ALL
SELECT 2,2,2
UNION ALL
SELECT 2,2,2
UNION ALL
SELECT 2,2,2

SELECT *
FROM #DuplicateRows; /*Check that the data is actually hosed*/

WITH Cleaning AS (SELECT ROW_NUMBER() OVER(ORDER BY Col1, Col2, Col3) as row,
Col1,
Col2,
Col3
FROM #DuplicateRows)

DELETE FROM Cleaning /*Delete the rows which are duplicates*/
WHERE Row NOT IN (SELECT row FROM  (SELECT Col1, Col2, Col3, MIN(row) row
FROM Cleaning a
GROUP BY Col1, Col2, Col3) b)

SELECT * /*Check the table to see that it is clean*/
FROM #DuplicateRows

DROP TABLE #DuplicateRows /*Clean up the table*/

Hopefully you find this code useful.

Denny

SQL Server Standard Edition getting Lock Pages in Memory

Microsoft SQL Server 2005 and 2008 Standard edition will be getting the Lock Pages in Memory feature that SQL Server Enterprise Edition has had starting in SQL Server 2005.  This was announced by Bob Ward via the CSS Blog in his post “SQL Server, Locked Pages, and Standard SKU…“.  Per Bob’s post a CU will be released for SQL 2005 SP3 and SQL 2008 SP1 which will allow for a trace flag to be used to turn this feature on.

On behalf of the users I’d like to thank Bob and the rest of the SQL Server team for being able to get this into the product.

On behalf of the developer team, I emplore you to not turn this on for no reason.  Only use this feature once you understand what this feature does and in the correct places.

The Locked Pages flag bascially tells the SQL Server that if it is told to flush RAM to disk to ignore it.  If the setting is enabled then SQL doesn’t flush to disk.  If you find that your SQL Server is flushing to disk, don’t just enable the flag and move on.  I emplore you to contact CSS and figure out why SQL is being told to flush to disk.  This is the only way the bug will be fixed.  Once the issue has been reported to Microsoft and they have the information they need enable the flag until the bug is fixed.  Then install the patch to fix the bug, disable the flag and you’ll be fine.

Because of the fact that this is how bugs are found and fixed I hope that this is a CSS only CU which will require that users contact CSS before they can get the fix.

Denny

Back To Basics: Reading an Execution Plan

All to often when helping people look at query performance problems I’ll ask them to look at the execution plan, and see what it says.  Most query performance problems can be resolved simply by looking at the execution plan and seeing where you need to add an index.

All to often I (and others) then get asked, how to I look at the execution plan, and what does it mean?

Continued »