Ozzie.eu

The guys from SQLServerCentral.com published an article giving a Quick Tour of sp_ExecuteSQL. This is all very well, except for the antibodies in me regarding dynamic TSQL inside frequently used stored procedures.

Yes, it’s true that “Every once in a while you find yourself working with SQL Server and you need to execute a piece of T-SQL that will be  different at the time of execution from the time that the code is being written.” but pointing out SQL injection as the single concern is quite an understatement.

The key downside events regarding dynamic TSQL are statement compile time and execution plans. You can read on the official documentation at MSDN: “The Transact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql.”

Furthermore “sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement many times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.”

One can try to optimize this dynamic execution by using parameter substitution, so the the Transact-SQL string is built only one time. But there are still aspects you can’t control:

• What is the generated execution plan for the dynamic statement, running separate from the execution plan of the batch that called sp_executesql;


• Is the execution plan any good? You can’t really tell to try to change it;


• Good or bad, the plan is cached for the following executions. If the diversity of queries that can dynamically be run is large, there’s no gain from the plan caching;


• Using the RECOMPILE option solves the above issue but creates another: each time the stored procedure is called, the execution plan is recompiled. For long running queries this might be overlooked, but for queries otherwise fast, it’s a major performance drawback.

Having stated my displeasure for the dynamic TSQL, I can also recommend the following readings:

• The Curse and Blessings of Dynamic SQL


• Is Dynamic TSQL a Good or Bad Idea?

I apologize for the rant but in all applications I witnessed the development, using dynamic TSQL inside stored procedures caused nothing but trouble as soon as the tiny tables turned into a larger, business critical database with hundreds of users.

Link: Announcing the release of Service Pack 1 for Office 2013 and SharePoint 2013 - Office Updates - Site Home - TechNet Blogs

Announcing the release of Service Pack 1 for Office 2013 and SharePoint 2013 - Office Updates - Site Home - TechNet Blogs

Microsoft has made available a series of ebooks on the MVA web site. From technical overviews to drilldowns on special topics, these free ebooks are available in PDF, EPUB, and/or Mobi for Kindle formats, ready for you to download.

You can check them all out here:

http://www.microsoftvirtualacademy.com/ebooks

Happy reading!

While securing a Microsoft SQL Server instance, there are many issues you should look after. According to the official documentation, it can be viewed as a series of steps, involving four areas: the platform, authentication, objects (including data), and applications that access the system. On environments where SQL Server is used on a more “Swiss Army Knife” fashion, there is often difficulty on disabling or, at least, restricting access to the general extended stored procedures, like xp_cmdshell. These stored procedures provide an interface from an instance of SQL Server to external programs for various maintenance activities. But they also represent a security liability. The alternative suggested is if possible to use SQL CLR Integration to perform these tasks. It can be done and it works, but maybe it isn’t a desired task by the regular DBA to learn to program a .NET Framework language.
With this in mind, as far as SQL Server Agent jobs are concerned, I’d suggest creating a Powershell job step:

Bear in mind this requires a specific kind of error handling. By default the ErrorActionPreference is set to Continue, and this has implications on how errors bubble up to the SQL Server Job Server. If you run a Windows PowerShell command as a SQL Server Agent job and there are no syntax errors but the command produces an error, the SQL Server Agent job will report success. If you want an error condition to halt execution of a SQL Server Agent job or to produce an error, you'll need to add some error handling. To bubble up Windows PowerShell errors to SQL Server Agent, you'll need to set your $ErrorActionPreference = "Stop". You can check that out here.
For the processing task which are a hybrid of data manipulation and other external system related tasks I’d suggest the development of Powershell scripts that use SQL Server Management Objects (SMO) which is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.
Sure, you might say that this suggestion doesn’t help from learning to code, but this is an inevitable goal every SQL Server DBA must have as the future might have projects where the projects might have a server core platform. What then?
So why not kill two rabbits with one stone and learn your Powershell and SMO, it quite seems  the way to go. For starters, this might help.

By coincidence, as I'm writing this post, this editorial from SQLServer Central came out. What are the odds of that?

Happy coding!


Photo Credit: baboon™ via Compfight cc

Microsoft Press has released Rethinking Enterprise Storage: A Hybrid Cloud Model, by Marc Farley, as a free download. You can download the PDF version of this book here.

The book includes seven chapters:

1. Rethinking enterprise storage
2. Leapfrogging backup with cloud snapshots
3. Accelerating and broadening disaster recovery protection
4. Taming the capacity monster
5. Archiving data with the hybrid cloud
6. Putting all the pieces together
7. Imagining the possibilities with hybrid cloud storage

If you prefer the kindle edition of the book, you can find it here: Rethinking Enterprise Storage: A Hybrid Cloud Model

Earlier this year I posted about modern.IE, a tool meant to make it easier to test sites for Internet Explorer.
About this last one, it comes from BrowserStack and it’s a really easy and useful tool to quickly assess the look of your site. Running it on this site gives the following output:

You can check out the live output here: http://www.browserstack.com/screenshots/5a6283df1308512082589290b4add4dcd6b9b35f
To run the tool on any site of your liking, visit BrowserStack.

