SQL: Database Mirroring over different domains

Soooooooo I’m back. I’ve never went away, actually. But time to write is always more rare.

Today I wanted to share a small post about Database Mirroring. It’s one of the simplest ways to have “High Availability” for your databases, but not exactly “Always On” - that’s a subject for a longer discussion.

The thing is: mirroring requires a lot less resources and lets you choose between automatic or manual failover. For smaller systems where humam intervention is required to do the fail-over, this is actually great. All you need is a second database server on the same network, and you are good-to-go.

Usually corporations will have one large single Domain (Active Directory) structure, but sometimes they don’t. In those cases, Mirroring comes to the rescue because it works with SQL Server Logins by using self-signed certificates to authenticate. By doing this you remove any requirement about the services, hostnames, access restrictions, etc – you just have two SQL servers talking directly to each other.

TechNet has a great article about this: http://technet.microsoft.com/en-us/library/ms191140.aspx .The summary is:

  1. have your firewall open the required ports (read more about endpoints here);
  2. make your database recovery model FULL and do a backup of it;
  3. restore it with NORECOVERY at the secondary server (Mirror);
  4. create the certificates at Principal and Mirror instances;
  5. create the logins using those certificates;
  6. set-up the partners and turn-on mirroring;

And that’s it. If you have a network with lots of restrictions, use the IP addresses of both servers to set-up the partners. If you don’t, you may receive weird messages when activating any of the partners, like this one:

Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://Your_Server.Domain.com:7022" can not be reached or does not exist.

Wich would point you to this article. To read more about mirroring, go to this link. If you want to know more about High-Availability in general, read this one.

And why would I recommend doing Mirroring, instead of Clusters or Always-On availability groups? Because Clustering/Always-On has a LOT of requirements, and is way more complicated to implement. Log-Shipping is ancient, so Mirroring is the chosen one: stretching Occam’s Razor concept a bit, the simplest solution must be used.

Here is a simple breakdown of High-Availability options for SQL Server and the best use-case for each (in my humble opinion, of course).

  1. Log shipping: use with SQL2000, also good if you want a copy of a database with a few minutes of delay compared to the principal. Manual fail-over only, different host/instance name, needs a shared network folder.
  2. Mirroring: use with SQL2005+, when you want almost-instant updates to secondary, or when servers are not on the same domain. Both manual and automatic fail-over supported, different host/instance name. Only uses SQL connections.
  3. Clustering: use with any SQL version, when servers have access to the same storage/SAN and you need to connect using always the same instance name. Server physical location is vulnerable. Needs Windows Clustering.
  4. Always-On: use with SQL2012+, it’s a mix of Clustering+Mirroring when you need the highest availability and protection from disasters at the main server location. Needs a lot of stuff.

The key aspect here is how the servers talk to each other. The only method that uses only SQL connections is mirroring, so it is my favourite. Note that none of these options have any interference with virtualization: we are talking about OS/SQL levels only. You can have some virtualization technologies with high-availability, but that’s not in our scope today.

And this article ended up a lot bigger than my intention. Hope it helps someone out there.



Posted in SQLServer, Windows | Tagged , , , | Leave a comment

Using VS 2012 and SSMS 2012 together (SSIS bug)

Sometimes when you work with data, you have to move a lot of data around. And one of the best tools to do it is SSIS (Sql Server Integration Services). It’s a flexible solution to move almost anything to almost anywhere. It uses a small piece of Visual Studio to create the “packages” (really just XML files) and even allows some level of .Net code inside.

But if you need to run the full Visual Studio installation alongside SSIS, you may run into some troubles. SSIS is installed together with SSMS, and due to some misterious circumstances you may end up with a non-working VS if you install or update something out of the exact order.

The latest info I got on this is that you will need SQL 2012 SP1 CU5 to fix it, if it begins to misbehave. Read on the tweets from @MsSQLGirl to check more details. She pointed this great blog post about it too.


Posted in SQLServer, Windows | Tagged , , , | Leave a comment

SQL Server Tools: Red-Gate SQL Scripts Manager

It’s been a little while that I was thinking how would be the best way to share knowledge over this blog. And this is the first post on a series more focused on how to make our jobs easier. I thought of calling this section “SQL Freebies”, but I’m not sure all future programs will be free, so I decided to tag it “SQL Tools”.

