Posted by Szymon Wójcik on March 22, 2012
In my last post containing a number of comments on database mirroring I left some points for follow-up.
First of all, when it comes to edition requirements it stays the same for mirroring in SQL Server 2012 as it was in previous versions. However, you have remember that for high performance (asynchronous) mirroring you have to use Enterprise Edition. Confirmed by official license documentation.
Second, you have to remember that if you have mirroring set up between different database engine versions the principal has to have earlier version than the mirror. The reason for this is backward incompatibility of database versions (don’t confuse it with compatibility model) which does not allow to use a database from newer server with an older one. If you establish such mirroring, you will not be able to do failover for the same reason – after failover in this scenario a principal is of newer database version and incompatible with a mirror. The mirroring will enter paused state and will not be resumable until mirror’s version matches the principal.
I noticed that in case of SQL Server 2012 CTP0 (build 11.0.1750) and RTM (11.0.2100) – the respective database version numbers are 705 and 706, soI could mirror from CTP0 to RTM, but then in order to have a failover possibility an upgrade was required for CTP0. I had in mind building a more comprehensive list of database versions, since all information I could come up with can be summarized in a following table:
|SQL Server version
Internal database version
|SQL Server 2012 RTM
|SQL Server 2012 CTP0
|SQL Server 2008 R2
|SQL Server 2008
|SQL Server 2005 SP2 + vardecimal enabled
|SQL Server 2005
|SQL Server 2000
|SQL Server 7.0
Third point was – I was wondering about FileTable in context of FILESTREAM incompatibility with mirroring. You can’t have both – it’s either mirroring or FILESTREAM. And after short research I found out that a FileTable is actually an extension to FILESTREAM which is designed to work with AlwaysOn. But basic assumptions remain the same and FILESTREAM is exclusive with mirroring.
Posted in SQL Server | Tagged: database mirroring, edition requirements, FILESTREAM, high availability, internal database versions, SQL Server 2012 | 1 Comment »
Posted by Szymon Wójcik on March 20, 2012
I liked database mirroring since it appeared in SQL Server 2005. In fact, it’s the only HA technique I feel comfortable with (apart from it’s obvious limitations). It’s neither as blunt as log shipping, as random as replication nor as complex as clustering. It either works or it doesn’t – and you know it straight away after you set it up – you don’t have to wait for snapshot to be generated, sent over the network and applied on a subscriber or for a backup to be copied and restored on your secondary.
The incentive for this post was one by Glenn Berry, which held one answer to my mirroring-related questions. The database mirroring is considered deprecated in SQL Server 2012; it’s going to be available in 2012 and the next version, but then it will be removed from SQL Server and replaced by AlwaysOn Availability Groups. Given the releases of new major versions of SQL Server my totally wild guess it would be like… 2018? Later? All in all, it’s not the major point of concern right now, but something worth remembering and planning when time comes.
What gives me second thoughts is the second part of deprecation note saying “If your edition of SQL Server does not support AlwaysOn Availability Groups, use log shipping”. What I understand is – combining it with new SQL Server licensing model – you can get replication and log shipping in Standard Edition, clustering and AlwaysOn in Enterprise Edition, but what about mirroring? I would like it to remain in Standard Edition, but to be 100% sure I asked on MSDN forums and I’m waiting for an answer. On the other hand, if it were to go to Enterprise Edition, I would expect dramatic costs increase due to per-physical-core licensing model, which would not be very welcome by most organizations.
When it comes to facts about database mirroring apart from standard source of knowledge (Books On-Line) I also recommend the following:
- The forementioned post and it’s sequel by Glenn Berry – a detailed introduction and complete checklist of points to be considered while implementing database mirroring.
- Pro SQL Server 2008 Mirroring – a book by Robert Davis and Ken Simmons, which apparently is not only about mirroring, but also about other high availability techniques as well.
- Blog posts by Robert Davis tagged with database mirroring – lots of additional information.
- An MCM video followed by a demo presented by Paul Randal – to give an insight how the mirroring works internally.
- Microsoft KB article 2001270 on things to consider while implementing mirroring, mentioning exact Performance Objects to watch out for in case of performance issues.
Now few words why I like mirroring in SQL Server:
- It works.
- It allows you to do automatic client redirection in case of failure (provided you have a high safety scenario with a witness) – see an example of implementation, works with SQL Native Client, ADO.NET and SQL Server JDBC driver.
- You can run it on Standard Edition and you don’t need extra hardware for that (unlike clustering).
- In high safety mode, you can have zero data loss (unless your mirroring is broken).
- You can have it working even if you are not in a domain, or you don’t have trust relationship between domains in which mirroring partners are – I have recently presented an example during local SQL Server user group meeting based on Books On-Line example available here. It’s completely opposite to simple scenario in one domain when it comes to implementation, but I recommend trying that one out.
For the other side of the coin – things to remember when implementing database mirroring:
- There was only one Redeemer! you can have only one mirror server – this limitation is overcome in 2012′s AlwaysOn, where you can have 4 replicas, but it has it’s costs.
- Mirror is not easily accessible but you can use snapshots to query it.
- Server-level objects are not transferred with mirroring – you have to take care of logins, Database Mail profiles, SQL Server Agent jobs or Maintenance plans yourself. See Books On-Line article on jobs and logins.
- Mirroring requires full recovery model so you have to be absolutely sure about the transaction log of mirrored databases. Index maintenance, for example, can kill mirroring performance if you have 100GB of log records related to index reorganize sent to the mirror (and it will kill you twice – first the network, then the mirror server).
- In SQL Server 2008 and 2008 R2 FILESTREAM and mirroring don’t work together. You can’t have both in one database. To be checked – SQL Server 2012′s FileTables and mirroring.
- The failover is not instant – see Paul Randal’s post and Books On-Line. In short – it depends on the length of your REDO queue, so if you’re not monitoring it, then please do, just for the sake of not being surprised in the event of failover.
- The failures not detected automatically – there are four cases here (again, I’m quoting Paul Randal):
- SQL Server unavailability – detected in a second
- Machine unavailability – default is 10 seconds, you may increase it using ALTER DATABASE SET PARTNER TIMEOUT
- Unavailable log drive – it takes 20 seconds for a warning, and next 20 seconds (total 40 seconds) for database to be taken offline
- Page corruption – might not even be detected
- It’s not the witness who performs the failover, but the mirror! The mirror and the witness agree that the principal cannot be contacted and the mirror switches to a principal. If you don’t believe, set three Profiler sessions on all three mirroring participants and watch out for ALTER DATABASE statements when failover occurs.
Wrapping it all up, database mirroring is a really useful high availability technique (especially when combined with other methods – be it clustering or replication). It is easy to implement, does not have extra hardware requirements. Though it’s marked as deprecated in SQL Server 2012, it is going to be available and useful for at least couple of years, but it has it’s downsides which should be carefully considered when planning.
Posted in SQL Server | Tagged: database mirroring, high availability, SQL Server 2005, SQL Server 2008, SQL Server 2012 | 3 Comments »
Posted by Szymon Wójcik on March 13, 2012
As SQL Server 2012 has reached RTM status on 07.03.2012, with scheduled General Availability starting from 01.04.2012 more and more training resources are available.
First of all, Microsoft prepared a big online event at SQL Server 2012 Virtual Launch. You can find a lot of information – mostly overviews and presentations – which give you a high-level insight into new features of SQL Server 2012.
If you want dive deeper into theory and get more details, try online courses at Microsoft Virtual Academy. 5 courses have been prepared covering different aspects of SQL Server:
- Breakthrough Insights using Microsoft SQL Server 2012 – Analysis Services
- Breakthrough Insights using Microsoft SQL Server 2012 – Scalable data warehouse
- Breakthrough Insights using Microsoft SQL Server 2012 – Reporting Services
- Cloud on your terms with Microsoft SQL server 2012: Scale on demand
- Mission Critical Confidence using Microsoft SQL Server 2012
If you’re looking for some hands-on experience, e.g. try a single feature, you should visit SQL Server Virtual Labs. Apart from predefined scenarios you may use them for testing – I used them in my object whitespace-naming madness to create a number of databases with non-standard names.
And – last, but not least – there is always SQL Server 2012 RTM Evaluation available for download , which you may install in your environment and test it in all the ways you want.
Posted in SQL Server | Tagged: SQL Server 2012, training | Leave a Comment »
Posted by Szymon Wójcik on March 10, 2012
I made my first attempt to install SQL Server 2012 RTM on Windows Server 8 Beta on 07.03.2012 and as you may remember from that post, it failed on .NET Framework 3.5. I tried to install it again after having enabled .NET Framework 3.5 in Server Manager and this time it worked like charm. My conclusion is – check the prerequisites before and install all the missing ones in advance, don’t just rely on setup program to do it all for you. So this leaves me with two more tests to do:
1. Install SQL Server 2012 RTM on a fresh Windows Server 8 machine – to replicate the problem.
2. Install SQL Server 2012 RTM on a fresh Windows Server 2008 R2 machine – to see if it’s related to OS feature detection – if so -> Connect.
Now the general feeling of using SQL Server 2012 RTM is not much different from Denali CTP3 or 2012 RC0, I’m just having a very hard time getting used to Metro interface in Windows Server 8. For start – after installation of Master Data Services, SQL Server Data Tools, Data Quality Services and Database Engine I have two screens full of icons scattered all around instead of nice grouping. And – bad luck – SSMS is on the second screen
SQL Server 2012 feature selection on Windows Server 8 Beta
So, for the test #1: In Windows 8, when you select Database Engine at the Feature Selection dialog you have following information:
- Power Shell 2.0 already installed – quite obvious
- Visual Studio 2010 Shell and .NET Framework 4 to be installed – by SQL Server installer
- .NET Framework 3.5 – Windows feature to be turned on
The installer does not check for .NET 3.5 availability in your system until actual installation, so you will complete whole setup wizard and get an error at the very end.
As far as test #2 is concerned – if you install SQL Server 2012 RTM on Windows Server 2008 R2 and you forget to enable .NET Framework 3.5, the installer will stop right after Feature Selection asking to fulfill the software requirements. Apart from .NET 3.5, any existing Visual Studio 2010 installation must be upgrade to SP1 to allow SQL Server 2012 to continue.
And if you go back to SQL Server 2008 R2, it will not even launch the setup landing page if you have .NET Framework 3.5 missing.
Posted in SQL Server | Tagged: installation, SQL Server 2012 RTM, Windows Server 8 | Leave a Comment »
Posted by Szymon Wójcik on March 8, 2012
I have scripted a method of granting EXECUTE permissions to certain stored procedures or functions a while ago and then it hit me – this is a kind of reinvent-the-wheel example. It works, it can be used for extending rights to certain objects, but if you want them all, there’s easier way.
You might want to create a database role for all stored procedures and assign users to it. Be aware, though, that by design a user through a procedure gets a possibility to manipulate the data, and that GRANT EXECUTE on a procedure preceeds DENY INSERT/DELETE/UPDATE on a table in permission evaluation. You should reconsider granting EXECUTE permissions to all procedures for too many users, as it is exposes a security hole.
Posted in SQL Server | Tagged: execute, stored procedures | Leave a Comment »
Posted by Szymon Wójcik on March 7, 2012
As I stated yesterday evening, trial installation of Windows Server 8 followed by SQL Server 2012 was about to happen.
And it did. Without much success, though.
Windows Server 8 installation – no problem, 30 minutes and you’re done (I am using standard Toshiba Satellite L650-1NT laptop – Core i5 560, 4GB RAM, standard 250 GB HDD).
Sysprep and VM start after sysprepping – no problem as well, you’re set after 10 minutes.
SQL Server 2012 RTM – installation looks no different than SQL Server 2008/R2 or Denali. But… error occured during actual installation – no .NET Framework 3.5. How come? The previous installer used to detect missing .NET 3.5 and enable it. The setup continued till the end, but neither DB engine nor management tools have been installed.
So it’s a point to be validated – either new installer doesn’t check it and you have to enable .NET 3.5 prior to installation(which you should, BTW) or it’s a case of Windows Server 8 that does not get it’s features recognized (that would be a shame). More tests to come.
Posted in SQL Server, Windows Server | Tagged: .NET Framework 3.5, installation, SQL Server 2012 RTM, Windows Server 8 | Leave a Comment »
Posted by Szymon Wójcik on March 6, 2012
This week brings plenty of new servers to play with. The hype is not over with Windows Server 8 and yet today Microsoft announces availability of SQL Server 2012 RTM for download. General Availability (GA) for new SQL Server is scheduled for 1st April.
I’m downloading now, tomorrow morning I’m installing it on my home PC with Windows Server 8.
Posted in SQL Server | Tagged: RTM, SQL Server 2012 | Leave a Comment »
Posted by Szymon Wójcik on March 1, 2012
Microsoft released public beta of Windows Server 8 just yesterday. It is available for download here in two flavours (DVD ISO image and ready-to-go VHD). I downloaded both, but for obvious reasons I chose VHD for start. It allows you to have new server running in less than 10 minutes thanks to native VHD boot features in Windows 7. Keep in mind that a disk you download is an expandable one with maximum of 40 GB and it is expanded on boot to it’s maximum – if you don’t have enough free space, you will encounter the revamped BSOD.
The features look pretty amazing. Storage engine has completely been redesigned with SMB 2.2, transparent failover, deduplication, new file system and lots of other interesting features (check for example this article). Not to mention Hyper-V 3.0 with extended resource support (256 CPUs and 2 TB of RAM per machine – not that easy to spare at the moment, but don’t forget Moore’s Law!), enhanced clustering capabilities (up to 4000 machines per cluster, no shared storage required) and redefined Live Migration. And BTW, the interface has been modified – since it’s Windows 8, it’s Metro, with the Server Manager being the core of server operations, now allowing for multi-server administration (looks pretty cool, but couldn’t get it to work yet).
All I am waiting for now are few hours of spare time to play with it a little bit more, perhaps install SQL Server 2012 RC0 (already done by Aaron Bertrand – read the full story here) and try some clustering to take advantage of simplified storage provisioning.
Posted in Windows Server | Tagged: Windows Server 8 | 1 Comment »