SQL Server 2000

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 »

Non-sysadmins create tables under own schema

When using SQL Server 2000 and the user doesn’t have sysadmin rights, and their login isn’t mapped to the dbo user within the database all objects created will be, by default created under the user schema.

This is the normal behavior of SQL Server 2000.  In order to allow users who are not members of the sysadmin fixed server role to create objects under the dbo schema by default you have to map their login to the dbo user, even if they are a member of the dbo fixed database role.

To work around this, in the T/SQL code specify the owner of the database object.  If your developer is using Enterprise Manager to create the new tables before saving the table, click on the properties button in the upper left hand corner of the Enterprise Manager window (second from the left).  Then change the owner drop down from their username to dbo.  There is no way to default this setting to dbo so it will need to be changed for each new table being created.

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

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 »

Get SQL Server log shipping functionality without Enterprise Edition

A new tip of mine has just been published on SearchSQLServer.com.  This tip, “Get SQL Server log shipping functionality without Enterprise Edition” is all about writing your own Log Shipping code without using Microsoft’s, allowing you to use Log Shipping on editions of SQL Server other than Enterprise Edition.

Denny

Getting more error data from SQL Server Replication

The error reporting in SQL Server Replication isn’t all that great.  This is a well known issue that pretty much everyone knows about.  Something that I don’t know if a whole lot of people know about, is that there is a way to get a lot more information from replication about what’s going on, and what’s going wrong.

This is most easily done by running the replication job manually from within a command window on the distributor.  This will allow you to add switches or change values as needed and easily see the output, or redirect the output to a file for analysis, sending to Microsoft, your consultant, etc.

 Replication is run by jobs, with job steps of some funky types.  These step types simply mask what is happening in the background.  SQL is shelling out and running a command line app and passing it all the switches as they are within the job step.

All the command line apps which replication uses are in the “C:\Program Files\Microsoft SQL Server\90\COM” folder by default (for SQL 2000 replace the 90 with 80, for SQL 2008 replace the 90 with 100).  In that folder you will find a few apps which are of interest.  When you run the snapshot job snapshot.exe is called.  When you run a distribution job DISTRIB.exe is called (I’ve got no idea why it’s uppercase).  When the log reader is running logread.exe is run.  When you are running merge replication replmerg.exe is run.

All of these can be run manually from a command prompt.  For starters open up the SQL Job and edit step two, the one which actually does the work.  Copy all the text in the command window and paste is after the filename in the command prompt window and press enter.  You will need to stop the SQL Agent job before you can actually run the command from the command prompt, as replication is designed so that you can only run the commands one at a time.

Now the whole point of this was to get more log info because the replication is failing.  This is done by added the “-OutputVerboseLevel” switch to the command.  This switch has between 3 and 5 logging levels depending on which command you are running.  0 (zero) is basically no logging, and as the number goes up more data will be shown.  The distrib.exe, replmerg.exe and snapshot.exe takes 0-2, while the logread.exe takes 0-4.

You should only do this when replication is failing and you can’t figure out why, and all SQL is telling you is some cryptic error message.

Hopefully you’ll find this information useful.

Denny

Back to Basics: The BACKUP DATABASE command

Now that you have objects created within your database, and data within the tables you need to secure your database in case of a server failure or accidental data deletion.  This would be done by taking backups of the database.  Backups are taken by using the BACKUP DATABASE command.

The frequency that full backups are taken at should be determined by how much data changes within the databases.  On busier systems full backups should be taken daily.  On systems which are less busy the full backup can be taken less often, every few days, once per week, etc.

The syntax of the BACKUP DATABASE command is fairly basic.  You specify the name of the database to backup, and the destination that you will be backing up to.  If your database name is MyDatabase and your going to backup to a file named MyDatabase.bak on the E drive your BACKUP DATABASE command would look something like this.

BACKUP DATABASE MyDatabase TO DISK='E:\MyDatabase.bak'

This command will backup all tables, views, procedures, functions, etc as well as all the data within the database.  Backing up the database in an online operation meaning that users can be connected to the database, continuing to make changes to the database while the backup database command is running.  The database is backed up in the same state that it was in when the command started.  All changes made during the backup are logged into the database and staged in memory.  Upon completion of the BACKUP DATABASE command these changes are destaged from memory to the physical file during a checkpoint operation.

If you have a full text index, and are using SQL Server 2000 the full text backup is not included as part of the backup which is created by the BACKUP DATABASE command.  After restoring the database you would need to recreate the full text indexes.  This issue is resolved in SQL Server 2005 when the full text indexes are added to the database backup.

If you wish to create two backups of the database so that your backups can survive a failure of the disk you backup the database to you can use the MIRROR TO clause of the BACKUP DATABASE command.  This clause creates two exact duplicate backups of the database.  This option is included in SQL Server 2005 and higher.

BACKUP DATABASE MyDatabase TO DISK='E:\MyDatabase.bak' MIRROR TO DISK='F:\MyDatabase.bak'

If you find that your backups are taking to long do to a large database size and/or slow disk speed on the disk you are backing up to you can strip the database backup across database backups.  This is done by simply specifying additional destinations.

BACKUP DATABASE MyDatabase TO DISK='E:\Mydatabase.bak', DISK='F:\MyDatabase.bak'

Now striping your database can improve your backup performance, but it puts your backups at greater risk for a disk failure as each part of the backup disk is spread across two drives.  If either backup file is lost or damaged the entire backup is useless.  Because of this you can combine the striping for speed and the mirroring for safety.  To do this the number of backup devices specified in the MIRROR clause must be the same as the number of backup devices specified in the initial destination clause.

BACKUP DATABAES MyDatabase TO DISK='E:\MyDatabase.bak', DISK='F:\MyDatabase.bak' MIRROR TO DISK='G:\MyDatabase.bak', DISK='H:\MyDatabase.bak'

You can read up more about detailed backup techniques in the eZine article I wrote a few months back.

Check back in the coming weeks for information about log backups and restoring your database and log backups.

Denny

Article: SQL Server memory configurations for procedure cache and buffer cache

I’ve just published another tip over on SearchSQLServer.com.  This one is titled SQL Server memory configurations for procedure cache and buffer cache.

To determine how much memory your SQL Server database uses for buffer cache and procedure cache, factor in the SQL version you’re using, whether it’s x86, x64 or Itanium and the amount of memory allocated to the SQL Server instance. I talk about how the system configures memory and how it allocates the amount of buffer and procedure cache that’s available.Denny