SQLBlog.com - Wed, 22 Feb 2012 15:42
I'm not one to whine. Really. I'm totally not a whiner. However, I'm going to sound like one with this statement... I fricken HATE iTunes. There, I said it. I'm already starting to feel better. Playing on Elisebeth Barrett Browning and her fantastic poem, "How do I love thee? Let me count the ways." , I'm going to count some ways that iTunes is filling me with inhuman, Hulk-like rage: Ridiculously frequent updates . Not the "Update Tuesday" sort of thing we get from Microsoft, but the "I'm going...(read more)
SQLBlog.com - Wed, 22 Feb 2012 12:31
I recently wrote an article about how to implement a Process Add on a table in a Tabular model. This is an area where there is a lack of documentation by Microsoft, especially if you want to use a custom binding query for every Process Add command, which should be the common case for a Process Add scenario. Cathy Dumas wrote several blog posts about this argument and I tried to put everything together, showing the same example written in XMLA Script, AMO and PowerShell. You can also use Integration Services, but considering you probably want to customize the binding query, chances are that you will opt for a more programmatic approach, maybe embedding an XMLA Script or an AMO Script in a standard Task in SSIS. The next step for me will be studying how much “near real time” a Tabular
SQLBlog.com - Wed, 22 Feb 2012 09:00
Over the past year or so I have written at length about the SSIS Catalog that will be provided in the upcoming version of SQL Server Integration Services (SSIS 2012) and the capabilities that it will bring; one of the biggest benefits (in my opinion) is that logging is now something that is taken care of on your behalf. Many SSIS developers have built their own logging solutions (often referred to as "frameworks") over the past few years and it occurred to me that many of those developers may have a need to carry on running their own logging solutions alongside that which the SSIS Catalog provides. To that end I have raised two Connect submissions requesting enhancements to SSIS that will make it easier for developers to do just that:
SQLBlog.com - Wed, 22 Feb 2012 07:11
I took the MCM lab a couple of weeks ago and passed. I still don’t believe it - it’s been quite the journey. There have been many decisive moments in my career; one of those was back in 2010 when I decided I would work on obtaining the certification after Brent Ozar blogged rather extensively about his experience with the program. I progressively worked on aspects of SQL Server that were out of my comfort zone – and that paid off. But simply put, I have the cert - but still don’t consider myself a true Master. And I probably never will. I might be okay in certain areas of SQL, maybe even good in others - but I don’t master them all. I intend on getting better on those areas, but that won’t happen overnight. SQL Server is a mammoth of a product. It’s actually a suite of products – and the MCM certification focuses on only one aspect of it: the Database Engine. No SSAS, no SSIS, no SSRS. So you might be wondering: Is it worth it then? W
sqlservercentral.com - Wed, 22 Feb 2012 07:00
Examines the scale-out capabilities of SQL Server Agent, using the MSX/TSX subsystem. This capability allows you to run jobs on multiple systems and consolidate the job results/history onto a single server.
A list of SQL Server 2008 R2 Data Types and Storage Sizes.
Companies often need to generate reports and forms from scanned images and various government supplied formats. Proper knowledge of how to incorporate images in a report is a must.
We’d like to find out about who handles SQL Server database storage at your organization. Enter our quick survey now for the chance to win one of three $50 Amazon vouchers. The Future of SQL Server Monitoring "Being web-based, SQL Monitor enables you to check on your servers from almost any location" Jonathan Allen.Try SQL Monito
Journey to SQLAuthority - Wed, 22 Feb 2012 01:30
We got tremendous response to our very first video of SQL in Sixty Seconds #001 and SQL in Sixty Seconds #002. We talked about how to convert Subquery to CTE and Error and Reaction. My co-authors Vinod Kumar and Rick Morelan, we often came across very interesting and useful tips which we believe would be helpful to [...]
SQLBlog.com - Tue, 21 Feb 2012 13:24
I recently joined Chris Webb, Jen Stirrup and Alberto Ferrari as PASS Data Warehouse and Business Intelligence Virtual Chapter for Europe. We will increase the number of virtual sessions delivered in a comfortable European time zone and we are looking for speakers interested in sharing their knowledge in data warehouse and business intelligence with the Microsoft SQL Server Platform. If you are interested, please write to PASSDWBIVC@sqlpass.org and present yourself with an abstract and a title for your session. Also mention your previous experience as a speaker – this will help us in understanding what ty
sqlservercentral.com - Tue, 21 Feb 2012 07:00
This article shows you the process which you require to regain your sysadmin access.
Keeping Developers in the Proper Frame of Mind The Future of SQL Server Monitoring "Being web-based, SQL Monitor enables you to check on your servers from almost any location" Jonathan Allen.Try SQL Monitor now.
We all understand the value of Unit Testing, but how come so few organisations maintain unit tests for their in-house applications? We can no longer pretend that unit testing is a universal panacea for ensuring less-buggy applications. Instead, we should be prepared to actively justify the use of unit tests, and be more savvy about where in the development cycle the unit test resources should be most effectively used.
Journey to SQLAuthority - Tue, 21 Feb 2012 01:30
Yesterday I wrote a blog post discussing how guest user can become security threat. The script which was demonstrated for the example had a small T-SQL query which creates a new user. Later, I got an email from a user who had created this scenario on his production environment. It makes me sad that I [...]
SQLBlog.com - Mon, 20 Feb 2012 21:16
(As with all of these types of posts, check the date of the latest update I’ve made here. Anything older than 6 months is probably out of date, given the speed with which we release new features into Windows and SQL Azure) I don’t normally like to discuss things in terms of tools. I find that whenever you start with a given tool (or even a tool stack) it’s too easy to fit the problem to the tool(s), rather than the other way around as it should be. That being said, it’s often useful to have an example to work through to better understand a concept. But like many ideas in Computer Science, “Big Data” is too broad a term in use to show a single example that brings out the multiple processes, use-cases and patterns you can use it for. So we turn to a description of the tools you can use to analyze large data sets. “Big Data” is a term used lately to describe data sets that
sqlservercentral.com - Mon, 20 Feb 2012 07:00
Three CLR-resident functions for 16-, 32-, and 64-bit FNV1a hashing Get Smart with SQL Backup Pro Powerful centralised management, encryption and more.SQL Backup Pro was the smartest kid at school Discover why.
The challenge is to identify the sequence of accounting transactions.
The majority of companies that suffer a major data loss subsequently go out of business. Wesley David remembers vividly the day when the organisation he worked for found that they couldn't restore their data, and the subsequent struggles that ensued. Shoulda-woulda-coulda.
Come to a free day of SQL Server training on Feb 25, 2012 in Redmond, WA.
Journey to SQLAuthority - Mon, 20 Feb 2012 01:30
“No Guests PLEASE!” “Doesn’t your Indian tradition suggest welcoming guests and treating them in the best way possible?” “Yes, but I am talking about the Guest user in SQL Server.” “Oh!” This was a real conversation that happened a couple of years ago. I welcome guests as much as any other Indian does; however, I am strongly opinionated about [...]
SQLBlog.com - Mon, 20 Feb 2012 01:05
Overall, SQL Server has become a very capable and mature product, with a very powerful engine and sophisticated query optimizer. Still, every now and then, a certain query structure throws the optimizer for a loop resulting in an execution plan that will take forever. The key to identifying this type of problem begins with the exeuction plan. First, the plan cost does not tell the whole story. It is necessary to know which execution plan operations can run well on modern server systems and which...(read more)
SQLBlog.com - Sun, 19 Feb 2012 11:09
I think I can say that we all agree that SQL Server 2012 brings with revolution in our database world, since the number of new feature and new possibilities, IMHO, is comparable to what SQL Server 2005 brought several years ago. Interesting enough, even the well-known IDC firm thinks that SQL Server 2012 can be a game changer. From a technical point of view the highlighted technologies are: AlwaysOn ColumnStore Index Windows Server Core Support Power View BI Sematic Model Data Quality Services Hadoop Support Cloud & On-Premise Integration Support for PHP, Java and Linux The full document (less than 10 pages) is very interesting – definitely worth reading – and can be extremely helpful to bring the awareness of what SQL Server 2012 can offer and how it can help business, also to non technical people:
Journey to SQLAuthority - Sun, 19 Feb 2012 01:30
Migration is always the challenge, it does not matter if people are migrating from one country to another country or birds are migrating from one continent to another continent or database is migrating from one platform to another platform. I remember years ago when I had to migrate our database from another platform to SQL Server, [...]
SQLBlog.com - Sat, 18 Feb 2012 01:50
Lately I've been very busy with client work, and one of my projects is to capture the history for SQL Agent jobs across all their servers so they can evaluate when to schedule jobs. (Yes, I know SQL Sentry has an excellent product for this, but right now there are budget constraints.) Anyway, I can do this in PowerShell with SMO. Once you connect to a server in SMO, the JobServer object has a collection called Jobs. For each job in that collection, you can call the EnumHistory method and it will...(read more)
Journey to SQLAuthority - Sat, 18 Feb 2012 01:30
Jeff asked me another question! If you do not know Jeff, you may read the following blog posts. You will get the idea of Jeff’s personality and who Jeff really is. SQL SERVER – Installation Log Summary File Location – 2012 – 2008 R2 SQL SERVER – INNER JOIN Returning More Records than Exists in [...]
SQLBlog.com - Fri, 17 Feb 2012 23:37
The ever alert Jonathan Kehayias (Blog | Twitter) sent me a question recently asking about the Extended Event UI used for showing predicates. In particular, he was wondering about the predicate for the wait_info event that is defined in the system_health session and was wondering what was going on.
sqlservercentral.com - Fri, 17 Feb 2012 07:00
Options and impacts when creating, altering and dropping an index
This article by Jonathan Roberts demonstrates how to use dynamic SQL and overcome its downsides.
SQL Saturday is hitting Dublin for the first time on March 24th.
Journey to SQLAuthority - Fri, 17 Feb 2012 01:30
Some questions are timeless and they never grow old; no matter how much they grow old their interest never dies. Earlier, I asked a simple puzzle based on a conversation on SQLAuthority Page, and have received an overwhelming response from readers. I still get emails related to this puzzle every day. Before we move on, I suggest you [...]
SQLBlog.com - Thu, 16 Feb 2012 21:32
Hooray – we’re ready to announce the details of the 24 Hours of PASS event that’s coming to an internet connection near you on March 21st! Read on for the day’s schedule, some of my thoughts on the 24 Hours of PASS concept, information about the platform, and an announcement which I think is really quite a big deal and worth making a fuss over. 24 Hours Straight So – March 21st. No overlap with any other days, just 24 Hours of PASS, squeezed back into a single day. The last few events have been split over two days, running from noon to midnight (GMT), which conveniently fits in daylight hours of the Eastern United States. To help with the current push for internationalisation (and there’s more on that later in this post), we’re starting at midnight GMT (which is a perfectly reasonable 7pm in New York), and running through to the following
SQLBlog.com - Thu, 16 Feb 2012 19:48
Almost two months have passed since my last blog post. And while it’s true that I’ve had (much) longer breaks, I do have a good reason now. All the time that I would normally at least in part spend on preparing new blog posts is now reserved for preparing presentations for a few upcoming events. I’ll give you an overview – who knows, maybe you’ll have a chance to attend one of them and meet me there? I’m looking forward to it! On Saturday, February 25, I’ll present my session on “Advanced Indexing”...(read more)
sqlservercentral.com - Thu, 16 Feb 2012 07:00
When using format files to import data into SQL Server, the Integer(INT) data type needs special handling.
Derek gets away from it all.
Find out what SQL Source Control & SQL Compare can do for your database development and deployment processes at one of our free weekly webinars. Each webinar includes a 30 minute software demonstration and a Q&A session.
On Thursday February 16th at 12PM noon Central, Steve Simon will discuss “No Matter how well planned and executed, data structures sometime resembles something out of a Dr. Seuss book."
Journey to SQLAuthority - Thu, 16 Feb 2012 01:30
This is a true story. My wife and my daughter were playing in the play area in our resident complex. I was sitting a ways off and was watching them play various games. My daughter likes the slides a lot. Suddenly, one kid started to climb the slide from the slide instead of the stairs. [...]
simple-talk.comFeodor Georgiev - Thu, 16 Feb 2012 00:00
When you are using SSIS, there soon comes a time when you are confronted with having to do a tricky task such as searching for particular connection strings in all your SSIS packages, or checking the execution history of scheduled SSIS jobs. You can do this type of work effectively in TSQL as Feodor explains.
SQLBlog.com - Wed, 15 Feb 2012 14:25
Two quick notes from our friends over at INETA : Component Code Challenge Ever had these two distinct thoughts - "I have a good idea for an application, however what can I get for it?" and "I would love to go to a big conference DevConnections or Tech·Ed, but how would I pay for it?". Haven't we all had thoughts like that at some time or another? Well, you are in luck. The INETA Component Code Challenge for 2012 will send one lucky winner to their choice of those aforementioned conferences for slinging...(read more)
SQLBlog.com - Wed, 15 Feb 2012 12:00
I am excited to co-present A Day of SSIS at the Atlanta SQL Saturday #111 13 Apr 2012! The SQL Saturday event will be Saturday 14 Apr 2012, but the precons (there are three!) will be Friday 13 Apr 2012. Matt Masson ( Blog | @mattmasson ) is co-presenting. If you have never had the opportunity to listen to Matt talk about SSIS, you should. Matt is one of the good people at Microsoft who develops and maintains SSIS. I hope to see you there. Register here ! :{>...(read more)
sqlservercentral.com - Wed, 15 Feb 2012 07:00
How maintenance plans are used as SQL Server Agent jobs to automate standard system tasks. This article will also lay out some suggested jobs that should be on virtually all SQL Server systems.
The list of builds for SQL Server 2008 updated SP3.
SQLBlog.com - Wed, 15 Feb 2012 05:17
SQLTeach (Part of the DevTeach Conferences in Canada) has enjoyed success for many years. I enjoyed the opportunity to speak at and attend SQLTeach at various times over the years. Since SQLTeach is part of DevTeach, the "feel" of the event is very different from PASS or SQLRally or even a SQL Saturday where SQL is the primary focus. It does however afford an opportunity for me to understand the challenges of those who need to work with SQL either in a direct or indirect manner. SQLTeach / DevTeach...(read more)
Journey to SQLAuthority - Wed, 15 Feb 2012 01:30
We got tremendous response to our very first video of SQL in Sixty Seconds #001. We talked about how to convert Subquery to CTE very quickly. My co-authors Vinod Kumar and Rick Morelan, we often came across very interesting and useful tips which we believe would be helpful to readers. In today’s SQL in Sixty [...]
SQLBlog.com - Tue, 14 Feb 2012 20:45
There are multiple ways to store data in a cloud provider, specifically around Windows and SQL Azure. As part of a “Data First” architecture design, one decision vector – assuming you’ve already done a data classification of the elements you want to store – is to decide the transaction level you need for that datum. Once you’ve decided on what level of transactional commitment you need, you can make intelligent decisions about the storage engine, method of access and storage, speed and other requirements. Although the list below is neither original nor exhaustive, these are the general considerations I use for a given data set. It’s important to note that in many on premises systems the engine choice at hand overrides these concerns. If you have a large Relational Database Management System (RDBMS) for instance, you might simply place all data there without further consideration. In a Platform as a Service (PaaS) like Windows an
SQLBlog.com - Tue, 14 Feb 2012 12:00
I am honored to present “Building Your First SSIS Package in SQL Server 2012” at the NJ SQL Server User Group Tuesday, 21 Feb 2012 starting at 6:00 PM! If you read this blog and will be in the area, please stop by and introduce yourself! I’m the fat guy with a fu. :{>...(read more)
sqlservercentral.com - Tue, 14 Feb 2012 07:00
This document shows how to install a Microsoft SQL Server 2008 R2 Reporting Services instance.
There's more than one way to be green.
If you will be in London on Mar 29th, come to the official UK launch of SQL Server 2012 at SQL Bits X. Saturday is sold out, so if you registered and cannot come, please cancel. There are still a few spots for Thur and Fri.
SQLBlog.com - Tue, 14 Feb 2012 05:07
This story didn’t actually take place on Valentine’s Day but it is appropriate to tell on Valentine’s Day. How far would you go for someone you love? When our first born child was a toddler, something made of glass fell to the floor. One of us quickly picked the barefoot boy up off of the floor before he could step on the broken glass. Then the work began. After much sweeping and vacuuming, my wife and I had an uneasy feeling. I opted for a technical solution. I got a flashlight, turned off the lights,...(read more)
Journey to SQLAuthority - Tue, 14 Feb 2012 01:30
Decoding the human genome originally took 10 years to process; now it can be achieved in one week - The Economist. This blog post is written in response to the T-SQL Tuesday post of The Big Data. This is a very interesting subject. Data is growing every single day. I remember my first computer which had [...]
SQLBlog.com - Tue, 14 Feb 2012 00:04
This is not the big announcement that I’ve promised about 24 Hours of PASS (which starts in five weeks from now!) – that’s coming later this week (and for those of you who haven’t picked up on it – 24HOP is the portfolio I’ve been given since becoming an elected director of PASS). The announcement that is coming later in the week will contain the full schedule (which is being finalised as we speak), and wi
SQLBlog.com - Mon, 13 Feb 2012 08:00
I can hardly believe I will be delivering From Zero To SSIS! in London, England 5-9 Mar 2012 – in less than a month! This course is delivered in cooperation with my friends at TechniTrain who provide awesome training by talented technologists like Chris Webb ( Blog ), Gavin Payne ( Blog ), and Christian Bolton ( Blog ). I hope to see you there! :{>...(read more)
sqlservercentral.com - Mon, 13 Feb 2012 07:00
Learn how to create a SQL Server 2012 Active/Active cluster in Hyper-V using an iSCSI SAN
A look at the memory architecture of SQL Server and Oracle, for those of you that may need to provide support for Oracle databases. Get smart with SQL Backup ProPowerful centralised management, encryption and more.SQL Backup Pro was the smartest kid at school.
On Mar 26-29 in Las Vegas, the DevConnections conference is hosting a launch of SQL Server 2012. Come to the conference and learn more at the SQLServerCentral track.
There are around seventy SQL Server instance settings, and a DBA needs to be aware of the effect that many of them have. Brad McGehee explains them in enough detail to help with most common configuration problems, and suggests some best practices.
Journey to SQLAuthority - Mon, 13 Feb 2012 01:30
“I have text files and I need to create interactive reports for my boss – do you have few minutes of time right now? Let us discuss.” I often get questions from people who are new to technology and struggling to get something done. However, this time it was not a question from any beginner. [...]
SQLBlog.com - Sun, 12 Feb 2012 11:58
Over the past few years of using datadude (aka DBPro aka Visual Studio Database Projects) I have fallen prey to a peculiar little nuance – if you forget to supply a value for a sqlcmd variable then it will simply use a default and often that is not the desired behaviour. Hence why yesterday I submitted the following suggestion to http://connect.microsoft.com/sqlserver/feedback : Specify sqlcmdvars properties as "required to be overridden” In my current place of work I am responsible for maintaining our datadude projects and we have another team that is in charge of deployments. Hence, when we place new properties into the sqlcmdvars file I need to tell the deployment team what values to supply for that property per environment (dev, systest, uat, prod). Unfortunately lack of communication/human error occasionally creeps in and,
Journey to SQLAuthority - Sun, 12 Feb 2012 01:30
I am in front of computer 16+ hours of the day. However, I accept that I am bit lazy when it is about doing installation etc. I always prefer that IT department help me to install my computer and I use it right away. Same feeling I get when I have to install Beta, RC [...]
Bob Beauchemin's Blogbobb (bobb) - Sat, 11 Feb 2012 23:44
Back when I was saw SQL Azure was producing an @@version string that mentioned version 11 (that's SQL Server 2012's major version number), I'd started looking at the version number every week or so. And looking for SQL Server 2012 functionality in SQL Azure. See the blog post "SQL Denali T-SQL features in SQL Azure now" for a list of the first functions supported. I'm not going to list them all again here. And Ed Katibah's (a.k.a Spatial Ed) latest list of SQL Server 2012 spatial functionality in SQL Azure. This week, I'd thought to look at the SQL Azure version
SQLBlog.com - Sat, 11 Feb 2012 03:32
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)
SQLBlog.com - Sat, 11 Feb 2012 01:32
The upcoming release of SQL Server 2012 has a lot of new features for business intelligence developers to love. The free preview of Introducing Microsoft SQL Server 2012 (Microsoft Press, 2012) does not include the chapter on Analysis Services, but you’ll be able to read the details when the final version of the ebook is released for download in March.Overall, there are a lot of changes in Analysis Services 2012, and it’s easy to get overwhelmed by the details. So, just as I did for
Journey to SQLAuthority - Sat, 11 Feb 2012 01:30
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 [...]
SQLBlog.com - Fri, 10 Feb 2012 21:43
I am honored to be presenting my first SQL Saturday session in Redmond on the 25th of this month. The session will be a PowerShell basics class, emphasis on helping people who might be starting out with PowerShell, or feel intimidated by PowerShell’s syntax or object-orientation. I have demos and material focused on PowerShell syntax in general, for any task, and then a few demos with a SQL Server slant. The main idea is to get an understanding of how and why PowerShell syntax works, which should...(read more)
Glen Berry's SQL Server Performance - Fri, 10 Feb 2012 15:15
Back during the PASS 2011 Summit in Seattle, I was interviewed by Tony Davis, Andrew Clark, and Rodney Landrum about hardware, storage subsystems and virtualization. This was a fun little impromptu interview, filmed in a somewhat quiet location during the … Continue reading →
SQLBlog.com - Fri, 10 Feb 2012 13:40
I recently did a Rick Review of the Day 3 Keynote from the 2008 PASS Community Summit that featured Dr. DeWitt speaking on new technology that ended up in the Parallel Data Warehouse (PDW) edition of SQL Server 2008 R2. I decided to also listen again to the Day 3 Keynote from the 2001 PASS Community Summit. While I listened intently to the keynote on Day 3 of this past summit, something seemed to be somewhat familiar. Had I heard some of these concepts before? The keynotes from the most recent PASS...(read more)
Journey to SQLAuthority - Fri, 10 Feb 2012 01:30
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 [...]
Journey to SQLAuthority - Thu, 09 Feb 2012 01:30
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 [...]
Journey to SQLAuthority - Wed, 08 Feb 2012 01:30
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 [...]
Journey to SQLAuthority - Tue, 07 Feb 2012 01:30
Here 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 [...]
Glen Berry's SQL Server Performance - Mon, 06 Feb 2012 23:04
Back in April of 2011, I wrote a post that compared the merits of using an ancient, but actual rack mounted server versus a new desktop based system for testing and development work. It is pretty common for old retired … Continue reading →
Journey to SQLAuthority - Mon, 06 Feb 2012 01:30
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 [...]
Journey to SQLAuthority - Sun, 05 Feb 2012 01:30
SQL 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 [...]
Journey to SQLAuthority - Sat, 04 Feb 2012 01:30
I 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. [...]
Glen Berry's SQL Server Performance - Fri, 03 Feb 2012 02:14
AMD had their 2012 Financial Analyst Day today, which is basically a dog and pony show for stock analysts. Previously, AMD had talked about introducing a new G2012 platform in 2012/2013, which would use 10 and 20-core processors called Sepang … Continue reading →
Journey to SQLAuthority - Fri, 03 Feb 2012 01:30
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 [...]
simple-talk.comPhil Factor - Fri, 03 Feb 2012 00:00
In 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.
Journey to SQLAuthority - Thu, 02 Feb 2012 01:30
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 [...]
simple-talk.comDave Green - Thu, 02 Feb 2012 00:00
With 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.
Journey to SQLAuthority - Wed, 01 Feb 2012 01:30
SQL Server Interview Questions and Answers Print Book Available (207 Pages) | Sample Chapters 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 [...]
simple-talk.comRobert Sheldon - Wed, 01 Feb 2012 00:00
We 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.
Journey to SQLAuthority - Tue, 31 Jan 2012 01:30
Journey to SQLAuthority - Mon, 30 Jan 2012 01:30
simple-talk.comRob Garrison - Mon, 30 Jan 2012 00:00
The 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.
simple-talk.comBrad McGehee - Mon, 30 Jan 2012 00:00
Occasionally, 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.
Journey to SQLAuthority - Sun, 29 Jan 2012 01:30
Bob Beauchemin's Blogbobb (bobb) - Mon, 23 Jan 2012 20:49
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, sqlserver.session_id, sqlserver.sql_text ) where error = 547 and package0.counter <= 3 ) add target package0.ring_buffer with (max_dispatch_latency = 1 seconds) go In previous versions, you could pretty much depend on the XML presenting the actions in or
Glen Berry's SQL Server Performance - Mon, 23 Jan 2012 16:04
Since Microsoft recently released a couple of new Cumulative Updates for SQL Server 2008, I thought it was finally time to release a new version of my SQL Server 2008/2008 R2 Diagnostic Information Queries for January 2012. You will need … Continue reading →
simple-talk.comPhil Factor - Mon, 23 Jan 2012 00:00
Over the next few months, we'll be asking various well-known DBAs to describe their worst disaster caused by a mistake they made. To kick off the series, we asked Phil Factor to confess. He came up with a classic: The mistaken belief that a backup WITH CHECKSUM guaranteed a good backup that could be restored, and the ensuing disaster.
simple-talk.comGrant Fritchey - Thu, 19 Jan 2012 00:00
Static 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.
simple-talk.comJonathan Kehayias - Wed, 18 Jan 2012 00:00
One 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.
Bob Beauchemin's Blogbobb (bobb) - Tue, 17 Jan 2012 20:01
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 listener name, they don't have a
Bob Beauchemin's Blogbobb (bobb) - Tue, 17 Jan 2012 18:03
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 filestream in SQL Server 2012. Namely, you can have multiple filestream containers (filespecs) per-filestream filegroup. This has an effect on the paths returned by PathName(). In SQL Server 2008/R2, PathName() returns a path that looks something like this: \\ZMV08\MSSQLSERVER\v1\Northwind\db
simple-talk.comRobert Young - Tue, 17 Jan 2012 00:00
The 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'.
simple-talk.comBuck Woody - Tue, 17 Jan 2012 00:00
We asked Buck Woody to come up with his favourite 'Cloud' Howlers. After 'Howler' monkeys, we are faced with Howler letters. Buck dreams of sending Howler letters to the folks who dreamed up the marketing hype around 'cloud' services, who misunderstand services, who don't prepares applications for distributed environments and so on.
Bob Beauchemin's Blogbobb (bobb) - Mon, 16 Jan 2012 21:06
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
Bob Beauchemin's Blogbobb (bobb) - Mon, 16 Jan 2012 16:00
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
Bob Beauchemin's Blogbobb (bobb) - Mon, 16 Jan 2012 06:17
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 virtual file share. Although the namespace management and fitting non-transactional access into a transaction-based system, and its easy to think of the share as a real file share, SQL Server manages everything. And you can manage the FileTable data completely with T-SQL.
Glen Berry's SQL Server Performance - Thu, 12 Jan 2012 18:51
I recently ran into a production instance of SQL Server 2005 SP2 (Build 3042) that is running in a two node, single instance fail-over cluster, running on Windows Server 2003 R2 SP2. As you may be aware, this entire stack … Continue reading →