Monday, May 9, 2011

What is SQL Server?

Have you ever wonder what exactly is SQL Server?  If so, check out this blog that explains SQL Server and it's services in great detail.

What is SQL Server?

Monday, April 18, 2011

Transaction Log Fragmentation???

Did you realize that transaction logs can be fragmented?  Well, not only can your transaction logs be fragmented but fragmentation can affect your transaction log’s performance and also delay database recovery.
There are two types of fragmentation that can affect your transaction log.  External and Internal. 

Check out Kimberly Trips’ article explaining both internal and external fragmentation and how to remove it.

Tip level: 300

Monday, January 24, 2011

Rollback code releases with “Database Snapshots”

There are some great 3rd party tools to help with version control and change control for your databases. If you are using SQL Server 2005 Standard Edition and have the budget for these tools, I would recommend buying them. If you are using SQL Server 2005/2008 Enterprise Edition, you may want to consider using database snapshots?

Database snapshot are used to create read only static copy of a database. In addition to being used to offload reporting to a mirrored database, database snapshots can be used to rollback a code release.

Here is how it works:

Before deploying the database release
  • Create your database snapshot via script or SSMS Snap-in (See ref: below)
  • Deploy the database release and test the application
  • If test is successful, drop database snapshot
  • If test is not successful, revert your database back from the database snapshot
RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT = <database_snapshot_name>
For version control, use a tool like SQLDBDiff or other schema and data compare tools to compare the database snapshot against the source database. Save the compare scripts for version control.
Other rollback options: using 3rd party tools (i.e. Quest Change Director), rollback scripts to undo DDL and DML changes (provided by your friendly dev group), or restoring the database from a backup.
Tip level: 300

Tuesday, November 9, 2010

Database Monitoring | Datavail

Database Monitoring Datavail

Ever wondered which is better, to buy monitoring software or create your own? Well, here is another perspective. Enjoy...

Tuesday, November 2, 2010

Managing multiple SQL Servers the easy way

Don't manage your SQL Server farm one server at a time. See what tools are available to help you manage multiple servers the easy way.

Managing multiple SQL Servers the easy way

Friday, September 24, 2010

SQL Server in the Clouds?:

Is SQL Server Ready for the Cloud? Check out this article by Kevin Kline, to see what progress Microsoft has made and where they need to go.

Kevin Kline is the Technical Strategy Manager for SQL Server Solutions at Quest Software.

SQL Server in the Clouds?:

Wednesday, September 8, 2010

To Autogrow or Not to Autogrow

A Love Hate Relationship     
Most DBA’s find themselves on either side of this issue.  They either use autogrow as a matter of practice or they choose not to use it at all. 
Autogrow, if used properly, can help prevent an unnecessary outage but should not be used to in lieu of properly sizing your data and log files.
Best Practices
  • For a managed production system, you must consider autogrow to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow.
  • You can use alerts or monitoring programs to monitor file sizes and grow files proactively. This helps you avoid fragmentation and permits you to shift these maintenance activities to non-peak hours.
  • AutoShrink and autogrow must be carefully evaluated by a trained Database Administrator (DBA); they must not be left unmanaged.
  • Your autogrow increment must be large enough to avoid the performance penalties listed in the previous section. The exact value to use in your configuration setting and the choice between a percentage growth and a specific MB size growth depends on many factors in your environment. A general rule of thumb to you can use for testing is to set your autogrow setting to about one-eight the size of the file.
  • Turn on the <MAXSIZE> setting for each file to prevent any one file from growing to a point where it uses up all available disk space.
  • Keep the size of your transactions as small as possible to prevent unplanned file growth.
Tip level: 200

Tuesday, September 7, 2010

Have you lost the SA password or are you locked out of SQL Server?

Are you a DBA? Have you lost the SQL Server SA password? Are you now locked out?

Well, here are some steps to get you back in quickly without bringing down the SQL Server.

  • You must be a member of the Administrators, Backup Operators, or Server Operators group on the local server to schedule a task
  • Task Scheduler must be running as SYSTEM (Default)
  • The NT AUTHORITY\SYSTEM account must be a member of the sysadmin server role (Default)
Schedule the following commands via the Windows Scheduler to regain sysadmin rights

1. Remote desktop or login locally to the SQL Server
2. Run the follow commands via command prompt (cmd.exe)

For servers configured for Mixed Mode Authentication (SQL Server and Windows Authentication)

at 22:50 sqlcmd -E -S ServerName\Instance -Q "CREATE LOGIN TempUnlock WITH PASSWORD = ‘<StrongPasswordHere>', CHECK_POLICY = OFF, DEFAULT_DATABASE=[master]"
at 22:50 sqlcmd -E -S ServerName\InstanceName -Q "EXEC master..sp_addsrvrolemember @loginame = N'TempUnlock', @rolename = N'sysadmin'"

For servers configured for Windows Authentication Only

At 22:50 sqlcmd -E -S ServerName\InstanceName -Q "CREATE LOGIN [Domain\GroupOrUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]"
At 22:50 sqlcmd -E -S ServerName\InstanceName -Q "EXEC master..sp_addsrvrolemember @loginame = N'Domain\GroupOrUser', @rolename = N'sysadmin'"

Tip Level: 300

Contributor: D. Jakubowski

Friday, September 3, 2010

Creating Solid Business Cases from Start to Finish | Knowledge Network |

Tired of having your business case being turned down because your business case is less than stellar?   Check out this article from ITBusinessEdge on how to write a solid business case from start to finish.

Creating Solid Business Cases from Start to Finish | Knowledge Network |

Tuesday, August 24, 2010

Leaving a SQL Server DBA Job Gracefully

Are you leaving your current job for a new opportunity? Follow this article to ensure a smooth transition.

Leaving a SQL Server DBA Job Gracefully