In my previous post in my ongoing series on SQL Server database corruption I covered a list of best practices for responding to database corruption when it happens. In that post I mentioned that I’d provide a follow-up post on the specifics of how to execute page-level restore operations from within SQL Server. Page Level Restore Operations As documented in Books Online, SQL Server’s RESTORE DATABASE command has an optional parameter or switch that instructs it to only restore one or more pages instead of an entire database – which is very handy and helpful in dealing with situations in which only a hand-full of pages have been corrupted by some sort of problem with the IO subsystem. Accordingly, the basic order of operations for this kind of operation is as follows Kick the database into single user mode. You can set the database to RESTRICTED_USER, but if you have end-users or applications connecting using logins that map to the db_owner role, they’ll still be able to connect to the database. So use SINGLE_USER mode instead and do NOT close the query window you use to set to SINGLE_USER. To set the database to SINGLE_USER mode, execute the following: ALTER DATABASE SET SINGLE_USER WITH ROLLBACK AFTER 10 SECONDS GO And note that the WITH ROLLBACK AFTER n SECONDS option can/will kill connections and on-going operations. It’s documented in here (towards the end of the page). Likewise note that to pull this recovery operation off you’re, OBVIOUSLY, kicking everyone out of the database. Then, START your recovery process by backing up the tail-end of the log file – or the portion of your log file that hasn’t already been backed up. (This is CRITICAL as it ensures that all operations up to a certain point have been accounted for. And you’ll want to do this AFTER switching the database to SINGLE_USER mode – otherwise you risk allowing a few operations to ‘sneak’ by – which means that end-users or ...Read the rest of entry >>

Microsoft recently launched Trust Services for Windows Azure, an application-level encryption framework that can be used to protect sensitive data stored on the Windows Azure Platform. Read more......Read the rest of entry >>

by Mark Kromer This is a follow-up from my blog post last month here on SQL Server Pro about utilizing common traditional reporting tools like Excel and Report Builder with the new SQL Server 2012 Tabular models (http://www.sqlmag.com/blog/sql-server-bi-blog-17/business-intelligence/report-builder-tabular-model-databases-142942) … I’ve received a few notes about some confusion concerning the processes to publish PowerPivot-generated models and the Visual Studio SSDT-generated models to SSAS 2012. I was sent a question pointing to the currently empty Books on Line entry on the “Restore from PowerPivot” technique that I was advocating: http://msdn.microsoft.com/en-us/library/hh230822.aspx. So, it seems like a good idea to dive a little deeper into 2 methods to publish your tabular models into SSAS 2012, again without the benefit of SharePoint 2010. In my mind, SharePoint 2012 provides the cleanest and best mechanism to publish PowerPivot-generated models because all you have to do is Save-As and publish to your PowerPivot library and then both the model and the report, if you have any charts in your XLSX, will get published automatically for you. The fact that SharePoint stores the model as an in-memory SSAS cube is golden and saves you many of these steps. That being said, let’s have at it without SharePoint and fill-in those blanks from the on-line help: STEPS TO PUBLISH MODELS TO SSAS 2012 DIRECTLY FROM POWERPIVOT You’ve created a PowerPivot data model that you want to share so that your business can build reports from it. Users will be able to use any tool that can connect to SSAS through the Analysis Services client drivers, including Report Builder, Excel and Power View. You cannot publish it directly from a local client SSMS because SSAS will want to “restore” a database from PowerPivot, which means you need to transfer your XLSX to the server first. Share a folder from the SSAS server and add that shared folder to your local PC/laptop where you’ve crea...Read the rest of entry >>

I recommend that you set your SQL Server to use the startup traceflag DBCC TRACEON (3226) to disable backup success message......Read the rest of entry >>

Three weeks back, I wrote about the pending release of programming legend Charles Petzold’s most recent book, the 6th edition of the classic Programming Windows. Well, that day has finally arrived! And if you buy now, you can get in cheap. Really cheap....Read the rest of entry >>

An attendee at a recent performance tuning session wanted ideas to more flexibly react to locks on blocks than using the WITH (NOLOCK) hint....Read the rest of entry >>

Early-bird discounted registration for the much-anticipated relaunch of the Microsoft Exchange Conference (MEC) finishes on May 18, which is tomorrow for most people. This public service message is brought to you in an attempt to help resolve the nagging question “which conference is likely to deliver the best Exchange content for me in the next year”. The operational and technical environment that people deal with differs enormously from deployment to deployment and a single answer won’t apply in all cases. For instance, it’s pretty clear that Microsoft will use MEC as the launch vehicle for Exchange 2013 with Kevin Allison, GM of Exchange, promising that “MEC will be full of Exchange 15 content” when he keynoted at TEC in San Diego earlier this month. Therefore, if you really must learn all you possibly can about the latest and greatest version of Exchange, you’ll be one of those who packs their Mickey Mouse ears and heads to Orlando in September to join the MECfest. On the other hand, if you’re more interested in the details of practical deployment, tips and techniques, and the nitty-gritty of current versions of Exchange, you’d probably be better off investing in a conference fee for either TEC or Exchange Connections. That is, if these conferences continue to function in a world where a lot of the available attention and attendee dollars is being vacuumed up by MEC....Read the rest of entry >>

Windows 7 is arguably one of the most hardened and regularly updated OSes available, and now Apple and the Macintosh are in the headlines for fighting off malware and patching vulnerable software....Read the rest of entry >>

Question: I came across a weird case recently where we had a server that was running at 100% across all CPUs for a time but when I used the DMVs to look at wait statistics I couldn’t see anything going on. How can I see what waits were occurring at the time? Answer: This is a question that came up in our Performance Tuning class this week in London after I’d lectured about wait statistics. The key point here is that in the situation described there weren’t any actual waits occurring. Many of the monitoring scripts/tools for looking at wait statistics (for instance mine at http://bit.ly/fSWeO5) aggregate all the wait statistics and return the top 95% of all waits occurring, sorted by most prevalent waits by total wait time. This is done so that your view of the pertinent waits on the server isn’t obscured by those waits that are benign and always occur (e.g. from certain system tasks like the lazywriter in the buffer pool). A side effect of this monitoring is that unless there are no other waits occurring on the system, you will not see the SOS_SCHEDULER_YIELD wait type show up, even though they may be occurring in great numbers. Why is this? The SOS_SCHEDULER_YIELD wait type occurs when a thread executing on a CPU manages to execute for its entire quantum – the uninterrupted time a thread is allowed to be using the CPU before it must allow other threads to have a turn executing. The SQL Server scheduling quanta is 4 milliseconds (and this is not configurable). When a thread exhausts its quantum, it moves off the CPU, but instead of being suspended and put on the waiter list, it moves directly to the bottom of the runnable queue in the scheduler. This makes perfect sense as the thread isn’t waiting for a resource – so has no reason to be put on the waiter list. However, a wait type must be registered and so the SOS_SCHEDULER_YIELD type is used and it will have a zero resource wait time. There may be a signal wait time though (the time the thread spends on the ...Read the rest of entry >>

With cloud services, even Office 365, what you don’t know about your cloud service can hurt you, this SharePoint expert found....Read the rest of entry >>