SQL Server Magazine - Tue, 21 Feb 2012 12:13
By Michael K. CampbellIn previous posts within this series, we’ve already looked at how you can’t really prevent corruption. Instead, in order to be able to better deal with corruption, you need to be able to detect it early. To that end, there are actually a number of different ways to enable early (or early-ish) detection of corruption when it happens. And, in this post, we’ll look at the use of CHECKSUM Page Verification as the first of these methods. Enabling CHECKSUM for Page Verification When configured correctly, SQL Server can instantly spot when data that it is pulling off of disks was saved improperly – or corrupted. To enable this functionality, you just need to ensure that desired databases are configured to use the CHECKSUM option for page verification – which you can set on SQL Server 2005 and above. To do this, simply query your server as follows, and review the names of all databases returned – as they are not configured for CHECKSUM verification: .sqlcode { font-size: 13px; font-weight: bold; font-family: monospace;; white-space: pre; -o-tab-size: 4; -moz-tab-size: 4; -webkit-tab-size: 4; } .sqlcomment { color: #00aa00; } .sqlstring { color: #aa0000; } .sqlfunction { color: #aa00aa; } .sqlkeyword { color: #0000aa; } .sqloperator { color: #777777; } USE master GO SELECT NAME ,page_verify_option_desc FROM master.sys.databases WHERE page_verify_option_desc != ’CHECKSUM’ GO Then, to enable (or toggle) these database to use CHECKSUM verification, execute the following statement per database that you wish to ‘convert’ to using CHECKSUM verification: .sqlcode { font-size: 13px; font-weight: bold; font-family: monospace;; white-space: pre; -o-tab-size: 4; -moz-tab-size: 4; -webkit-tab-size: 4; } .sqlcomment { color: #00aa00; } .sqlstring { color: #aa0000; } .sqlfunction { color: #aa00aa; } .sqlkeyword { color: #0000aa; } .sqloperator { color: #777777; }
SQL Server Magazine - Mon, 20 Feb 2012 17:11
By Brian MoranI’ve coached dozens of people through starting small businesses over the past decade. Everyone eventually has to decide ‘what entity should I pick? I’m not a CPA nor did I sleep in a Holiday Inn Express last night. So, please, run anything I say by your accountant. But the truth is that most people choose their first entity without understanding all of the options out there. Worse. They often make the wrong choice.
SQL Server Magazine - Fri, 17 Feb 2012 11:36
By Michael OteyMicrosoft has announced that SQL Server 2012 will include support for Hadoop. In this editorial article, Michael Otey explains the possibilities that Hadoop can bring to SQL Server DBas.
SQL Server Magazine - Fri, 17 Feb 2012 10:26
By Blair GreenwoodCheck out the latest products from Melissa Data, Wise Coders Solutions, MobiForms, Red Gate, and Sanbolic.
SQL Server Magazine - Thu, 16 Feb 2012 09:55
By Kalen DelaneyKalen Delaney offers an expert tip about a simple code change that can be made to search arguments (SARGs) to improve SQL Server performance.
SQL Server Magazine - Wed, 15 Feb 2012 09:06
By Kevin KlineTwo quick notes from our friends at INETA: the Component Coding Challenge and INETA Community Champs.
SQL Server Magazine - Tue, 14 Feb 2012 15:31
By Brian MoranI’m fascinated and incredibly excited about the concept of data science and the so called scientists who do this job. I had never heard the term Data Scientist until last December when I ran across it in a blog by @BuckWoody. (http://sqlblog.com/blogs/buck_woody/archive/2011/11/15/the-data-scientist.aspx). Since then it’s been popping up in conversations I have with people everywhere like a bizarre game of whack a mole.
SQL Server Magazine - Mon, 13 Feb 2012 14:00
By Michael OteyHere’s a quick overview of the SQL Server 2012 family, including Enterprise, Business Intelligence, Standard, Web, Developer, and Express editions.
SQL Server Magazine - Thu, 09 Feb 2012 13:49
By Kimberly L. TrippQuestion: Partitioned tables were the new shiny feature in SQL Server 2005 – why are partitioned views even still available? Are there any benefits that they provide? Answer: In the first post (Partitioned Tables v. Partitioned Views–Why are they even still around?) I raised some of the general questions and concerns that come with VLT (very large table). Today, I’ll start to tackle why partitioned views can be a fantastic choice for partitioning large sets – even for new design. When I first posted, some folks emailed me and said that the most obvious reason to choose partitioned views is that they don’t require Enterprise Edition. While that’s a fantastic reason, it’s still not the only reason (or my favorite). The most important reason for me is that there are still a few concerns around VLT that even partitioned tables don’t solve. In this post, I’ll tackle two concerns with partitioned tables. The first issue is related to statistics. Statistics always cover the entire set described by the index (or statistic). For the purpose of this discussion, I’m going to focus primarily on table-level statistics (meaning statistics that are NOT filtered). And, while you can use filtered statistics with partitioned tables – there are significant limits to fast-switching. As a result, they might solve one problem yet introduce another. So, why are table-level statistics an issue? Really, in concept, they’re not. But, remember, a statistic must be relatively small to be useful. With larger and larger tables (especially those that have a lot of uneven data distribution), it becomes more and more difficult for a statistic to be accurate. Additionally, it also takes longer and longer for the statistic to get updated. There is a special trace flag available to change the invalidation threshold for statistics (so that they don’t wait as long to get updated) but there’s nothing that changes their accuracy (except filtered stats – which then eliminate fast-switching if the fi
SQL Server Magazine - Thu, 09 Feb 2012 10:51
By Michael K. CampbellIn previous posts we touched upon an overview of what causes corruption and looked at ways to simulate corruption. In this post we’ll talk about more about corruption – in terms of addressing how you can prevent it. (Though, if you’ve been paying attention in previous posts, you’ll probably guess what’s coming up). How to Prevent SQL Server Database Corruption In answer to the question: “How can I prevent corruption?” the simple, and honest, answer is: “You really can’t”. As covered in previous posts, corruption is ‘more or less’ a random problem that occurs at the disk subsystem level and really can’t be prevented. (Or as I like to tell all of my consulting clients: “There’s a reason that EVERY major Operating System out there ships with something like CHKDSK.”) So, while disk/storage errors should be rare within any production system, the fact is that these kinds of errors aren’t so much a question of IF they’ll happen, but WHEN they’ll happen – simply because the reality is that magnetic storage is insanely complex and subject to the occasional ‘hiccup’ that can result in corruption. However, given that corruption SHOULD be a relatively rare problem that you will encounter in your environment, it IS safe to say that if you keep bumping into seemingly-regular or semi-regular problems with corruption, it’s fair to say that you may need to start becoming concerned about whether there’s a problem with your hardware or possibly even drivers and so on. (The problem, however, is that troubleshooting these kinds of problems is insanely hard – so if you do start to recognize regular problems with corruption it may be time to start thinking about migrating critical systems to new hardware.) However, even though you can’t prevent corruption, that doesn’t mean that you are powerless against its effects. In fact, not only does SQL Server readily accept or anticipate that disk subsystem errors can and WILL occur, but it also provides a number of great tools and function
SQL Server Magazine - Wed, 08 Feb 2012 10:00
By Denny CherryThere is a setting which is available within Cisco Fiber Channel switches that I ran across while configuring some new Cisco MDS switches that looked great for security. The setting is called "globalEnforcePriv" and the basic idea as I understand it is that it globally enforces privacy on all the accounts on the switch. Read on to see how it shot me in the foot.
SQL Server Magazine - Thu, 02 Feb 2012 20:23
By Kimberly L. TrippQuestion: Partitioned tables were the new shiny feature in SQL Server 2005 – why are partitioned views even still available? Are there any benefits that they provide? Answer: This is actually a question I get at almost every event at which I speak. It’s a common question and it’s actually VERY complex to fully describe. I suspect it might take me more than one post to tackle all of the issues but I’ll start with the basics here. First and foremost, I’ll start by saying that partitioning is CRITICAL for VLT. What is VLT? It’s about as descriptive as VLDB and it means very large table. (Yes, I just made it up as a new TLA (three-letter acronym) that I’m planning to start using more. However, I’m also going to quantify it a bit more.) Most people speak of VLDBs (very large databases) and they define that as databases that are 100s of gigabytes (many would say that a database that’s 1TB or larger is a VLDB). For me, and in my experience, *many* customers run into problems long before their databases reach 1 TB; their problems tend to start when they have even just one table that starts to get well into double-digit gigabytes. Think about it, a single table that’s 60 GB presents a variety of problems. And, to highlight where the problems occur – think about these questions in the context of your larger tables: Is all of the data recent? How old is the oldest data in your 5 largest tables? How much of that data changing? Not including the new data coming in – what percentage of the older data needs to be modified? How often are you accessing the older data? How long are your maintenance processes against it? Are you replicating it? Do you have enough memory to fit the table (and all of its indexes) in cache? Do you really need to have indexes on ALL of that data? Or, does your data have different access patterns (which might warrant different indexing strategies)? For many, these questions start to pose many concerns at table sizes in the mi
SQL Server Magazine - Wed, 01 Feb 2012 10:00
By Denny CherryNo... Oh you wanted more information than that? OK Here goes...
SQL Server Magazine - Mon, 30 Jan 2012 20:19
By Michael K. CampbellIn my last post I provided an overview of what SQL Server database corruption is – and how it’s almost always caused by problems at the IO subsystem (or disk) level. However, while it’s all fine and well to talk about things in such a theoretical sense, in that post I also mentioned that a great way to get a ‘feel’ for how corruption works is to simulate it a bit on your own. Accordingly, in this post I’ll provide a step-by-step walkthrough of what that looks like by simulating some corruption. Setting the Stage Obviously, when it comes to actively trying to ‘corrupt’ a database there are a couple of caveats that need to be addressed – above and beyond the OBVIOUS caveat that this is something you’d never want to do with production database. (It IS a great experiment to test against COPIES of your production databases though). Otherwise, the caveats to simulating corruption are that you’ll want to DELETE any potential existing data from msdb’s suspect_pages table (which we’ll talk about in a future post), and that you’ll obviously want to make sure that you’ve got a viable backup (even if it’s a simple copy/paste of an existing .BAK or .mdf/.ldf files) of whatever database you’ll be corrupting. In my case, I’m corrupting a copy of the AdventureWorks database that I have running in my environment – mostly because I just hate AdventureWorks so much. So, in my case I’m backing it up like so: Simulating Corruption Then, when it comes to actually simulating corruption, that ends up being a bit hard to do when SQL Server has its ‘hooks’ into the database in question – so I’ll just Detach it using the SSMS GUI, as follows: Once detached, the database is just a collection of ‘zeroes and ones’ that I can then open up and ‘mangle’ as needed. At this point I then just need to find the actual .mdf file for this database (which I happen to know is in my D:\SQLData\ drive on my test server), and then I can open it up with an application other than SQ
SQL Server Magazine - Sun, 29 Jan 2012 21:45
By Paul RandalQuestion: Several times in the last year I’ve been forced to run the repair option of CHECKDB because of corruption affecting a database and backups being unavailable. In one case the database was a replication publication database and I followed the Books Online guidelines to reinitialize it’s subscribers after running repair. Can you explain why this is necessary? Answer: Any time that the REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB (hereafter just called ‘repair’) is used, you need to consider the effect on replication if the database being repaired is a publication database. There are two cases to consider – if the repairs affected the replication metadata tables or not. The simplest is when they did not affect the replication metadata tables. When using merge replication, changes to the publication database are captured using DML triggers. When using transactional replication, changes to the publication database are captured by analyzing the database’s transaction log and converting the logged operations into logical operations on the database. In both cases the logical operations are then applied to the replication subscribers. Neither of these mechanisms allows the capture of operations performed by repair. Repair operations are always direct physical changes to the database structures to fix inconsistencies in the structures (e.g. a database page, table record, or a linkage between two pages), as opposed to physical changes because of queries performing inserts, updates, or deletes on tables. These repair operations cannot translate into logical operations that can be applied to replication subscribers because there are no logical operations than can be expressed using Transact-SQL for the equivalent of the direct structural changes that repair is performing. This means that if any table that is part of a replication publication is changed by a repair operation, the replication subscription is no longer valid and must be reinitialized. As an example,
SQL Server Magazine - Fri, 27 Jan 2012 09:55
By Michael K. CampbellWhen it all comes down to it there are really only two main things that DBAs need to worry about: making data available to the proper people, and making sure it’s inaccessible to the not-so-proper people. All other considerations are really just appendages to these two concerns. For example, performance is just an extension of making data available to the right users – because if they can’t access it in timely fashion, then it’s not really as ‘available’ to them as it should be. As such, concerns around disaster prevention and recovery should rank high on every DBA’s list. Yet, surprisingly, I find that most DBAs (especially the ‘reluctant’ ones) don’t have enough of a grasp on how to sufficiently protect against database corruption. I therefore thought it would be fun to launch into a multi-part set of posts that provide a hands-on overview of SQL Server Database Corruption Basics – including an overview of what corruption is, why you can’t really prevent it – and what you can do to deal with that reality such that you’re able to ensure proper availability and uptime of your data even when corruption occurs. What is Database Corruption? For the purposes of this series of posts, database corruption is defined as a problem associated with the improper storage of the actual zeroes and ones needed to store you database data at the disk or IO sub-system level. In this sense, the corruption addressed in this series of posts is VERY different from other kinds of ‘disasters’ that might render business data useless (such as when a developer or admin runs an UPDATE statement without a WHERE clause or a software glitch incorrectly ‘mangles’ unit prices). However, while user/application errors or ‘disasters’ are technically much different than disasters caused by corruption, it’s important to note that many of the mitigation strategies outlined in this series of posts for addressing SQL Server database corruption ALSO represent best-practices that can easily be leverag
SQL Server Magazine - Thu, 26 Jan 2012 13:13
By Brian MoranBrian Moran highlights the SQL Server 2012 Virtual Launch Event and explores how Apple’s iBooks 2 electronic textbook publishing platform will change the way SQL Server professionals read SQL Server books and attend conferences.
SQL Server Magazine - Thu, 26 Jan 2012 11:18
By Itzik Ben-GanItzik wraps up his discussion of SQL Server 2012’s window functions by focusing on optimization. He provides indexing guidelines for optimal performance, and he describes cases with especially optimal treatment, cases that compute the difference between two cumulative values, cases that expand all frame rows, and cases in which the optimizer can use an in-memory spool versus an on-disk spool.
SQL Server Magazine - Thu, 26 Jan 2012 09:54
By Robert SheldonThese five tips help you avoid a few frustrations that might arise when developing SQL Server Integration Services (SSIS) packages so you keep your development efforts moving forward.
SQL Server Magazine - Thu, 26 Jan 2012 09:08
By Stacia MisnerConfiguring authentication in SSRS isn’t always a straightforward task because it occurs at multiple points and involves multiple technologies. This guide will show you the basics so you can get started.
SQL Server Magazine - Thu, 26 Jan 2012 08:00
By Saravanan RadhakrishnanLoading new data and purging old data from a table can take a lot of time if a large amount of data is involved. Here’s an elegant partition solution that loads and purges data dynamically.
SQL Server Magazine - Wed, 25 Jan 2012 10:00
By Denny CherryAn awful lot of rain half way around the world is keeping me from getting hard drives delivered to a data center in New Jersey.
SQL Server Magazine - Thu, 19 Jan 2012 15:37
By Kalen DelaneyLearn how you can replace a scalar-valued user-defined functions (UDFs) call with actual code to greatly improve SQL Server performance.
SQL Server Magazine - Wed, 18 Jan 2012 10:00
By Denny CherryThe answer to the question here will depend greatly on what storage platforms you have available to you. Assuming that you have some sort of storage array which supports replication to a remote data center you have some pretty neat options available to you.
SQL Server Magazine - Mon, 16 Jan 2012 12:36
By Mark Kromerby Mark Kromer Late last year, Microsoft announced the availability of an updated beta or community technology preview (CTP) of SQL Server Reporting Services in the Cloud based on the Microsoft Azure platform: http://blogs.msdn.com/b/windowsazure/archive/2011/10/13/announcing-sql-azure-reporting-preview-release.aspx. The product is formally known as SQL Azure Reporting Services and if you’d like information about the preview, visit the link above for all the details. I just wanted to spend a few minutes today introducing you to the new CTP and what I’ve found there so far. I’ve built a Cloud BI Web-based sample app and a Windows Phone 7 sample app using SQL Azure Reporting Services and I promise that I will blog about how you can build one, too, in the next coming months. This should be a natural progression of my Microsoft Cloud BI: All the Pieces series. One thing that I really liked in this CTP is that I am able to manage users so that I was able to make demo users who can access my reports from my Web-based Cloud BI solutions with a read-only report user style account. You can also upload and download RDL files to and from the cloud and your laptop from the Azure management screen: I was not able to access the normal SSRS styled Report Manager link from SQL Azure Reporting Services. But the Azure Management Screen also contains an area within the Silverlight console that looks like the Report Manager console and has similar functions like when you select a report and use the drop-down menu in the Details view of Report Manager: The Report Server Web Services URL is available in this CTP, just as it was in the previous CTP. Of course, a key tenet and advantage of any area of the Azure platform from Microsoft is the fact that all of my existing developer and administrator tools will essentially still work the same in Azure as they do today in the traditional on-premises world meaning that I won’t need to learn new tools or new design paradigm
SQL Server Magazine - Fri, 13 Jan 2012 15:51
By Michael OteyLearn why Michael Otey thinks that SQL Server 2012 is the next evolution of Microsoft enterprise data platform.
SQL Server Magazine - Fri, 13 Jan 2012 13:00
By Blair GreenwoodCheck out the latest products from Panorama Software, CA Technologies, PlumChoice, Melissa Data, Werysoft, and Idera.
SQL Server Magazine - Fri, 13 Jan 2012 12:00
By Kevin KlineThe Scripted PowerShell Automated Deployment Engine is a free, remote install and configuration tool for SQL Server that streamlines remote deployment and simplifies complex installation processes.
SQL Server Magazine - Wed, 11 Jan 2012 10:00
By Denny CherryWhen purchasing a PDW which vendor should we use?
SQL Server Magazine - Mon, 09 Jan 2012 17:22
By Kevin KlineCheck out the latest update for SQL Server FineBuild, a tool which speeds the installation and configuration of SQL Server and associated tools and programs.
SQL Server Magazine - Mon, 09 Jan 2012 03:28
By Paul RandalIn a departure from our usual Q&A style I want to post a follow-on to my previous discussion about how transaction log corruption can lead to transaction log backup failures, and how to recover from them (see here). Any operation that tries to use a corrupt log record will encounter failure, and DBCC CHECKDB is one of those operations. By default, DBCC CHECKDB will create a hidden database snapshot under the covers to provide a transactionally-consistent view of the database on which to run the consistency checks. The process for creating a database snapshot is to checkpoint the real database, and then essentially run crash-recovery on the real database, but into the context of the database snapshot – not affecting the real database. This pseudo-crash recovery rolls back the effect of any uncommitted transactions that are occurring in the real database, making the database snapshot consistent. If this process encounters a corrupt transaction log record, then the database snapshot creation will fail – leading to the DBCC CHECKDB failing too. A bunch of errors will be generated, including one that identifies the corrupt transaction log record, like below: DBCC encountered a page with an LSN greater than the current end of log LSN (141131:0:4) for its internal database snapshot. Could not read page (9647:-33648758), database ’PaulsDB’ (database ID 26), LSN = (-1302554001:2131886119:4432), type = 255, isInSparseFile = 1. Please re-run this DBCC command. The page ID and its LSN are obviously completely wrong. All is not lost, however, as there are two ways around this issue. Firstly, you could employ the technique I described in the blog post I referenced above (switch to the Simple recovery model, checkpoint to truncate the log, and switch back to the Full recovery model), but you’d have to make sure there are no uncommitted transactions otherwise the transaction log may not truncate past the corruption. Secondly, you could use the WITH
SQL Server Magazine - Fri, 06 Jan 2012 03:29
By Paul RandalQuestion: I came across a situation where a transaction log backup failed because of corruption in the transaction log. One of my colleagues put the database offline and then online and problem went away. Can you explain why and whether log corruption can lead to a suspect database? Answer: Transaction log corruption is interesting because it doesn’t usually cause any problems apart from failed backups. However, that doesn’t mean it should be ignored. As I’ve stated many times, the vast majority of corruptions are caused by the I/O subsystem and a transaction log file is just as likely to be corrupted as a data file. Unless the corruption occurs in the active portion of the log (the portion that is required for some reason by SQL Server – see this article for background information) then its likely that no-one will ever know it occurred. This may seem disturbing to you, and in a way it is – undiscovered corruption isn’t good – but there’s no way for SQL Server to process the entire log, only the active portion. DBCC CHECKDB doesn’t analyze the log at all any more since I rewrote it in SQL Server 2005, instead it will only use the active portion of the log as a by-product of creating a database snapshot to run the consistency checks on. There’s no consistency checking of the transaction log – only checksums that are checked as log records are read, for whatever reason. The active portion of the log will also be used if a transaction rolls back, if crash recovery has to run, or when a transaction log backup occurs (plus a bunch of other uses like replication and mirroring – the aforementioned article has more information). If a damaged log record is encountered during one of these uses then an error will be thrown and the operation will fail. The only time a database will be marked SUSPECT because of a corruption in the transaction log is if the corruption is encountered during crash recovery or during a transaction rollback. In that case, the operation will fail
SQL Server Magazine - Thu, 05 Jan 2012 16:56
By Denny CherryIn short, yes. The LUN or disk which hosts the backups is probably the disk which needs the most write performance.
SQL Server Magazine - Mon, 02 Jan 2012 19:26
By Michael K. CampbellUntil now, my previous two posts have largely just been a review of best practices for making backups redundant – with only a single mention about RESTORE VERIFYONLY’s LOADHISTORY argument as a means of providing a rationale for enabling luke-warm failover servers from those backups. And, again, just for the record: there ARE better High Availability solutions out there for SQL Server (such as Mirroring, Log Shipping, SQL Server 2012’s AlwaysOn happiness, clustering, and (in some cases) replication). As such, the point of these posts is really just to cover options for less-expensive failover options for organizations that don’t need ‘full-blown’ failover options and as a means of describing some additional ways to make ‘full-blown’ HA solutions more redundant and capable of recovery – as you never know when you’ll need some sort of Remote-Availability or smoke-and-rubble contingency. That, as I’ve said over and over again, high-availability isn’t the same as disaster recovery. So, with all of that said, the big problem at this point is that just having copies of your backups in secondary/remote locations is NOT a disaster recovery plan. Yes, having copies of your backups off-site is better than nothing – but merely having copies of your backups off-site isn’t going to help much in an emergency – especially if we’re talking about multiple databases of any decent size. That, and unless you’re regularly testing these secondary/luke-warm backups, you not only have any idea whether they’re viable or not, but you don’t accurately know if they’re capable of meeting RPOs and RTOs. To that end, you need what I like to call a ‘mass recovery’ script. The Benefits of a Mass Recovery Script In my mind there are two primary benefits of a ‘mass recovery’ script. First and foremost, my experience is that most DBAs aren’t as familiar with all of the ins and outs of restoring databases in an emergency as they need to be. Or, as I like to tell many of my clients: “The LAST thi
SQL Server Magazine - Mon, 02 Jan 2012 11:36
By Kevin KlineCheck out the latest weird, gruesome, and deformed IT horror story from international technology speaker Kevin Kline. Now coming twice per month!
SQL Server Magazine - Wed, 28 Dec 2011 16:28
By Paul RandalQuestion: I’m involved in a project to design a database schema and some of the tables are expected to have millions of rows and a large amount of LOB data (a mixture of binary and character). I know there are a bunch of options for storing this data but I’m struggling with how to choose between them. Can you help? Answer: The answer to this question depends on the size of the data and how it will be used. There are two kinds of data type for storing LOB data – true LOB data types that can store more than 8000 bytes and the limited data types that can store up to 8000 bytes – and of course there are pros and cons for each type. If your data is going to be less than 8000 bytes then it makes sense to use one of the limited data types – (n)varchar (1-8000) or varbinary (1-8000). When the data type becomes really small (say less than 5 bytes), you need to decide whether to use a fixed-length type – (n)char – instead of a variable length type, to avoid the two-byte overhead that comes with variable-length columns. If you need to store 2-byte character set data, you’ll have to use nchar or nvarchar. These data types are always stored ‘in-row’ – i.e. inline with the row they are part of on the same data file page (except for the special case when the row grows beyond 8060 bytes, but that’s beyond the scope of this answer). This means that the rows may become quite large and so very few rows can be stored per data file page. If the LOB data is not going to be used very often, this reduces the data density of the columns that are going to be used frequently – meaning more data file pages will have to be read and stored in memory to process the columns being used and lowering the efficiency of data processing operations. You may decide, if this is the case, to store the seldom-used LOB columns in a separate table, and JOIN to them when they are needed, or to store them in true LOB columns that are stored off-row (i.e. in separate data file pages, but still in the same ta
SQL Server Magazine - Wed, 28 Dec 2011 10:00
By Denny CherryKnowing which counters can be trusted to give you valid information and which counters can’t be trusted is very important to properly monitoring and troubleshooting storage and Microsoft SQL Server.
SQL Server Magazine - Tue, 27 Dec 2011 17:39
By Mark KromerBy Mark Kromer We’re coming up to the end of 2011 and it’s been a while since I’ve posted here on the SQL Mag BI Blog. In fact, in the 1.5 months sine I’ve posted here, the site’s name has changed to SQL Server Pro and SQL Server 2012 Release Candidate (RC0) was released! Well, since this is a BI blog for SQL Server, it only makes to end the year in style. Let me introduce you briefly to perhaps the most highly-anticipated new BI feature and new tool in SQL Server 2012, Power View: http://technet.microsoft.com/en-us/library/hh213579(SQL.110).aspx. What is Power View? Power View is a brand new Silverlight browser-based reporting tool that is currently scheduled to ship with SQL Server 2012 Enterprise Edition and the new Business Intelligence editions, once general availability hits in 2012. This is the current plan as of RC0: http://msdn.microsoft.com/en-us/library/cc645993(v=sql.110).aspx. You access the reporting tool from a SharePoint 2010 library or BI site and it is geared toward business users. You get one surface to design on and to report from. There is no click-once client or separate tool to download for designing and viewing. That is still the general model for the existing Report Builder and SSRS reporting tools in SQL Server and those still exist with their own enhancements in SQL Server 2012. But the biggest and most exciting advances in SSRS 2012 are in the Power View tool. I’ve used Silverlight components in the past and Derek and I have references different ways to utilize Silverlight in your BI applications here on this blog. Power View gives you that power and interactive data exploration capability out of the box, meaning that your business users can be much more engaged with data assets in the data warehouse or anywhere in your organization, which encourages better decision making. If you have used the SSRS tools in the past like BIDS with Visual Studio or Report Builder, then you will notice a bit of a paradigm change with Power Vie
SQL Server Magazine - Fri, 23 Dec 2011 13:03
By Michael K. CampbellFollowing up on my previous post, when it comes to the need to create off-box backups, there are really only two (well, three) main reasons you’d want to do Off-Box Backups: Three Primary Reasons for Off-Box Backups First: Redundancy. As I pointed out in my last post: If you’re only keeping backups and data on the same server or hardware, then you’re DOING IT WRONG. From an elementary Disaster Recovery (DR) standpoint you always need a copy of your backups ‘mirrored’ to at least one other location. Drives can fail, RAID controllers can fail and take drives/data with them, and a host of other REALLY UGLY things can happen to data stored on a single host/server. Without off-box backups, then, you’re a sitting duck. And therefore, the reason, in this case to have off-box backups is a question of simple redundancy. (And, as I pointed out in my last post, an additional benefit of ‘off-box’ backups is that you can commonly store these redundant backups on less-expensive (and more voluminous) storage where you can actually, typically, keep copies of backups longer than you could on your primary server. But again, as I mentioned in my last post – you want to make sure you’re keeping backups locally on your primary server as well – to help avoid incurring the cost of pulling backups over the wire WHEN you need to recover. So, in this case, think redundancy of your data. Second: Closely related to the first reason for why you’d want to keep backups in off-box locations is the simple fact that sometimes entire servers can fail. A Windows Update, or the addition of a driver might render a box completely non-responsive. In which case, trying to get backups off of that box, or it’s RAIDed HDs is going to be nothing short of a nightmare. That, and the point of this series of posts is to describe how to effectively set up ‘luke warm’ standby servers. So, in that case, knowing that you need a redundant location for your backups, and knowing that you might need a redundant HOST o
SQL Server Magazine - Thu, 22 Dec 2011 10:48
By Brian MoranThe SQL Server community is known for banding together to help those in need, whether they have a SQL Server-related question or a charity they want to support. Here’s two acts of giving that have inspired Brian Moran.
SQL Server Magazine - Wed, 21 Dec 2011 17:19
By Blair GreenwoodCheck out the latest database administration products in the SQL Server market from Attunity, Idera, and expressor software.
SQL Server Magazine - Wed, 21 Dec 2011 13:30
By Paul RandalQuestion: We’ve set up database mirroring for one of our critical databases with a witness server to allow automatic failover. Everything has been running fine until we had a power outage on the data center recently. Database mirroring performed the failover but people reported that the application just hung. When we manually failed back, the application worked again. Why didn’t the application failover too? Answer: This is a reasonably common problem that people encounter when using database mirroring and a production failure like this often happens because no failover testing is conducted after database mirroring is implemented. We became involved after the failed failover. To avoid taking production downtime, we duplicated the mirroring environment on the client’s test systems. After confirming that the application and database mirroring were working, we powered down the principal server and the application completely hung. We checked that the mirror server had successfully initiated a failover and was online as the principal server. We also checked that the mirror database was online, could be used locally on the new principal server, and the principal server was accessible from a remote client, like the application uses. This led us to check the application. We talked to the developer, who confirmed the application was using ADO.NET to connect to SQL Server and was using explicit client redirection, specifying the mirror server name in the ConnectionString property of the SqlConnection. (As an aside, it is always better to use explicit client redirection rather than relying on the mirror server name being cached automatically on the client once a connection has been made – implicit client redirection). So why wasn’t the application failing over? We dug deeper into how the application was handling connection failures and discovered that it was not coded to cope with the existing connection failing at all! Basically the application would open a connection t
SQL Server Magazine - Wed, 21 Dec 2011 10:00
By Denny CherrySo a few weeks ago I got a nice little present in the mail from Crucial. It was one of their nice new(ish) M4 SSDs with a massive 256 Gigs of free space. Here’s a review of my experience with the drive so far...
SQL Server Magazine - Thu, 15 Dec 2011 10:59
By Paul RandalQuestion: I’ve started seeing a problem where periodically the scheduled DBCC job fails and I get 823 errors in the error log. I also see errors from the file system too. Is this an I/O subsystem problem? Answer: These errors are not caused by I/O subsystem issues, although outwardly they seem to be. Under the covers all of the DBCC CHECK commands create a hidden database snapshot. When the database snapshot is created, any active transactions in the database are effectively rolled back into the database snapshot (not affecting the real transactions, of course) so that the database snapshot is transactionally consistent. Also, while the database snapshot exists, any data file pages in the real database that are going to change need to be copied into the database snapshot before they change so that their state at the time the database snapshot was created is preserved. These two factors mean that there is the potential for the database snapshot to grow very large – potentially up to the size of the real database. If that happens, there is the possibility that the database snapshot may exceed the size that NTFS can cope with and error 665 will result, as below: The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x000005bd3dc000 in file ’Test.mdf:MSSQL_DBCC8’ This error comes from Windows Server 2008 – Windows Server 2003 will give error 1450. You can read more about this in this blog post from Product Support. You may see a variety of SQL Server errors too – including 823, 7928, 1823, and 3314. Check to make sure that the 823 errors are referencing a file with a name like ‘MSSQL_DBCC’ (the database snapshot files) rather than one of the real database data files. If the latter then you have a real I/O subsystem problem. If you’re seeing these errors when you’re running your consistency checking job at a time when there shouldn’t be any user activi
SQL Server Magazine - Wed, 14 Dec 2011 10:00
By Denny CherryThis is really an it depends question. There are a variety of things which can influence which technology you select.
SQL Server Magazine - Tue, 13 Dec 2011 13:32
By Michael K. CampbellSQL Server’s RESTORE statement is insanely powerful. Not only because of the obvious fact that it can be handy in a disaster (when you have regular backups in place), but because it’s also so incredibly versatile. So versatile, in fact, that I’d wager that most DBAs only use a fraction of the various options and capabilities that it provides. And, to that end, I wanted to take a multi-part look at how just one or two rarely used facets of the RESTORE command can be used to great benefit when it comes to increasing overall disaster preparedness. Taking RESTORE VERIFYONLY For a Spin I’m guessing that many DBAs have bumped into the VERIFYONLY argument of the RESTORE clause – as it’s something that many DBAs will either explicitly implement themselves as part of a scripted, regular, backup of their databases, or it’s potentially (hopefully) something that they’re doing with their backups through whatever GUI they’re using to schedule regular backups. But, just to make sure we’re on the same page, here’s what Books Online says about it: Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. In Microsoft SQL Server, RESTORE VERIFYONLY has been enhanced to do additional checking on the data to increase the probability of detecting errors. The goal is to be as close to an actual restore operation as practical. For more information, see the Remarks. From within SQL Server Management Studio, the option to run this command is ‘baked in’ to ad-hoc backups – as shown in Figure 1. Figure 1: The ‘Verify backup when finished’ checkbox executes RESTORE VERIFYONLY under the covers. There’s also a similar GUI option called ‘Verify backup integrity’ in the ‘Backup Database Task’ that can be defined as part of a SQL Server Maintenance Plan. Likewise, there is typically a simi
SQL Server Magazine - Tue, 13 Dec 2011 09:54
By Itzik Ben-GanSQL Server 2012 enhances support for window aggregate functions. In part 2 of this series, Itzik discusses window offset functions and window distribution functions.
SQL Server Magazine - Mon, 12 Dec 2011 11:20
By Michael OteyNew to SQL Server 2012, the AlwaysOn Availability Groups feature is the next evolution of database mirroring. Find out what AlwaysOn Availability Groups are, what you need to use them, and how to create and manage them.
SQL Server Magazine - Wed, 07 Dec 2011 14:32
By Paul RandalQuestion: I’ve been told that it’s possible to run out of values when using the bigint data type and that I should use GUIDs instead, regardless of the problems they can cause. Is this true? Answer: This is similar to the question I answered last year on whether it’s possible to run out of virtual log file sequence numbers – where the answer is yes, but it would take 240 million years to do so. See this post for the VLF discussion. Similarly, yes, you can run out of bigints but it’s not practical that you will. Bigint allows you to have +/- 2^63 (or +/- 9,223,372,036,854,775,808). Say for argument’s sake that you’re able to process 100 thousand pieces of data per second, and you assign an ever-increasing bigint value for each one. You’d have to be processing continuously for 2^63 / 100,000 / 3,600 hours – which works out to be 2.925 million years. Now that’s just using the bigints – if you wanted to store them too, you’d run out of storage space before running out of numbers. Doing a quick test of a heap with a single bigint identity column shows me that I can get 453 rows per 8KB data file page (don’t forget the record overhead, slot array overhead, and that the heap pages won’t be filled completely because of the way the free space caching and searching works). A terabyte of data would store roughly 61 billion rows. At that rate, actually running out of bigints AND storing them would take roughly 150 thousand petabytes. This is clearly impractical – especially when you consider that simply storing a bigint is pretty pointless – you’d be storing a bigint and some other data too – probably doubling the storage necessary, at least. So yes, while it is theoretically possibly to run out of bigint values, in reality it’s not going to happen.
SQL Server Magazine - Wed, 07 Dec 2011 10:00
By Denny CherryWhile it isn’t the most common place that a performance bottleneck can happen, the fiber channel switches or Ethernet switches if you are using iSCSI or Fiber Channel Over Ethernet (FCoE) can become a performance bottleneck. The switches which run the network that the data travels over, reguardless of the technology involved can become a bottleneck.
SQL Server Magazine - Thu, 01 Dec 2011 10:26
By Paul RandalQuestion: I’m having problems with an application vendor who is mandating that we run regular DBCC SHRINKDATABASE operations against the application databases and tempdb. They say this is necessary for performance – can you give me some advice please? Answer: I get asked this at least once a month, where an application vendor refuses to allow a DBA to remove regular shrink operations because they’re ‘necessary for performance’. We all know that shrinking databases causes index fragmentation, consumes CPU and IO resources, and generates a lot of transaction log (which can cause problems for mirroring, replication, and so on). We also know there are some extenuating circumstances where occasional one-off shrinks may be necessary. Furthermore, we know that *regularly* shrinking databases is a cardinal sin because if the database repeatedly grows after being shrunk, all that shrink work is completely wasted effort – its akin to having auto-shrink enabled for the database. The problem I see is that the vendor application teams *don’t* know these things about shrink, and are loath to listen to anyone trying to educate them. Occasionally I’ll jump on an email thread with the original sender and the application vendor team. The justifications from the application vendor team are usually along the lines of the following (paraphrasing): The indexes in the database are already fragmented so shrinking doesn’t make it any worse. Nobody’s ever complained about performance before so why are you? We have to have a regular shrink because the operations we do cause the database to expand a lot and customers want their disk space back. We have to shrink tempdb because the operations we do cause it to grow continually. None of these are valid reasons for regularly shrinking databases, and in fact it’s documented in KB article 307487 that shrinking tempdb when there’s user activity can lead to tempdb corruption, and the Working with Tempdb in SQL Server 2005 whi
SQL Server Magazine - Tue, 29 Nov 2011 20:15
By Michael K. CampbellWhile I like to pride myself on being professional enough to admit when I’m wrong, it turns out that I’m a bit more shallow than I’d like to be. For example, I commonly tell my clients that I don’t know everything about SQL Server (and that anyone who claims to know everything is someone they should run away from). Consequently, I don’t mind being wrong when I ‘guess’ at things with them as we’re figuring out various problems and issues together. But as I’ve found out in the last day, admitting that I’m wrong has actually been a bit of a nightmare – for two reasons: First because I was wrong in a case that really mattered: I offered flawed professional advice. And that, in turn means that I got to simultaneously feel sick about offering bad advice while also feeling dumb about doing so in ‘front of everyone’. Second, this was a nightmare because this was sadly a case where I’ve been WRONG about a key concept for entirely too long. Years in fact. I Was Wrong Given that I was wrong, I want to do two things with this post: First, I want to correct the bad information that I posted previously. To that end, rather than correcting my previous posts, I’m going to leave them intact and ‘update’ them with warnings about the bad info – along with a link to this post. Second, I also want to take a look at HOW I ended up being so wrong because I think a post-mortem of my mind-set might be instructive and can hopefully help others avoid falling into similar traps. (And if I’m honest, I also want a chance to, umm, defend myself a bit.) How I was Wrong In the previous two posts: SQL Server Backups – When More is Less and Unused Secret Weapon – COPY_ONLY Backups, I erroneously linked COPY_ONLY backups to providing ‘protection’ for transaction log backups. That was incorrect. Transaction Log backups are NOT susceptible to the problems I outlined in terms of breaking the backup chain. Only DIFFERENTIAL backups are. Being wrong about that sucks. But it’s also worth m
SQL Server Magazine - Mon, 28 Nov 2011 12:00
By Megan KellerSQL Server Magazine has a new name -- SQL Server Pro! We’ll continue to provide hands-on content that shows DBAs, developers, and BI analysts how to use and troubleshoot SQL Server.
SQL Server Magazine - Fri, 18 Nov 2011 14:19
By Megan KellerThe SQL Server 2012 Special Ops Tour could come to your city and you could be featured at an event.
SQL Server Magazine - Fri, 18 Nov 2011 09:00
By: EditorsSQL Server Magazine editors and readers recognize standout products in eight categories, making it easy for you to find the right tool.
SQL Server Magazine - Thu, 17 Nov 2011 14:15
By Megan KellerMicrosoft’s SQL Server 2012 Release Candidate 0 is now available for download and include new capabilities over the SQL Server Denali CTP3 release.
SQL Server Magazine - Thu, 17 Nov 2011 12:21
By Michael K. CampbellCOPY_ONLY backups were a great addition to SQL Server 2005 (and up). Without them, it was all to easy for DBAs, devs, and SysAdmins to create a ‘backup’ for dev/testing purposes which would actually, silently, break the Log Chain and put production data in severe risk. In other words, if: a) A Non COPY_ONLY backup was made, b) A new or scheduled FULL/DIFFERENTIAL backup hadn’t yet been made, and c) A database disaster occurred Then DBAs _HAD_ to have that non-copy only backup on hand or they wouldn’t be able to recover from the disaster properly – because they’d be missing the proper baseline from which to begin applying transaction log backups. What’s So Secret? As I’ve mentioned before, the notion of breaking the log chain can be a bit counter-intuitive. (Seriously, backing something up can actually BREAK it? That’s not something you expect to find with many systems. Yet, if you understand what’s going on and WHY the log file is so essential to SQL Server, then the fact that you CAN break the log chain becomes something that is not only second nature – but which you’re constantly vigilant against.) Yet, while I think that increasing numbers of DBAs (including even reluctant DBAs) have a decent understanding of the benefits of COPY_ONLY backups in terms of protecting the log chain, I think that the overwhelming majority of DBAS don’t realize that log file backups can ALSO be used against COPY_ONLY backups as well. Therefore, since a picture is worth a thousand words, here’s a diagram that I think increasing numbers of DBAs understand and get – in terms of why COPY_ONLY backups are so important. Figure 1: Why it’s so important to use COPY_ONLY Backups (or keep non-COPY_ONLY backups on hand until next FULL/DIFF backup). On the other hand, while the benefits of COPY_ONLY backups are understood from the sole standpoint of PROTECTING the log chain for a PRODUCTION database, Figure 2 shows that there are also some other, very important, benefi
SQL Server Magazine - Wed, 02 Nov 2011 10:19
By Kevin KlineKevin tells you how he is now keeping up with the thousands of blog posts, newsreader feeds, e-newsletters, and social media entries in his information hoard.
SQL Server Magazine - Mon, 31 Oct 2011 12:22
By Michael K. CampbellFor most reluctant-DBAs there’s a certain degree of mysticism or ‘unknown’ about the SQL Server backups that they get ‘roped’ into managing. Moreover, for the uninitiated, there are a number of things about how SQL Server works that can seem counter-intuitive. Such as auto-closing and auto-shrinking databases – things that may seem logical to Systems Admins who are tasked with ‘optimizing’ resources but which, in reality, are easily some of the worst things you can do to your SQL Server databases. SQL Server and Disaster Recovery Just as counter-intuitively: while the data in a SQL Server database may be all sorts of mission critical, more backups of that data may actually JEOPORDIZE your ability to recover a database after a disaster – because MORE backups of SQL Server databases may, in fact, result in less coverage and protection. For example, one thing that I see fairly regularly is that reluctant DBAs who are handed ownership of SQL Server databases typically just don’t get the kinds of training or background needed to help them demystify SQL Server Backups. So, what commonly happens is they’ll spend a bit of time with their ‘new responsibility’ and commonly use SQL Server Management Studio to create a Maintenance Plan that (among other things) executes FULL backups at let’s say… 1AM every night. Then, what commonly happens is plenty of time passes with no problems, incidents, or issues. And the sense of urgency to figure out how these backups work gets pushed into the background – by more immediate problems and day-to-day needs. Then, commonly, these same reluctant DBAs commonly make a critical mistake a bit later on which is while reviewing their normal system-level backups for some Well-Known-3rdParty-Backup-Solution(TM) they decide “Hey, this 3rd party solution that has saved my bacon with file-server or AD or Exchange backups in the past actually supports native SQL Server backups; I’m going to go ahead and provide EXTRA coverage and protection
SQL Server Magazine - Mon, 31 Oct 2011 09:23
Here’s a handy little block of T-SQL code to improve code stability.
SQL Server Magazine - Wed, 12 Oct 2011 13:27
By Michael OteyAt the keynote in this year’s PASS 2011 conference Ted Kummert announced that SQL Server 2012 will include a new immersive data visualization technology named Power View. Formerly code named project Crescent, Power View is a data exploration and visualization tool that enables ad-hoc queries from a variety of devices including new touch-based devices like Windows 8 will support. At the PASS keynote technical Fellow Amir Netz demonstrated working with Power View on both a Windows Phone 7 and an iPad. Power View will replace the older Report Builder 1.0 ad-hoc query that was first introduced in SQL Server 2005. The Power View technology is included in SQL Server Denali CTP3.
SQL Server Magazine - Wed, 12 Oct 2011 12:48
By Michael OteyAs a part of their any data, any size, anywhere initiative, at this year’s PASS 2011 conference in Seattle Ted Kummert, Microsoft Corporate Vice President Business Platform Division announced Microsoft’s intension to deliver Hadoop based data management to Windows Server and Windows Azure. Hadoop is a distributed computing platform that is written in Java. It uses a MapReduce technology to distribute computing resources across clusters of computers to rapidly process extremely high volumes of data. At first Microsoft’s embracing of Hadoop may seem a bit confusing but when you view like their incorporation of OLAP services back in SQL Server 7 you can see that support for Hadoop is really another step along the evolutionary path of SQL Server becoming a complete enterprise data platform. Hadoop will enable the processing of high volumes of data as well as unstructured data. A CTP is planned to be released before the end of the year. The Hadoop implementation will be integrated with both SQL Server relational databases as well as Analysis services. Microsoft announced Hadoop Connectors for SQL Server and the Parallel Data Warehouse. Customers can use these connectors for bi-directional integration of Hadoop with SQL Server’s relational and BI data. Hadoop data will be able to be consumed using Microsoft’s BI tools, including Microsoft PowerPivot and Power View
SQL Server Magazine - Wed, 12 Oct 2011 10:26
By Megan KellerSQL Server 2012 was announced at PASS Summit 2011 as the formal name of SQL Server Denali.
SQL Server Magazine - Thu, 06 Oct 2011 16:47
By Mark Kromerby Mark Kromer For those SQL Server and development shops that have built a lot of SQL Server Integration Services (SSIS) packages out there and have utilized Visual Studio Team Foundation Server or Visual SourceSafe to provide version control over those SSIS packages, you will be glad to learn about the new versioning capabilities in SQL Server v-next, code named “Denali”. In fact, when I’ve been trying out the new version of SSIS, I have found the overall deployment experience to be a huge step forward over the 2005/2008 deployment model with many advances in terms of configuration management and deployment to a server. For today, I’m just going to focus on version control, because it’s been difficult in the past to get something working that is easy to configure and get working. And it is very nice to see this now natively in the product. In the picture below, you will see that a new node is available in the Object Explorer tree in SQL Server Management Studio (SSMS) that is set aside just for Integration Services. You can still log in directly to the SSIS service from SSMS, but that is only going to show SSIS packages that were deployed in the old, more arduous model, which you will see referred to as “Legacy Model” in BIDS. The new model is simplified and wizard-driven. The first thing you have to do is to set-up an SSIS “Catalog” which stores SSIS “Projects” that then store SSIS “Packages”. Now when you right-click on the Projects node in the tree, you will see a “Versions” menu item which launches the screen below. Note that on this screen you will see the project versions, which one is active, descriptions and the option to rollback to an earlier version with “Restore to Selected Version”. You will see 2 packages in my project on the screenshot to the left. Notice that “Project” is essentially project-level from BIDS, which is now Visual Studio 2010 in Denali. When I rolled back to my initial version of the project, I lose the 2nd pack
SQL Server Magazine - Sun, 02 Oct 2011 05:03
By Itzik Ben-GanItzik describes new T-SQL scalar functions in SQL Server Denali CTP 3.
SQL Server Magazine - Tue, 27 Sep 2011 03:50
By Kevin KlineDownload this powerful and free utility to extend your SQL Server administration capabilities.
SQL Server Magazine - Mon, 26 Sep 2011 11:24
By Michael OteyOne of the things that became clear after the preview of the next version of Windows Server (code named Windows Server 8) and the direction that Microsoft announced at its recent BUILD 2011 conference is the fact that Microsoft is seriously moving toward using PowerShell as its preferred management tool. While the GUI isn’t going away Microsoft has added almost 2000 PowerShell cmdlets to Windows Server 8 – radically expanding the areas that can be managed with PowerShell. While not everyone knows it SQL Server can already be managed using PowerShell. SQL Server 2008 and 2008 R2 both provide PowerShell cmdlets that let you navigate the database, create database objects, and run queries. You can start SQL Server’s PowerShell cmdlet by entering sqlps on the command prompt or by opening SSMS and right clicking on an object. While most DBAs don’t use PowerShell yet now would be great time to get started learning it. There is a learning curve for PowerShell but once you get the hang of it it’s reasonably straightforward. There’s no doubt that PowerShell will be an important management tool going forward and it’s more capable than you might think. For a more complete introduction to using PowerShell with SQL Server you might want to check out:: http://msdn.microsoft.com/en-us/library/cc281954.aspx In addition, you might want to read Accessing SQL Server Data from PowerShell by Robert Sheldon at InstantDoc ID# 100458.
SQL Server Magazine - Mon, 26 Sep 2011 00:48
By Itzik Ben-GanNot terminating T-SQL statements with a semicolon is on a deprecation path.
SQL Server Magazine - Wed, 14 Sep 2011 20:00
By Michael OteyAt this past BUILD Conference in Anaheim CA Microsoft released the details of the next release of the Windows Server operating system currently known as Windows Server 8. Here are some of of the highlights in the new Windows Server 8 operating system. New multi-server capable Server Manager Server Core will be the default installation The GUI is now considered a feature which enables you to perform your initial server configuration using the GUI then remove it when you’re readily to move into production. Storage pools enable you to group together individual disks Thin provisioning and storage deduplication are provided out of the box New support for continuously available file shares with SMB 2.2 Clusters support 63 nodes and 4000 VMs Support for installing SQL Server databases on file shares Hyper-V 3.0 in Windows Server 8 will support hosts with up to 160 logical CPUs and up to 2 TB RAM. Hyper-V 3.0 VMs will support up to 32 virtual CPUs with up to 512 GB RAM per VM providing much greater scalability for your SQL Server virtual machines. Live Migration in Hyper-V 3.0 supports multiple concurrent Live Migrations Live Migration without the requirement for shared storage between the different virtualization hosts Live Storage Migration enables you to move a virtual machine’s configuration, virtual hard disk and snapshot files to a new location without incurring any downtime for the VM. Network virtualization enables multiple virtual networks on the same networking hardware Hyper-V Replica will provide support for built-in asynchronous replication of Hyper-V VMs Built-in NIC Teaming works across multiple heterogonous vendor NICs
SQL Server Magazine - Tue, 13 Sep 2011 16:38
By Mark Kromerby Mark Kromer I’ve been a long-time advocate and big proponent of having a mechanism in my end-to-end BI solution that can show me what has changed and what will changed BEFORE something breaks. By end-to-end BI solution I mean from the data source to the ETL to the data mart to semantic layer to the scorecards and dashboards. If something changes anywhere in my complex system of data integration and data analysis, I need the system to either self-heal (ultimate vision!) or at least flag this and let me know the impact. In fact, back when I was a BI consultant and PM for BI solutions, we used to include a custom-built Microsoft Console Snap-In that would collect your solution’s metadata from database to scorecard, store that data about your data and allow you to analyze changes flagged as red for breaking change, yellow for caution, etc. SQL Server Codename Denali (SQL Server v-next) had the original public beta as CTP1 last year that included a “Dependency Service” which is meant to be a SQL Server based lineage and impact-analysis tool. You will notice that in CTP3, that service is gone from Object Explorer. But have no fear! For those Microsoft BI solution architects out there, if you have not seen it yet, Microsoft is still working on this capability under the project name “Project Barcelona”. This is their blog here and I recommend you visit it if you would like to have a sneak peak at the tool and find out the latest from the project team’s efforts. One thing that is really cool on their blog is that they have a link to where you can try out and provide feedback on the UI. below is a screen capture sample of the latest UI that they have put up publicly for comment: You should check it out and submit your feedback. Even if you don’t car
SQL Server Magazine - Fri, 02 Sep 2011 11:43
By Kevin KlineThe last quarter of the year is usually my busiest due to the fantastic PASS Summit and SQLBits events. The fall looks a little different ... BECAUSE IT WILL BE EVEN BUSIER! Here’s a rundown of where I’ll be this fall through mid-October.
SQL Server Magazine - Wed, 31 Aug 2011 18:13
By Michael OteyAt this past VMworld 2011 in Las Vegas VMware announced their new vFabric Data Director. vFabric Data Director is a platform that’s intended to allow enterprise databases to function as a cloud service. In his VMworld 2011 keynote Paul Martiz, CEO of VMware, referred to this as Database-as-a-Service (DBaaS). vFabric Data Director is designed to allow administrators to securely automate and delegate routine administrative tasks making the database more self-service. These tasks include database provisioning, backup, and cloning. Currently the vFabric Data Director only supports a customized version of PostgresSQL that VMware created called VMware vFabric Postgres. However, VMware has published their standard for the service. At this point neither SQL Server or Oracle support the new vFabric Data Director service. You can find out more about vFabric Data Director at: http://www.vmware.com/products/datacenter-virtualization/vfabric-data-director/
SQL Server Magazine - Wed, 31 Aug 2011 17:42
By Itzik Ben-GanItzik describes the enhanced support for window functions introduced in Microsoft SQL Server Denali CTP3.
SQL Server Magazine - Thu, 25 Aug 2011 15:15
By Michael OteyThe new SQL Server Developer Tools (previously codenamed Juneau) is designed to close the gap between relational database development and BI development. From my point of view the new Developer Tools is overdue. It never did make sense to me why I needed to do T-SQL development using SSMS but when I wanted to use Reporting Services or Integration Services I needed to use BIDS – this was especially annoying because you knew that both SSMS and BIDS were both built on top of the Visual Studio shell. A recent project that I’ve been working on really pointed out this problem. While working on an ASP.NET application I needed to have Visual Studio 2010 open for working on the web application, SSMS open for developing the T-SQL stored procedures and database schema, and BIDS open for developing the Reporting Services reports used by the application. Then I spent a good deal of time tabbing between the different environments. You might point out that I could have done my T-SQL development in Visual Studio but I’ve never liked Visual Studio for T-SQL development. There are little problems like the data types not being displayed in the explorer dialog but the main issue is a lack of visibility into the SQL Server instance. Denali’s new SQL Server Development Tools is supposed to solve a good chuck of these problems by combing the relational and T-SQL development capabilities from SSMS with the BI development features in BIDS. Don’t worry both SSMS and BIDS are still present in Denali – at least as far as CTP3 goes. However, BIDS may be dropped from the final release. My experiences with SQL Server Developer Tools were a bit disappointing. The SQL Server Developer Tools are not part of the CTP3 package itself. Instead, you have to download and install them using the slow and failure prone Web Platform Installer. The Web Platform Installer, which failed three times during the excruciatingly slow download and installation process, is a great example of how ch
SQL Server Magazine - Wed, 24 Aug 2011 12:54
By Megan KellerSQL Server Magazine’s print issues have many uses, and in this photo they are used as a cat’s bed. Provide your photo caption ideas for a chance to win a prize!
SQL Server Magazine - Sat, 20 Aug 2011 01:00
By Mark Kromer by Mark Kromer In the world of data warehouse specific databases, column-oriented databases (as opposed to the classic row-based databases) are becoming very popular. One of the most popular pure-play column-oriented databases is from Vertica and Oracle databases with Exadata storage includes hybrid columnar compression, which maintains the row structure, but compresses the data very effectively with common values in columns. The next release of SQL Server (11.0 or Denali, if you like) will include Microsoft’s first release of their database engine to include columnar compression, which is being surfaced to database developers and DBAs as columnar indexes. The technology that Microsoft is using inside SQL Server for the column-oriented compression and indexing is based on the same Vertipaq technology that is currently in use in PowerPivot and being expanded upon in Denali as a secondary Analysis Services engine that will provide an alternative to the MDX/UDM engine. Much of what I am describing here is fully expanded upon in this whitepaper by Microsoft’s Eric Hanson which I highly recommend that you download and read. It is brief and very effective. What I want to also highlight in terms of the Microsoft implementation of columnstore indexes is that the data on the data pages in a columnstore index are stored as pure column storage, not as a hybrid row and column model. The example columnstore index from that whitepaper is reproduced for you here: CREATE COLUMNSTORE INDEX cstore on [dbo].[catalog_sales] ([cs_sold_date_sk] ,[cs_sold_time_sk] ,[cs_ship_date_sk] ,[cs_bill_customer_sk] ,[cs_bill_cdemo_sk] . . . Basically, what they are doing is adding ALL rows from the fact table of their star schema data warehouse data model in this index. The query optimizer will make the determination of using the columnstore index or another index or heap for you. I’ve also copied a screenshot of how you can acc
SQL Server Magazine - Mon, 15 Aug 2011 14:56
By Michael OteyWith all of the recent push to the cloud by various vendors it’s easy to forget that there are really two ways of implementing cloud computing. For sure, the big cloud vendors push is for businesses to move to public cloud computing. However, many organizations have leaned more toward the private cloud. The public cloud and the private cloud offer similar promises: reduced operation expenses, greater elasticity of resources, and self-service management. The big difference is that using the public cloud requires leasing resources from some other vendor’s web-based infrastructure. In contrast, you implement the private cloud as a management layer on top of your own internal infrastructure. Not surprisingly, most IT professionals are far fonder of the idea of the private cloud than they are the public cloud because the private cloud leverages your existing infrastructure and its keeps vital aspects of your infrastructure like performance, availability and security under the control of your own company. But if you think about it does the private cloud really work for SQL Server? SQL Server is not like IIS where workloads can be balanced between multiple servers. In other words, you can’t add an additional SQL Server instances to your cluster and get any performance benefits. For SQL Server, clusters only offer enhanced availability – not enhanced performance. SQL Server users are connected to a given database and that database has a given configuration and properties. While that may be the case that doesn’t mean that you can dynamically alter the available computing power for a SQL Server instance. Virtualization is the key to implementing SQL Server in the private cloud. SQL Server instances running in VMs can be dynamically live migrated to hosts with greater resource availability and computing power. Further, if the SQL Server instance is running in a VM and the host has the available physical capacity you can dynamically increase the number of virtual CPUs and RAM
SQL Server Magazine - Mon, 08 Aug 2011 15:09
By Michael OteyThis past week I was vividly and personally reminded about what I think is probably the single biggest problem to cloud computing: the last mile. Cloud providers do provide service level agreements (SLAs) for different levels of promised availability but that really doesn’t help anything when the problem happens in that last mile of wire between your network provider and your local location. In my case, I was just out of service for over two days because a service truck took out the telephone wires right outside my office. Thanks to the fates and some unusually skillful driving (or whatever) my office was the only building affected– lucky me. Both the phone and my Internet connections were gone and it was a bit enlightening about exactly how much I tend to use those two things. It took Qwest two days and two repair calls to get the everything working again. All in all, looking at the pig picture it was really more of an annoyance than a disaster. All my main servers are located on-premise and my product testing and other office work continued on without too much trouble. Email and web-browsing were out. However, with so many vendors focusing on the cloud these days I couldn’t help but think about what would have happened if the critical services I was using were cloud based. For this kind of local outage the cloud provider could not have held been responsible. While their services are dependent on that last mile of networking they are also independent of your Internet service provider. In this case, the outage would have been the responsibility of the customer. So there would have been no refunds for the cloud provider not meeting their SLA. An SLA in itself is no guarantee of availability. Certainly there are ways to help mitigate the last mile problem. For instance, you can arrange for high priority services level from your Internet provider and you could arrange for duplex Internet service providers. Both of these measures would help but eve
SQL Server Magazine - Wed, 03 Aug 2011 16:39
By Mark Kromerby Mark Kromer Here is a quick peek at some of the enhancements to Master Data Services (MDS) in SQL Server Denali as of CTP3. Before I do that,though, let’s take a look at what MDS is from a SQL Server product perspective … MDS is Microsoft’s MDM tool that is included in SQL Server 2008 R2 Enterprise Edition and above. The lineage of the product is from the Microsoft acquisition of Stratature and the MDS release in SQL Server 2008 R2 was the first release of the Statature IP with Microsoft. MDS is meant to be a tool that you can use to build data domains around your critical business entities like products, customers and employees and to use MDS as the tool in your master data management (MDM) projects to provide centralized data governance that creates single version of the truth repositories. The final step is to integrate MDS with your operational and analytical systems with common Microsoft data integration tools like SSIS and BizTalk. What MDS in Denali CTP3 provides is a big step forward in terms of the usability, applicability and analytical MDM by integrating with a brand new capability in the SQL Server data management suite called Data Quality Services (DQS). Ok, so without further ado, I will get you started with 3 new features in MDS in SQL Server Denali CTP3 (download it here) that I’ll highlight and then you can go and explore the rest of the suite in CTP3. Improved Web UI I’ll be honest, no matter what product I’m talking about, saying that a new version has an “improved Web UI” is just simply not a major feature of the product. An example is the way that Oracle has been touting the UI improvements in Fusion Applications over the Web-based navigation UI on some of their ERP apps. The MDS Web UI is meant as a tool for data stewards and so, like ERP systems, they are not going to be glamorous user interfaces. I’ve pasted a screen capture below which doesn’t really do it justice. But what is important to note is that if yo
SQL Server Magazine - Wed, 03 Aug 2011 14:53
By Kline, KevinGet the latest versions of two popular SQL Server code libraries.
SQL Server Magazine - Wed, 03 Aug 2011 12:47
By Ben-Gan, ItzikItzik provides the solution to last week’s table truncation challenge.
SQL Server Magazine - Tue, 02 Aug 2011 13:43
By Michael OteyThere’s no doubt that availability is one of the most important if not the most important job for the database administrator. However, choosing the right options isn’t always easy because there’s a lot of different options and each is designed for a different purpose. Here’s an quick overview of SQL Server’s different high availability options: Windows Failover Clustering – Microsoft’s premier high availability solution, Windows Failover Clustering provides sever level protection. Before Windows Server 2008 clustering was very difficult to setup. With Windows Server 2008 and higher this task is much easier but there are still many working parts. Clustering provides automatic failover and can be implemented with up to 16 nodes (each node is a separate server system). There is some downtime as services are shifted to the backup node. Clustering requires Windows Server Enterprise edition and higher. Database Mirroring – Unlike clustering database mirroring provides protection at the database level. Database mirroring automatically forwards transaction log entries from a principle system to a mirror system. Database mirroring is limited to two systems: the principle and the mirror. There are two modes of database mirroring: high safety and high performance. High Safety mode is only available in the SQL Server Enterprise edition and provides automatic failover but it also requires a third SQL Server system to act as a witness. One catch with database mirroring is that you need to be sure that server level objects such as logins are the same between both systems. Mirroring doesn’t handle this for you. Log Shipping – Log shipping is really more of a disaster recover technology. With log shipping transaction logs are periodically send from the primary system to one or more secondary systems. Unlike mirroring which forwards transaction log entries as they occur log shipping forward entries periodically whenever the SQL Agent job runs the log shipping stored procedure. Log
SQL Server Magazine - Fri, 29 Jul 2011 15:40
Itzik provides a challenge to perform fast clearing of the data from a table that has an indexed view defined on it.
SQL Server Magazine - Thu, 21 Jul 2011 16:07
By Mark Kromer by Mark Kromer We all know that data warehouses aren’t “real time”, right? I mean, a warehouse is there to store data for historical analysis, after all. But the struggle to separate operational reporting against an ODS or a reporting schema from BI reporting against a data warehouse continues to confound IT and business organizations. The “real time” data warehouse and analytics can be PowerPivot, after all, or smaller functionally-confined data marts. But if your data warehouse has a business requirement to be more “real time” than, say, the classic nightly load or even hourly, then I typically recommend using the “trickle-feed” or incremental update approach. In SQL Server 2008 R2, there are 2 mechanisms built into the product that you can use to accommodate this, provided that your data sources are SQL Server databases. So, let’s take a brief look at Change Tracking and Change Data Capture or CDC: Change Tracking This is a feature that is built into the query engine in SQL Server and is available in both the Standard Edition and Enterprise Editions of SQL Server. You do not have the kind of control over the tracking mechanisms that CDC provides and SQL Server performs some behind-the-scenes magic by adding flags to your rows once you’ve enabled tracking on. You can see the difference in the bytes per row on your tables when you enable change tracking on a table using sp_spaceused. Before you start building out your data warehouse ETL, you will enable change tracking on your database and then on each table to track with these commands: ALTER DATABASE AdventureWorks SET CHANGE_TRACKING = ON ( CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON ) GO ALTER TABLE dbo.Products ENABLE CHANGE_TRACKING GO As you can see
SQL Server Magazine - Wed, 20 Jul 2011 09:50
By Kline, KevinLinks to great Microsoft blogs and tool resources for networking, performance, and the Windows platform.
SQL Server Magazine - Fri, 15 Jul 2011 11:36
By Michael OteyVisual Studio LightSwitch is Microsoft’s new entry level development environment. It has been in development for almost two years. LightSwitch has the potential to fill in the low end development void that was left with the retirement of VB6. Microsoft is releasing the final version of Visual Studio LightSwitch on July 26th 2011. You can find out more about Visual Studio LightSwitch at: http://www.microsoft.com/visualstudio/en-us/lightswitch
SQL Server Magazine - Thu, 07 Jul 2011 14:00
By Itzik Ben-GanItzik covers solutions to last week’s puzzle – Reoccurring Visits.
SQL Server Magazine - Fri, 01 Jul 2011 08:00
By Mark Kromer by Mark Kromer Well, readers, all good things must come to an end. This is part 5, the final part of my series where I’ve walked you through the tools, techniques and pieces of the Microsoft Cloud BI puzzle that you’ll need to start developing your own Microsoft BI solutions that are Cloud (mostly, perhaps a bit hybrid) BI solutions. We’ve talked about created data marts using Microsoft’s cloud database, SQL Azure, based off your large on-premises SQL Server data warehouse using SSIS or da
SQL Server Magazine - Wed, 29 Jun 2011 09:44
By Itzik Ben-GanItzik provides a T-SQL challenge involving identifying reoccurring visits to a website.
SQL Server Magazine - Thu, 02 Jun 2011 11:43
By Kevin KlineWant to get up to speed on NoSQL ("Not Only SQL") databases? Check out these resources.
SQL Server Magazine - Thu, 02 Jun 2011 10:29
By Itzik Ben-GanDownload your free copy of a new e-book about Data Quality and Master Data Management for Microsoft SQL Server 2008 R2 by Dejan Sarka and Davide Mauri.
SQL Server Magazine - Wed, 01 Jun 2011 18:17
By Mark Kromerby Mark Kromer It is now time for part 4 of my 5-part series where I am walking you through the different Microsoft product and solution pieces to build a Microsoft Cloud BI solution. There are a few parts that I’ve called out thus far that are “hybrid”, i.e. not yet fully cloud-based. But today’s installment is going to focus on the presentation layer and we’re going to deploy these dashboards solely in the cloud, via Microsoft’s Azure platform. First, we’re going to use the new CTP (beta) of Azure Reporting Services to host a simple AdventureWorks dashboard that I built using Report Builder 3.0 in part 3 of this series. And, yes, that is currently a client tool that you need to have a local copy of to make this work. So we’re still quasi-complete in Cloud BI, still somewhat hybrid. But I’ll use our local copy of Visual Studio 2010 to build a simple ASP.NET application that will include the ReportViewer control hosting that Azure-based report, deployed in Windows Azure. Then another ASP.NET app will include a very simple, rudimentary example of a Silverlight control to display data from our SQL Azure database. Both of these presentation methods of Cloud BI use the same tools that you use today to build the classic SQL Server based dashboards and reports – Report Builder, Reporting Services, SQL Server, Visual Studio, Silverlight and ASP.NET. Instead, I’m just using SQL Azure, Windows Azure and deploying into the cloud instead of on a local server. Already being familiar with these tools and languages (T-SQL, MDX, .NET and C#), make the transition into cloud with Microsoft’s Azure platform quick & quite easy. Let’s start with the ReportViewer control in Visual Studio with SQL Azure. Again, not much different than what you can do today where you host an ASP.NET application that has the ReportViewer control embedded in a form and you point that control to your report. The report is usually best to have hosted in SQL Server 2008 R2’s Reporting Services (SSRS).
SQL Server Magazine - Tue, 31 May 2011 11:23
By Itzik Ben-GanItzik discusses certain behavior of the REPLACE function that might seem like a bug to some.
SQL Server Magazine - Mon, 23 May 2011 13:12
By Itzik Ben-GanItzik provides the materials for his TechEd Atlanta 2011 session; Code: DBI310; Topic: Efficient T-SQL Querying using SQL Server Denali’s Window Functions.
SQL Server Magazine - Thu, 12 May 2011 12:06
By Mark KromerBy Mark Kromer In part 3 of this 5 part series, Mark examines the new Azure Reporting Services CTP (beta) that enables SSRS-like reporting services in the cloud with Windows Azure & SQL Azure. So far, in part 1 & part 2, we’ve talked about migrating data into data marts in SQL Azure and then running analysis against that data from on-premises tools that natively connect into the SQL Azure cloud database like PowerPivot for Excel. That approach can be thought of as a “hybrid” approach because PowerPivot is still requiring on-premises local infrastructure, such as PowerPivot, for the reporting. Now we’re going to build a dashboard that will exist solely in the cloud in Microsoft’s new Azure Reporting Services. This part of the Azure platform is only in an early limited CTP (beta), so you will need to go to the Microsoft Connect site to request access to the CTP: http://connect.microsoft.com/sqlazurectps. Think of Azure Reporting Services as SSRS in the cloud. You will author reports using the normal SQL Server Reporting Services 2008 R2 tools. In this demo, I’m going to use the business user ad-hoc reporting tool called Report Builder 3.0. One of the great benefits of the overall Microsoft cloud platform called Azure, is that you can develop applications and databases and you can migrate to Azure from your on-premises solutions to Azure very easily. The tools that you are already familiar with: Visual Studio, SQL Server Management Studio, BIDS, Report Builder, SQL Server Management Studio, all work with SQL Azure and Windows Azure. For example, in Report Builder, you will design a report just as you normally would and enter in the URL for the Azure Reporting Services as the site to store or retrieve the report definition: Design the report based on a data source that is from SQL Azure, since this is a pure Cloud BI solution: Whether you are using Report Builder or BIDS in Visual Studio, you will enter the Azure s
SQL Server Magazine - Thu, 14 Apr 2011 10:18
By Michael OteyMarathon’s EverRun MX provides multi-server multi-core fault tolerance
SQL Server Magazine - Mon, 21 Mar 2011 10:22
By Blair GreenwoodGartner released its 2011 Business Intelligence Platform Magic Quadrant that lists vendors that should be considered when selecting a business intelligence (BI) platform to develop reporting and analysis applications.
SQL Server Magazine - Thu, 10 Mar 2011 12:04
By Megan KellerSQL Server Connections is one such event that’s coming up March 27–30th in Orlando, and I thought it would be fun to get a behind the scenes look at how the sessions are chosen as well as recommendations about opportunities attendees should take advantage of at the event. I turned to SQL Server Magazine authors and SQL Server Connections chairs Paul Randal and Kimberly L. Tripp to give us the inside scoop on the conference.
SQL Server Magazine - Thu, 24 Feb 2011 11:37
By Megan KellerSQL Server virtualization and cloud computing aren’t going away any time soon, so if I were you, I would take advantage of every opportunity to learn about these two technologies and how they’re going to affect you. A great opportunity to learn about these technologies is coming up—Virtualization Connections and Cloud Connections, two great events that are collocated in Las Vegas, April 17-21st.
SQL Server Magazine - Tue, 08 Feb 2011 15:54
By Megan KellerMichael Otey dives into SQL Server 2008 R2’s enterprise data access features and how you can use them to effectively manage your SQL Server environment in the video “SQL Server 2008 R2: Enterprise Data Access for Mission-Critical Applications.”