And tonight’s product is…. Red-Gate SQL Scripts Manager. This one is actually free. It’s a collection of nice scripts from great people out there, with a simple interface to run them against the SQL Server you want. Yes, it includes all the sources if you wanna to customize something. Below you have the download link:



I will give these a try. Actually I think they should even include some more scripts (just because I like stand-alone “packages”), like the one from Michelle Ufford already mentioned on this blog. BTW, this would be a gread time to go and check it there is any new version.


Posted in SQLServer | Tagged , , | Leave a comment

SQLServer: Unable to restore database – Msg 5118, Level 16, State 3

Nowadays storage is cheap (relatively) and the problems of shortage of space are less frequent. But sometimes they do happen. And some of these times, someone could think enabling NTFS Folder Compression is a good idea. Maybe it is, but just for one folder and not the whole drive. Especially drive C:.
Well, I have no idea why this ever happened. But Windows will allow you to do so on a folder with a live database (even though it shouldn’t) - at least it will do this if you compress the whole drive. And when you try to restore any database or create a new one on that drive, you will be greeted by this error:

Msg 5118, Level 16, State 3, Line 1
The file "C:\MSSQL\Your_File.ldf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

To fix it simply disable NTFS compression on the folder used by SQL. Or disable it for the whole drive. Well, never use it in the first place! cya!
Posted in SQLServer, Windows | Tagged , , | Leave a comment

What is the latest SQL Server Service Pack?

I don’t know. But it seems we won’t have as much SPs as once we did. I just read a great article at SSC (short for SqlServerCentral, a great source of research) with this same question. And I agree with them completely: the cumulative update servicing model is ill-suited for server administration.

By installing a lot of smaller updates we will cause more downtime simply to reboot the sql service (or the whole server), even if we disregard testing completely (do NOT patch without testing! this was just an example!).

Read the full article below. Share it, spread the word, because it seems this change will affect MS OS and other products guys/gals too. Maybe we can make a difference.



Posted in SQLServer, Windows | Tagged , , , , , | Leave a comment

Windows Server: error 1018 illegal operation

Sometimes the past will strike you hard. And all of a sudden you are back to 10 years ago, dealing with bugs on Windows Registry settings and issues.

This weekend I spent quite a few hours trying to fix an unwanted behaviour of Windows 8.1 on my desktop, all via murky and mysterious keys. In the end, it kept disconnecting my USB hard drives and I had to re-format the whole thing (believe me, after you tinker on Regedit and it still doesn’t works, the next step is a clean install).

And now a Windows Server 2008 R2 box is complaining about registry keys marked for deletion, and will not let me restart any service or even logon to it. Searching the web was fruitless, as all I could find are old posts and no conclusive answer. The only fix? Restart the machine. That’s a really nice thing to be done on production servers, don’t ya think? [/sarcasm].

Anyway, with some teamwork from the OS team we discovered that we had a pending boot on that box. For some unknow reason the Windows Installer service went crazy after some updates that were pushed to the server, and blocked any new process trying to run. The only option left was to boot the server. Below you have the error message and some links about it.

Error 1018: Illegal operation attempted on a Registry key which has been marked for deletion


List of Windows System Error Codes: http://www.hiteksoftware.com/knowledge/articles/049.htm


Posted in Windows | Tagged , , , , | Leave a comment

2013 in review

The WordPress.com stats helper monkeys (ed note: monkeys? lol!) prepared a 2013 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 41,000 times in 2013. If it were a concert at Sydney Opera House, it would take about 15 sold-out performances for that many people to see it.

Click here to see the complete report.

Posted in Uncategorized | Tagged , | Leave a comment

PowerShell: change TimeStamp of files on Windows Servers

So here we go: last post of 2013. First one about this great little tool: Powershell. It’s not directly related to databases, though. But this tool is reaching a mature state and it is so usefull for SQL Server administration that any tuned DBA can not give up on learning it.

Today I needed to compact an old .BAK file, but keep the original file creation/modify/access time. This little script came to help. It works for any file, but I’m not interested in other things. Only database files matter to me :)