Trying to install Virtualbox on my latest Windows 8 machine, the result was always the same, a screen like the one below:

The error message is irrelevant, if you google for it you’ll get mostly useless rants. The cause for my problem as well as the solution was to enable hardware virtualization support on the BIOS setup:

Hope this helps someone.

On a daily basis, developers use versioning control software like Microsoft's Team Foundation Server. A problem that arises from time to time is a pending checkin from a user that has left the company, or worse yet, a computer that is missing either because it was damaged or taken away.
On TFS, to solve these issues, you can use the TF.EXE command line tool. To use it you should open a command prompt window:

After opening the command prompt, you need to have identified the following information:

• Name of the computer where the workspace used to be hosted;
• Windows username used to logon to Team Foundation Server;
• The TFS Web URL

While searching for documentation about how to install, configure and administer the latest Microsoft Sharepoint released, I found two very relevant documents that combined give a really good work basis for deploying a fresh install of a server farm or maybe just a standalone development server.
First came the Deployment guide for SharePoint 2013 Preview. It’s the preview version but it has useful information on, for example, how to use a network share to install the software prerequisites. It looks a bit like a work in progress documentation, but it has some bits and pieces that are gone from the definitive guide.
Second, came the definitive Deployment guide for SharePoint 2013. It’s an official free eBook to learn how to install and configure SharePoint Server and SharePoint Foundation. It comes in several formats and it’s a downloadable source of the content available at the TechNet Sharepoint Library.
The first guide is really nice if you’re an experienced administrator that comes from the 2010 version, but lacks some specific guidance. The second one is a complete guide and covers the operation and maintenance aspects very well.
Download the Deployment guide for SharePoint 2013 Preview here.
Download the Deployment guide for SharePoint 2013 here.

Advanced Persistent threats are a hot security topic lately. According to Wikipedia, APT usually refers to a group, with both the capability and the intent to persistently and effectively target a specific entity. The term is commonly used to refer to cyber threats, in particular that of Internet-enabled espionage using a variety of intelligence gathering techniques to access sensitive information.
A modern attack performed by APT is the “Pass the Hash”. According to a whitepaper from Microsoft, while performing a PtH attack, an attacker obtains elevated read/write permission to privileged areas of volatile memory and file systems, which are normally only accessible by system-level processes on at least one computer. Second, the attacker attempts to increase access to other computers on the network by:

1. Stealing one or more authentication credentials (user name and password or password hash belonging to other accounts) from the compromised computer.
2. Reusing the stolen credentials to access other computer systems and services.
   This sequence is often repeated multiple times during an actual attack to progressively increase the level of access that an attacker has to an environment.

Returning to the subject of this post, while going through Microsoft’s community information, I gathered quite interesting resources regarding security tools and guidance.
Regarding free assessment tools from Microsoft, as well as protection software, there’s a really nice compilation on the “Irish IT Professional” technet blog. You get to know tools like Microsoft Security Compliance Manager, Microsoft Baseline Security Analyzer and Microsoft Security Assessment Tool.
Regarding security tools for footprinting, internal auditing anf guidance, you should check out the Security Tools Community Edition page, on Microsoft’s Technet Wiki.
Finally, you might want to follow the Microsoft Security Blog.
Photo Credit: JustEvents via Compfight cc

While installing a third party software on my laptop running Microsoft Windows 8, the operating system informed me that it required to download and install the .NET Framework 3.5. After my consent, it failed to download the package to install. I thought nothing of it and left the task for later.

However, this began to be a recurring episode. Even the very .NET Framework setup program would fail. While installing Windows Live Essentials (I really love Live Writer), the error happened again:

The error code 0x800F0906 wasn’t something I knew about and the support link is an awful article which suggest an over the top work around.

So I tried some common sense and analogy. If you’re used to administering Windows 2008 Servers you should know that the Microsoft .NET 3.5 Framework is installed from the feature selection on the add/remove role operation for the Application Server. Windows 7 itself has features that are essential for system administration like Telnet, and yet you have to add them manually.

With these aspects in mind I went to the Windows 8 Start screen and typed “Features”. Afterwards I selected “Settings” in the search bar and the desired option appeared, called “Programs and Features”:

From the list, you just choose the .NET Framework 3.5 and choose to download from Windows Update:

Now just wait for the installation to complete:

I was all set to go. You should be too .

Today Microsoft announced a new set of tools to help you support modern and older versions of Internet Explorer.

The site makes available a scanning tool that, provided a URL, detects common coding practices that may cause compatibility problems or prevent your users from getting the best possible experience on a webpage rendered on IE. For instance, I ran the scanner on this blog and the report was as follows:

A common problem from supporting old versions of IE was detected. The scanner found that the blog webpage was currently rendering in a Compatibility Mode on Internet Explorer 9 and 10, due to the following tag:

<meta http-equiv="x-ua-compatible"
      content="ie=emulateie7">

Other “nice to have” issues were detected like a lack of responsive design and no Windows 8 features like a touch browsing interface or a live tile for the start screen.

After removing the compatibility tag, the common problems were all fixed.

