SQLServer

In my last post I showed a query to identify non-unique indexes that should be unique. You maybe have some other indexes that could be unique based on the data they contain, but are not. To find out, you just need to query each of those indexes and group by the whole key, filtering out those that have duplicate values. It may look like an overwhelming amount of work, but the good news is I have a script for that: [Read More]

Defining the appropriate primary key and unique constraints is fundamental for a good database design. One thing that I often see overlooked is that all the indexes with a key that includes completely another UNIQUE index’s key should in turn be created as UNIQUE. You could argue that such an index has probably been created by mistake, but it’s not always the case. If you want to check your database for indexes that can be safely made UNIQUE, you can use the following script: [Read More]

As you probably know, SQL Server Express doesn’t ship with SQL Server Agent. This is a known limitation and many people offered alternative solutions to schedule jobs, including windows scheduler, free and commercial third-party applications. My favourite SQL Server Agent replacement to date is Denny Cherry’s Standalone SQL Agent, for two reasons: It uses msdb tables to read job information. This means that jobs, schedules and the like can be scripted using the same script you would use in the other editions. [Read More]

Last week I was in the process of migrating a couple of SQL Server instances from 2008 R2 to 2012. In order to let the migration complete quickly, I set up log shipping from the old instance to the new instance. Obviously, the existing backup jobs had to be disabled, otherwise they would have broken the log chain. That got me thinking: was there a way to keep both “regular” transaction log backups (taken by the backup tool) and the transaction log backups taken by log shipping? [Read More]

The problem One of the tasks that I often have to complete is manipulate some data in Excel, starting from the query results in SSMS. Excel is a very convenient tool for one-off reports, quick data manipulation, simple charts. Unfortunately, SSMS doesn’t ship with a tool to export grid results to Excel quickly. Excel offers some ways to import data from SQL queries, but none of those offers the rich query tools available in SSMS. [Read More]

Sometimes you have to copy all user databases from a source server to a destination server. Copying from development to test could be one reason, but I’m sure there are others. Since the question came up on the forums at SQLServerCentral, I decided to modify a script I published some months ago to accomplish this task. Here is the code: ## ============================================= ## Author: Gianluca Sartori - @spaghettidba ## Create date: 2013-10-07 ## Description: Copy user databases to a destination ## server ## ============================================= cls sl "c:\" $ErrorActionPreference = "Stop" # Input your parameters here $source = "SourceServer\Instance" $sourceServerUNC = "SourceServer" $destination = "DestServer\Instance" # Shared folder on the destination server # For instance "\\DestServer\D$" $sharedFolder = "\\DestServer\sharedfolder" # Path to the shared folder on the destination server # For instance "D:" $remoteSharedFolder = "PathOfSharedFolderOnDestServer" $ts = Get-Date -Format yyyyMMdd # # Read default backup path of the source from the registry # $SQL_BackupDirectory = @" EXEC master. [Read More]

Oooooops! I exchanged some emails about my post with Jonathan Kehayias and looks like I was wrong on many of the points I made. I don’t want to keep misleading information around and I definitely need to fix my wrong assumptions. Unfortunately, I don’t have the time to correct it immediately and I’m afraid it will have to remain like this for a while. Sorry for the inconvenience, I promise I will try to fix it in the next few days. [Read More]

Some months ago I posted a method to overcome some quirks in the MDW database in a clustered environment. Today I tried to upgrade that clustered instance (in a test environment, fortunately) and I got some really annoying errors. Actually, I got what I deserved for messing with the system databases and I wouldn’t even dare posting my experience if it wasn’t cause by something I suggested on this blog. [Read More]

Yesterday I upgraded my laptop to Windows 8.1 and everything seemed to have gone smoothly. I really like the improvements in Windows 8.1 and I think they’re worth the hassle of an upgrade if you’re still on Windows 8. As I was saying, everything seemed to upgrade smoothly. Unfortunately, today I found out that SQL Server services were gone. My configuration manager looked like this: My laptop had an instance of SQL Server 2012 SP1 Developer Edition and the windows upgrade process had deleted all SQL Server services but SQL Server Browser. [Read More]

SQL Server logins can implement the same password policies found in Active Directory to make sure that strong passwords are being used. Unfortunately, especially for servers upgraded from previous versions, the password policies are often disabled and some logins have very weak passwords. In particular, some logins could have the password set as equal to the login name, which would by one of the first things I would try to hack a server. [Read More]