Clustering every SQL Server instance
Updated: 2012-02-11 03:32:00
You may disagree, but I believe it is a good practice to cluster all the SQL Server instances. That is, even when you plan to run a SQL Server instance on a single machine, you should install it in a single node cluster. The primary advantage is that you only need a single standard SQL Server build instead of one for the stand alone and one for the clustered. This results in simplified configurations such as when you configure network aliases, Kerberos, and multiple instances. If you need to add...(read more)
3 Big Changes in Analysis Services 2012 Enabling Flexible Design
Updated: 2012-02-11 01:32:00SQL SERVER – Solution – A Quick Puzzle on JOIN and NULL – SQL Brain Teaser
Updated: 2012-02-11 01:30:19
Yesterday was really fun. I asked a simple Brain Teaser and we had excellent conversation on SQLAuthority Page as well SQL SERVER – A Quick Puzzle on JOIN and NULL – SQL Brain Teaser. That was an easy puzzle for those who have attended the SQL Server Questions and Answers online course. Here is a quick recap of the puzzle. Lots [...]SQL Saturday 108 Redmond PowerShell Session Material
Updated: 2012-02-10 21:43:24Rick Review: Day 3 Keynote PASS Summit 2011 - Big Data-What's the Big Deal?
Updated: 2012-02-10 13:40:00SQL SERVER – A Quick Puzzle on JOIN and NULL – SQL Brain Teaser
Updated: 2012-02-10 01:30:26
It seems that we all love to solve puzzles. On SQLAuthority Page, we have been playing the number game and those who are playing with us know how much fun we are having. Sometimes, the answers are so innovative and informative that they open up those aspects of the technology which I have not thought [...]Rick Review: Day 3 Keynote of the 2008 PASS Community Summit
Updated: 2012-02-09 19:04:00Speaking at Lisbon NetPonto UG on 02/13/2012
Updated: 2012-02-09 15:48:12PBM for SSIS? What policies would you want?
Updated: 2012-02-09 13:50:00SQL SERVER – INNER JOIN Returning More Records than Exists in Table
Updated: 2012-02-09 01:30:29
I blog and engage with the community because it gives me satisfaction when someone resolves an issue. A few days ago, I blogged about a DBA who began his first day at a new company and could not find out where the installation summary file was. He was very happy when I featured his story [...]SSIS Snack: Name Those Connections!
Updated: 2012-02-09 00:00:00Avoiding nested transactions might not improve performance.
Updated: 2012-02-08 18:09:00[Shameless Plug] Can We Have Your Vote?
Updated: 2012-02-08 17:34:00SQLBits now publishing all SQLBits agendas as an iCalendar
Updated: 2012-02-08 08:36:00SQL SERVER – Convert Subquery to CTE – SQL in Sixty Seconds #001 – Video
Updated: 2012-02-08 01:30:58
SQL Server is an ocean of information. I believe if one starts learning today, after 60 years he/she may still be learning the subject (there are always a few exceptions)! Recently, I published the SQL Server Questions and Answers video tutorial, and since the course came out, I have been receiving lots of request to [...]SSIS Package Configurations MicroTraining Recording Available!
Updated: 2012-02-08 00:00:00defeasible asynchronous oracle database operations
Updated: 2012-02-07 15:41:00Run database updates and selects in a separate threadApplication Lifecycle Management Overview for Windows Azure
Updated: 2012-02-07 14:58:39SQL SERVER – Installation Log Summary File Location – 2012 – 2008 R2
Updated: 2012-02-07 01:30:41Here is email received from user: “Pinal, I am new DBA in my organization and I have to manage SQL Server 2005, 2008 and 2008 R2. Today is my first day at job and my manager has asked me to install all these different edition on our test environment. I have finished installing them. Later [...]Memory Settings in #Tabular #ssas (and Prepare for #msteched!)
Updated: 2012-02-06 21:57:07Upgrading to SQL Server 2012 with Lock Pages in Memory
Updated: 2012-02-06 20:36:00
During SQL Server 2012 RC0 setup (specifically when upgrading), you may have noticed upgrade rules regarding Lock Pages in Memory (LPIM): However, for most folks, these rules always pass, whether or not they are actually using LPIM. I wanted to run a few tests to demonstrate why this is - or at least in which situations the rule checks will fail. So I created two virtual machines running SQL Server 2008 R2 SP1 CU3 - one running Windows Server 2008 SP2 (x86), the other running Windows Server 2008...(read more)SQL SERVER – ERROR: FIX – Database diagram support objects cannot be installed because this database does not have a valid owner
Updated: 2012-02-06 01:30:16
Recently one of my friend sent me email that he is having some problem with his very small database. We talked for few minutes and we agreed that to further investigation I will need access to the whole database. As the database was very big he dropped it in common location (you can use livemesh [...]To Snark or Not to Snark…
Updated: 2012-02-06 00:00:00SQLAuthority News – Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery
Updated: 2012-02-05 01:30:33SQL Server 2012 is has very exciting new feature of SQL Server AlwaysOn. This new feature reduces planned and unplanned downtime and maximize application available. Additionally it provides data protection keeping database always available. Microsoft has released a whitepaper on this subject where it discusses common context business stakeholders, technical decision makers, system architects, infrastructure [...]SQL SERVER – Finding Count of Logical CPU using T-SQL Script – Identify Virtual Processors
Updated: 2012-02-04 01:30:03I recently received email from one of my very close friend from California. His question was very interesting. He wanted to know how many virtual processors are there available for SQL Server. He already had script for SQL Server 2008 but was mainly looking for SQL Server 2000. He made me go to my past. [...]SQLAuthority News – An Incredible Successful SQL Saturday #116 Event – First SQL Saturday in India
Updated: 2012-02-03 01:30:20
We have recently wrapped up our most recent event, SQL Saturday #116, and I am I am sure I am not alone in reporting that it was a huge success! We had a full crowd – every seat taken, plus standing-room-only in the back. We also had a lot of good feedback and the crowd [...]Automated Script-generation with Powershell and SMO
Updated: 2012-02-03 00:00:00In the first of a series of articles on automating the process of building, modifying and copying SQL Server databases, Phil Factor demonstrates how one can generate TSQL scripts for databases, selected database objects, or table contents from PowerShell and SMO.SQL SERVER – An Inspiring Personal Story – Movie from The Book – Video Course – SQL Server Questions and Answers – Pluralsight
Updated: 2012-02-02 01:30:36
Nov 3, 2011 – Visit to Grandma When our SQL Server Interview Questions and Answers book got published I ran to my grandma with a copy of the book for her blessings. Well, just like every grandma, she loves me, her grandson, unconditionally. She is not into the technology domain (obviously), but she loved the book. She read the [...]Using SQL Test Database Unit Testing with TeamCity Continuous Integration
Updated: 2012-02-02 00:00:00With database applications, the process of test and integration can be frustratingly slow because so much of it is based on manual processes. Everyone seems to agree that automation of the process provides the answer to accomodating shorter development cycles, but how, exactly? Dave Green describes a successful process that integrates third-party tools.SQL Server 2012 Early Adoption Cook Book
Updated: 2012-02-01 22:28:00Using XACT_ABORT ON may be faster than using TRY...CATCH
Updated: 2012-02-01 22:25:00Wrapping related changes in a transaction may use less CPU.
Updated: 2012-02-01 22:02:00DTLoggedExec 1.1.2008.4 SP2 released!
Updated: 2012-02-01 17:15:44SQL SERVER – What is Slowly Changing Dimension – Quiz – Puzzle – 31 of 31
Updated: 2012-02-01 01:30:23
Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to [...]Converting String Data to XML and XML to String Data
Updated: 2012-02-01 00:00:00We all appreciate that, in general, XML documents or fragments are held in strings as text markup. In SQL Server, XML variables and columns are instead tokenised to allow rapid access to the data within. This is fine, but can cause some odd problems, auch as 'entitization'. What, also, do you do if you need to preserve the formatting? As usual Rob Sheldon comes to our aid.SSIS MicroTraining: SSIS Package Configurations
Updated: 2012-02-01 00:00:00SQL SERVER – Advantages of Partitioning – Quiz – Puzzle – 30 of 31
Updated: 2012-01-31 01:30:43Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to [...]Performance impact: the cost of NUMA remote memory access
Updated: 2012-01-30 23:17:20
These days if you get a new server-class machine to run SQL Server, you can almost be 100% sure that it’ll be running on NUMA hardware. The recent AMD Opteron and Intel Nehalem-based processors are all built on some form of NUMA architecture. The current consensus is that as the number of processors grows, their shared memory bus can easily get congested and becomes a major impediment to scalability. NUMA hardware solves this scalability challenge by dividing the processors into groups, with each...(read more)OT: A Good Reason to Attend #SQLBits
Updated: 2012-01-30 08:41:46
My 4yo son keeps asking me why I should leave home so often to attend SQL conferences around the world and today, after a couple of days of snow, he discovered a good reason, along with the best place for the SQLBits scarf....(read more)SQL SERVER – Data Collector Usage – Quiz – Puzzle – 29 of 31
Updated: 2012-01-30 01:30:02Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to [...]How to Document and Configure SQL Server Instance Settings
Updated: 2012-01-30 00:00:00Occasionally, when you install identical databases on two different SQL Server instances, they will behave in surprisingly different ways. Why? Most likely, it is down to different configuration settings. There are around seventy of these settings and the DBA needs to be aware of the effect that many of them have. Brad McGehee explains them all in enough detail to help with most common configuration problems, and suggests some best practices.What's the Point of Using VARCHAR(n) Anymore?
Updated: 2012-01-30 00:00:00The arrival of the (MAX) data types in SQL Server 2005 were one of the most popular feature for the database developer. At the time, there was a lot of discussion as to whether this freedom from having to specify string length came at a cost. Rob attempts to give a final answer as to any down-side.Suggested Best Practises and naming conventions
Updated: 2012-01-29 18:08:53SQL SERVER – Reclaiming Space Back from Database – Quiz – Puzzle – 28 of 31
Updated: 2012-01-29 01:30:36Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to [...]SQL SERVER – Lots of Date Functions – Find Right One to Use – Quiz – Puzzle – 27 of 31
Updated: 2012-01-28 01:30:43Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to [...]SQL SERVER – Common Gotcha’s Associated with Common Table Expressions (CTE) – Quiz – Puzzle – 26 of 31
Updated: 2012-01-27 01:30:27Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to [...]SQLAuthority News – Interview with Book Authors after 2 Months of Book Released
Updated: 2012-01-26 01:30:39Community is the most motivating force for me. I have often found situations where I have done more and better things because there was community around me. My latest book SQL Server Interview Questions and Answers is the result of the community’s support and love. Without the wide acceptance of the community I would have [...]SQL SERVER – Different Aspect of Policy Based Management – Quiz – Puzzle – 25 of 31
Updated: 2012-01-26 01:30:33Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to [...]SQL SERVER – Correct Value for Fillfactor – Quiz – Puzzle – 24 of 31
Updated: 2012-01-25 01:30:45Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to [...]SQL SERVER – Database Mirroring and Fine-Prints – Quiz – Puzzle – 23 of 31
Updated: 2012-01-24 01:30:41Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to [...]XEvents in SQL Server 2012: No more "lazy XML" in event harvesting scripts
Updated: 2012-01-23 20:49:00: Home Syndication Log in XEvents in SQL Server 2012 : No more lazy XML in event harvesting scripts I've said before how thrilled I am will the new extended event UI in SSMS for SQL Server 2012. However , you might be one of the early adopters who made up their own scripts to define extended event sessions , and use hardcoded scripts to harvest the results . So , you may run into this problem if you're using what I call lazy XML in the event harvesting script . Take , as an example , an extended event session defined as follows with 3 actions : create event session errorsession on server add event sqlserver.error_reported action package0.callstack , nbsp sqlserver.session_id , nbsp sqlserver.sql_text where error 547 and package0.counter add target package0.ring_buffer withSQL SERVER – What is Piecemeal Restore – Quiz – Puzzle – 22 of 31
Updated: 2012-01-23 01:30:49Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to [...]SQL SERVER – Difference between Create Index – Drop Index – Rebuild Index – Quiz – Puzzle – 21 of 31
Updated: 2012-01-22 01:30:58Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to [...]SQL SERVER – Methods for Accessing SQL Server XML Datatype – Quiz – Puzzle – 20 of 31
Updated: 2012-01-21 01:30:15Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to [...]SQL SERVER – MERGE or INSERT, UPDATE, DELETE – Quiz – Puzzle – 19 of 31
Updated: 2012-01-20 01:30:31Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to [...]SQL SERVER – Importance of Resource Database – Quiz – Puzzle – 18 of 31
Updated: 2012-01-19 01:30:20Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to [...]SQL Cop Review
Updated: 2012-01-19 00:00:00Static code analysis is used a lot by application programmers, but there have been surprisingly few tools for SQL development that perform a function analogous to Resharper, dotTest, or CodeRush. Wouldn't it be great to have something that can indicate where there are code-smells, lapses from best practice and so on, in your Database code? Now there is.Great SQL Server Debates: Buffer Cache Hit Ratio
Updated: 2012-01-18 00:00:00One of the more popular counters used by DBAs to monitor the performance, the Buffer Cache Hit Ratio, is useless as a predictor of imminent performance problems. Worse, it can be misleading. Jonathan Kehayias demonstrates this convincingly with some simple tests.SQL Server 2012 FileTable and T-SQL on AlwaysOn secondary replicas
Updated: 2012-01-17 20:01:00Home Syndication Log in SQL Server 2012 FileTable and T-SQL on AlwaysOn secondary replicas OK , back to PathName( with AlwaysOn , which I started on in the previous blog post PathName( in SQL Server 2012 doesn't return the computer name by default AlwaysOn1 or AlwaysOn2 in my example but returns the VNN name virtual network name That is , it returns the availability group listener share name . In my case , the PathName( would start with AlwaysOnAG1 There's an additional option in SQL Server 2012 PathName( that allows you to return the current replica name . So , when AlwaysOn1 is the current primary replica , it returns that when we fail over to AlwaysOn2 that's what name is returned . In addition , GetFileNamespacePath( and FileTableRootPath( always return the availability group listenerSQL Server 2012 FileTables - AlwaysOn support and PathName()
Updated: 2012-01-17 18:03:00Home Syndication Log in SQL Server 2012 FileTable and T-SQL on AlwaysOn secondary replicas SQL Server 2012 FileTables AlwaysOn support and PathName( When I was covering the FileTable-specific functions and methods , I didn't mention PathName( a filestream-related function , that naturally can be used with FileTables . This posting is about PathName( but , mostly about what happens with FileTable in an AlwaysOn availability group configuration . There were some surprises there . None of the FileTable-specific functions and methods mention AlwaysOn , but PathName( mentions it . There is an additional parameter you can specify that matters if you use PathName( in an AlwaysOn availability group environment . Remember that , in addition to FileTable , there have been enhancements to filestreamRelational Databases and Solid State Memory: An Opportunity Squandered?
Updated: 2012-01-17 00:00:00The relational model was devised long before computer hardware was able to deliver an RDBMS that could deliver a fully normalized database with no performance deficit. Now, with reliable SSDs falling in price, we can reap the benefits, instead of getting distracted by NOSQL with its doubtful compromise of 'eventual consistency'.SQL Server 2012 FileTables in T-SQL part 3: hierarchyid methods
Updated: 2012-01-16 21:06:00: Home Syndication Log in SQL Server 2012 FileTables in T-SQL part 3 : hierarchyid methods So finally , we get to using FileTable's path_locator column with the methods of hierarchyid . To pick up where we left off remember that hierarchyid has a method named GetLevel( Given the following directory structure in the FileTable's share : File1.txt File2.txt SQLFiles SubdirFile1.txt SubdirFile2.txt I have a few more levels of subdirectory here , but hopefully , you get the idea . Issuing the query SELECT path_locator.GetLevel( as Level , Name FROM dbo.Documents show , unsurprisingly , File1.txt , File2.txt , and SQLFiles directory at level 1, and the subdirectory files at level 2. So to be sure we're getting the right SQLFiles directory , we could change that query in the previous blog entrySQL Server 2012 FileTables in T-SQL part 2: new rows
Updated: 2012-01-16 16:00:00: Home Syndication Log in SQL Server 2012 FileTables in T-SQL part 2 : new rows So the functions methods that I wrote about in previous post are needed because FileTables don't store the UNC path name of the file , they store the path_locator as a hierarchyid data type . Wonder what encoding scheme they're using . Let's see , by doing SELECT path_locator.ToString( Name FROM dbo.Documents We get hierarchyid strings that look like this : 192992825631153.73945086322524.2119705196 Turns out that the encoding scheme involves newid( as you can see by looking at the definition for the default constraint for the path_locator column . It looks like this : convert(hierarchyid , convert(varchar(20 convert(bigint , substring(convert(binary(16 newid( 1, 6 convert(varchar(20 convert(bigint ,SQL Server 2012 FileTables in T-SQL part 1: functions and methods
Updated: 2012-01-16 06:17:00: Home Syndication Log in SQL Server 2012 FileTables in T-SQL part 1 : functions and methods I've been working with the SQL Server 2012 FileTable feature lately . Besides learning to appreciate the esoteric features of the NTFS file system and SMB protocol , only some of which are supported by FileTables , I've been trying to work with FileTables in SQL Server using T-SQL . This turns out to be an interesting exercise , especially if you're trying to brush up on your skills with the hierarchyid data type . It turns out that a FileTable is just like a normal SQL Server table with a filestream varbinary(max column named , unsurprisingly , file_stream It uses computed columns and constraints rather extensively . Behind the scenes , SQL Server functions as a Win32 namespace owner and exposes a