So here it is, quoted from the above link. There is a lot of material about advanced scripts for PowerShell, but we it’s easier start from somewhere simple and small. This sequence of commands will create an “object” with the information from your file, and then you can edit it’s properties using PS functions just like you were programing something.

1- Open PowerShell and go to desired folder
2- run the commands:
PS> $a = Get-Item file.zip
PS> $a.LastWriteTime = “mm/dd/aaaa hh:mm:ss”
PS> $a.LastAccessTime = “mm/dd/aaaa hh:mm:ss”
PS> $a.CreationTime = “mm/dd/aaaa hh:mm:ss”
3 – close PS

And that’s it. Over the next year I’m planing more posts on Oracle and MongoDB. If the tides are good, I will post a series of scripts to create an auditing structure over dissimilar databases. Have a nice Xmas and cya next year!

Posted in Windows | Tagged , , , , | Leave a comment

SQL versions compatible with Windows Server 2012

Times of change are ahead of us. Lately the technology reached some kind of plateau, with performance and features staying relatively at the same level for several years. Go and check the hardware market: despite some big savings on power, the fastest CPU cores from a couple of years ago are still in the “fastest block”. The same goes for software: from 2006 until 2012 we did not have the kind of big changes that happened a few years before.

I know, there were changes and improvements, of course. They were just not big as the changes that happened before. The end result is that we have fewer compelling reasons to upgrade. If not much changed, why hurry? But we must not let this keep us preventing from using the latest and the greatest. So go ahead, do your research and deploy the latest versions, there are incremental benefits waiting for you. And I’m not even talking about the cloud, this is a separate topic.

TLDR: here are some considerations for using the latest OS and SQL versions. Push forward, and if you have any considerations to share please fell free to add your comments on this post.

  • SQL 2005 is not supported on Windows 2012
  • Windows 8 comes with .NET 4.0, and SQL 2012 requires .NET 3.5 – enable it first
  • SQL2008R2 has some issues with clustering on Windows 2012

For more details, please read the full article: http://support.microsoft.com/kb/2681562

SQL 2012 Express with SP1 download: http://www.microsoft.com/en-us/download/details.aspx?id=35579 (I’m still trying to find the stand-alone SSMS installer for 2012).

And for some benefits of using both the latest and greatest: http://blogs.technet.com/b/dataplatforminsider/archive/2012/12/06/the-perfect-combination-sql-server-2012-windows-server-2012-and-system-center-2012.aspx

and http://blogs.msdn.com/b/vsanil/archive/2012/11/12/what-is-the-benefit-of-installing-sql-2012-on-windows-2012.aspx

R2 wave: http://blogs.technet.com/b/microsoft_blog/archive/2013/08/14/ve-the-date-windows-server-2012-r2-windows-system-center-2012-r2-and-windows-intune-update-coming-oct-18.aspx


Posted in SQLServer, Windows | Tagged , , | Leave a comment

Windows: Restore a System Image to smaller disk


I’ve stumbled upon this error when fixing my GF’s computer. Nowadays almost all servers on work environment are virtualized, so you can change the hardware atributes freely. But at home the story is different: you only have a handfull of hardware, and that’s it! No chance to mess around with terabytes of free space.

To make things clear: the builtin System Image tool of Windows (Vista/7/8,server2008/R2) will not restore to a smaller disk. It will always require a destination disk of the same size or bigger. Well, when you need to restore a system image it means something is really wrong with your PC, right? And this is a pretty dumb requirement, as most of the time the first part of a computer to die is the Hard Disk.

What leads me to my final situation: a dead hard-disk and a useless system imagem. I installed everything a few months ago on a 500gb HDD. Yesterday it started giving me bad sectors, so I picked up an older 320gb HDD I had unused, changed it, and tried to restore. Only to find this ridiculous requirement. Now I’m forced to install it all over again. And I won’t trust MS builtin tools for backup anymore. And I was thinking that Windows finally was “good enough” with the 8th version….

More links on the subject: windows 8 the system image restore failed disk too small. Some people listed work-arounds for Windows 2008 server. Not worth to a desktop.




cya! And take your backups with something else!

Posted in Windows | Tagged , , , , , , , | 2 Comments