Virtualizing your databases guide

Recently the guys at SQLServerCentral published a great article about SQL Server virtualization. But it is usefull for SQL Server, Oracle or any other DB technology that must run “on the cloud”.

So here is the link to it:

And that’s it. Go read it over there, but come back here after.


Posted in Cloud, MongoDB, MySQL, Oracle, SQLServer, VMware | Tagged , , | Leave a comment

Windows 2012 deduplication – turning it off (completely) and recovering your space!


ok, it is always good to know how to turn off things. So here it is: how to reclaim space on deduplicated drives.

Originally posted on HayesJupe's Blog:

A while back i wrote a post about data deduplication in 2012…. generally a very good feature, but as that specific post talked about, there was a collection of .iso and compressed data that not only did dedup not save me anything, it actually used up more space in the dedup folder than the orginal data size (which i found a little odd)

Today i got around to doing something about this and found

  • Disabling data deduplication (via GUI of powershell) only stops further deduplication from occuring – but data that has already been deduplicated will remain deduplicated
  • In order to”move” (re-hydrate ?) the data back to the original file and out of the deduplication store, use the powershell command “start-dedupjob -Volume <VolumeLetter> -Type Unoptimization”
  • You can check the status on where this is at by using get-dedupjob, or i like using TreeSize which shows the size on disk of…

View original 303 more words

Posted in Uncategorized | Leave a comment

SQL Server 2012 Management Studio download

And finally it’s time to update my post on SSMS 2008 download with the new version, for everyone joy. Well actually we are on SQL2014 now, but as any brand-new Microsoft product, it will take some time until most of us are using it. The cost of updating existing servers is not exactly interesting – the newer versions will be used on mainly on new projects for a while.

And this brings me to the current post. Where do I download the 2012 version of just Management Studio? Actually this has been answered already, and in great detail, in this nice article by Jason Strate:  (notice it’s from 2013).

The key here is to look for the Service Pack 1 product page of SQL 2012 Express. If you searching for the RTM version you will have less luck, as I pointed out on my first post about SSMS 2008r2 download. I strongly recommend the x64 version. But if you need the BIDS/SSIS pieces, you will need the full SQL installer and not these (as pointed out in my previous post).

Enough talk, go to the SQL 2012 SP1 Express page here:, enter your language and look for the files named “SQLManagementStudio_x64_ENU.exe” or “SQLManagementStudio_x86_ENU.exe“.

As with my previous post, below are the direct links too (for english language – actually the only version you should consider):

SQL 2012 SSMS 64bits with SP1 direct link:

SQL 2012 SSMS 32bits with SP1 direct link:

PS 1: while doing research for this post, I came across some great links about SQL lifecycle and support:

PS 2: stay away of SQL Express with Advanced Services, unless you really really want a database to run in your local workstation. This version is suited for developers who need a local DB – for remote server administration work you should only use the SSMS version without the local database engine.


Posted in SQLServer | Tagged | Leave a comment

SQL SERVER – Good Value for Page Life Expectancy – Notes from the Field #026


Todo List: PLE checks.

Originally posted on Journey to SQL Authority with Pinal Dave:

[Notes from Pinal]: In the past, I have been wrong many times, but I was man enough to accept my mistakes and correct myself. Page Life Expectancy is a very similar subject for me. In the past when I had written a blog post based on Microsoft’s white paper, I was corrected by SQL Experts immediately for my error in judgement and incorrect information. I accepted my mistakes and corrected it. I just shared this story with my good friend Tim Radney and he was very kind to give me guidance on this subject. I asked him if he can write further on this topic and help people understand this complex subject in simple words.

Linchpin People are database coaches and wellness experts for a data driven world. In this 26th episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains in a…

View original 328 more words

Posted in Uncategorized | Leave a comment

Red-Gate SQL Monitor: issues with Windows Registry

Earlier today I opened a topic on Red-Gate forums, about one issue I found while testing the soon-to-be-discontinued SQL Monitor Hosted service. That is a great service, and it was free while in beta, but unfortunately I did not have enough time to play with it (or write about it). Altough I really wish they extend the product a couple more months, I will post this message with the hope that it could help someone on the future – either with Hosted or On-Premise versions of SQL Monitor.

I was having some errors while trying to set up the monitoring on my Windows Server 2008R2 box, all related to the registry key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones”

It turns out the server did NOT have a valid TimeZone configured. It is easy to check this by cliking on the clock on the taskbar. Once the correct timezone was selected, this error stopped happening, resuming monitoring activities.

See the full post here:


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

Oracle: view Error Log and connection issues.

Usually I try not to mess with Oracle, and in return it tries not to mess with me. Relationships are difficult when both parties have pretty strong characters. But today Oracle was angry at me, and I needed to do something about it. So SSH here I go!!!

Well, actually a user was asking me if Oracle service was up or down. Basic checking on Enterprise Manager told me it was up. But I wanted to see the logs and look for unusual messages. Unfortunately this is not so easy to find, so here I am writing a small post to help whoever needs this information.

The error log location is customized for each install, so I can not point you to a single default location. It usually stays inside your ORA_HOME folder, somewhere around /diag/rdbms/<instance/something>/trace, but this could be anywhere on your filesystem. To find out where your Oracle log file is located, connect to your database by your prefered way, then query this:

show parameter background_dump_dest; 

The main log file is “alert_orcl1.log”, so you could just locate/search for it too. When you find it, you will have to go back to Operating System and check the file/tail it. You can read more about this here. Also, if you are already troubleshooting, you could take a look at this great article about How to troubleshoot Oracle remote database connection. It’s a rare sight to see such a clean and complete article about some Oracle errors.

Also while we are here, you could read this article too if your Oracle is refusing connections. The query below will show you the configuration and current usage of your available connections.

select * from v$resource_limit where resource_name = 'processes';

And if you are having connection issues, it could be usefull to see who is currently connected on your database. The sample below is from this forum post.

substr(a.spid,1,7) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,15) box,
substr(b.username,1,8) username,
substr(b.osuser,1,8) os_user,
substr(b.program,1,23) program
from v$session b, v$process a
where b.paddr = a.addr and type='USER' order by spid;

Have you noticed that I like to link whatever sources I find about the subjects posted here? I don’t intend to give just tips, but also link more source material to be used as research. Usually these articles have way more information than what I quote, so it is worth to take a look at them.


Posted in Linux, Oracle | Tagged , , , , | Leave a comment

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: .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://" 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