SQL Server 2008

Data Transformation Services vs. SSIS: The key differences

I recently published an article on SearchSQLServer.com about DTS and SSIS where I talk about some of the differences.  Obvsiolly I didn’t cover all the differences between DTS and SSIS, just some of them.  So don’t go railing on me that I didn’t cover something.

Denny

SQL Server Clustering Guide & Webcast Links

A white paper which I wrote for Starwind Software is now available.  The link to register for the webcast (October 21, 2009) has also been put up.  The webcast and white paper go together, so I recommend that you grab the white paper now and I hope to see you at the webcast next month.

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 »

SQL Server 2008 Clustering Article in SQL Server Magazine

For those of you who haven’t check out this months SQL Server Magazine, the cover article was written by yours truly about Clustering SQL Server 2008 on Windows Server 2008.  This is an updated version of the article I did last December on Clustering SQL Server 2005 on Windows Server 2003.

I hope that you find it handy.  (Subscription is required.)

Denny

Join me on Sept 15, 2009 as we talk about Clustering SQL Server without a SAN

On September 15, 2009 I’ll be hosting a webcast entitled “How to Set Up a Cost-Effective Windows Server 2008 Cluster with SQL Server 2008 and Tune The Performance“.  During this webcast I’ll be showing how to use Starwind Software’s iSCSI Server software to replace to need for a SAN when creating your Windows 2008 and SQL Server 2008 server.

The presentation will start at 2PM EST, 11AM PST.  Pre-registration is required using the above link.

Do note that this presentation will be done a little bit backwards as we’ll dive into the technical content write away, then hit the slide show with the SQL Installation is running in the background.

See you there.

Denny

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

When clustering SQL 2008, SQL Server may reject the Key

When installing SQL Server 2008 Enterprise onto my new SQL Cluster I downloaded the media from Microsoft and dropped it onto a network share and installed it.  The first node went perfectly, the second node, not so much.  When I went to install on the second node I would get to the screen which asked for the Key (it is a volume license install so the key was already there) and when I clicked next it told me that the key was invalid and the installer exited. Continued »

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