Another interesting part of this site is that virtual machines are made available for both Mac and Linux developers to download and be able to test their Web applications on Internet Explorer, using various versions of the windows operating system and the browser:

Finally I leave you with the insights from two IT professionals about how challenging it is to develop web application given the number of browser and client devices that exist:

Modern IE Homepage: http://www.modern.ie/

For all the Microsoft Office enthusiasts, here are the top 10 compilations regarding last year:

• Your top 10 favorite Excel posts of 2012
• Your top 10 favorite Word posts of 2012
• Your top 10 favorite Outlook posts of 2012
• Your top 10 favorite PowerPoint posts of 2012

Enjoy!

A free guide is available on MSDN patterns & practices section, demonstrating how you can create from scratch a multi-tenant, software as a service (SaaS) application to run in the cloud.
This guide is the third release of the second volume in a series about Windows Azure. It demonstrates how you can create from scratch a multitenant, Software as a Service (SaaS) application to run in the cloud by using the latest versions of the Windows Azure tools and the increasing range of capabilities of Windows Azure.

The guide is intended for any architect, developer, or information technology (IT) professional who designs, builds, or operates applications and services that are appropriate for the cloud. It is primarily written for people who work with Windows-based systems. You should be familiar with the Microsoft .NET Framework, Microsoft Visual Studio, ASP.NET, SQL Server and Microsoft Visual C#.
You can check ou the guide at this MSDN page.

Some weeks ago I published about the free preview of  Introducing Windows 8: An Overview for IT Professionals. Well, the final version of this free ebook has been released. You can download it from here.
If you prefer a hard copy of the final book, you can order it here for $8.46 from Amazon (pre-order price).

Is this book for you?

Get a headstart evaluating Window 8—guided by a Windows expert who’s worked extensively with the software since the preview releases. Based on final, release-to-manufacturing (RTM) software, this book introduces new features and capabilities, with scenario-based insights demonstrating how to plan for, implement, and maintain Windows 8 in an enterprise environment. Get the high-level information you need to begin preparing your deployment now.

Topics include:

• Performance, reliability, and security features
• Deployment options
• Windows Assessment and Deployment Kit
• Windows PowerShell™ 3.0 and Group Policy
• Managing and sideloading apps
• Internet Explorer® 10
• Virtualization, Client Hyper-V, and Microsoft Desktop Optimization Pack
• Recovery features

Source: Microsoft Press

If you’re beginning to use the new Microsoft Office 2013 and your native language isn’t English, you’ll surely want the new proofing tools.

You can download them from this page at Office Online. Afterwards, install it and restart any open applications from the Office suite.

You’re all set to go.

After migrating my laptop to Windows 8 RTM, I installed Cisco VPN client software. All went well but when trying to connect to my workplace VPN, I got the error “Failed to enable virtual adapter”.

The problem is that, for some unknown reason, the adapter’s name get’s garbled on your windows registry.

The solution lies on the following steps:

1. Open Registry editor by typingregedit in Run prompt
2. Browse to the Registry Key: HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Services\\CVirtA
3. Select the DisplayName to modify, and remove the leading characters from the value data up to "%;" i.e. for x64, change the value data from something like " @oem8 .inf,%CVirtA_Desc%;Cisco Systems VPN Adapter for 64-bit Windows” to "Cisco Systems VPN Adapter for 64-bit Windows”
4. Try connecting again

Good luck!

Web Platform Docs claims itself to be a new community-driven site that aims to become a comprehensive and authoritative source for web developer documentation. This is not an unusual effort, to bring together on one place all the information needed by Web developers for creating application that use open standards web technology. But never has it been done before with stewards that are none the less than the top businesses and companies in this industry. The Web Platform Docs has put together, among others, Google, Microsoft, Mozilla and Apple.
On their site there are numerous helpful tutorials and articles about open Web technologies. To find  out more about this collaboration platform, you can watch the video below:

The greater good that this effort brings is that finally there’s a place to find unbiased documentation for web development regardless of brand, browser or platform.
Check it out!

Microsoft SQL Server comes with a great tool for database administrators and developers alike, called Management Studio. SQL Server Management Studio is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SQL Server Management Studio combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server to developers and administrators of all skill levels.
But like most database management tools, it lacks a more automated support for some recurrent tasks that should be easier to perform.A developer will certainly wish for automatic generation of CRUD statements for a given database table. And a database administrator would surely like to run a maintenance script on all databases where it should be executed, all servers at once.
If these kind of enhancements are on your wishlist, be sure to check out the SSMS Tool pack. It’s a free add-in for SQL Server Management Studio with a great list of enhancements:

• Execution Plan Analyzer
• SQL Snippets
• Window Connection Coloring
• Tab Sessions, Window Content History, Query Execution History and Current Window History
• Format SQL
• Search Table, View or Database Data
• Run one script on multiple databases
• Copy execution plan bitmaps to clipboard or file
• Search Results in Grid Mode
• Generate Insert statements from resultsets, tables or databases
• Regions and Debug sections
• Running custom scripts from Object Explorer
• CRUD (Create, Read, Update, Delete) stored procedure generation
• New query template

You can download it from here.