Upgrading to SQL Server 2012 May 17th, 2012

Vinod Kumar

As new versions of software get released, companies and products also get into this natural transition of upgrading and supporting the latest and greatest. And in the same space, SQL Server is no exception -almost as an predictable cycle, every 3 years we can expect a version of SQL Server getting released. This has been a trend since the SQL Server 2005 version for sure. As part of my job in meeting customers, I have to give recommendations and best practices to move to the latest versions. There are so many nuances to keep in mind as we make a successful upgrade. Let me take a moment to give you some of the suggestions I give to customers. Hope these will be helpful for you too.

Bible to start

You need to always start with preparation phase. This phase involves multiple steps and here are some of them for you to start.

  1. Keep the SQL Server 2012 Upgrade Technical Guide handy with you always. This is a very long document to read but it is worth the reference. I am outlining this as the FIRST reference because I will refrain from what this document covers as content. It is exhaustive and as I said with ~450 pages has a lot of material on how to upgrade. Also for reference SQL Server 2005 to 2008 Upgrade Whitepaper.
  2. If you still want to know WHY UPGRADE? I am attaching a link to some of the new features document for your reference. Knowing the new features is an important step before you upgrade. Later, you must be looking at how you can start maximizing the new features introduced with the new version.

Just upgrading by keeping the compatibility level at a older version is not real upgrade. Evaluate at using some of the new features to increase productivity, performance, maintainability and scalability.

As management takes the decisions for upgrade, DBA’s and developers need to also get themselves set for this task. Any upgrade process cannot be a simple overnight activity but more of a calculated informed step as line-of-business applications are at stake and it can bring down the efficiency of the teams working inside the organizations.

  1. Take a BACKUP of your databases. This is the first step irrespective of what you do next. A backup is required to make sure we have a fallback mechanism if things go wrong. Also make sure if you are using any specific system databases and objects in those DB, these are appropriately backed-up or scripted out.
    1. Take FULL Backups
    2. Transaction Log Backups
    3. Backup Database Master Keys
    4. Backup Service Master Keys
    5. Backup any external resource used (CLR DLLs, Extended SP) and anything that is required for the application outside the database for proper functioning.
  2. Before the backup process, just run a DBCC CHECKDB across the databases to make sure there is no corruption before the backup and they are consistent. This step can reduce any heartburns at a later point in time post upgrade :).
  3. As the upgrade talks happens, get started with tracking if any deprecated features are being used. There are multiple ways to do this – I highly recommend using the “SQL Server: Deprecated Features” Perfmon counter to track it like a background task. As your application runs, this gives you a rough idea of how many deprecated features you might be using inside your application that might have an impact to your upgrade process. There is also a Trace Event for capturing Deprecated features with SQL Server Profiler which you can use but that will be to nail down which code is causing that perfmon spike.
  4. Before initializing the upgrade process, please start by running the Upgrade Advisor as a first step. This surely guides you of potential problems upfront and is one of the essential step too for any SQL Server upgrade process.
  5. Keep track of the Supported Versions and Editions as you plan for Upgrades. With changes in licensing plans, LPIM (lock pages in memory), AWE gone etc. It is critical to keep track of how we upgrade between environments.
  6. Take a typical workload using profiler and keep it handy for a later test. Create a separate environment, bring the databases via a backup / restore operation to the new edition. Look for warnings, errors, changes in functionality and performance bottlenecks. Make sure to replay the workload collected before to see if anything unusual behaviors or errors. Document and then make sure these are mitigates during the actual run.
  7. Also, review the KB article that talks about “How to move databases between computers running SQL Server”. Especially, when we move between servers during upgrades we might need to change the location of files and hence have to account for the same. Also on a new setup, make sure the drivers for SAN or other hardware components are done properly and are the latest as suggested by the vendor.
    1. There are interesting ways to install as we move forward with SQL Server 2012 like, you can install SQL Server with SMB Fileshares as storage options.
    2. Another, is to install it on a Windows Server Core. This is interesting because now you don’t have an UI to manage SQL Server and it also reduces the surface area of attack and patch management is easy too.
    3. Considerations for SysPrep install for SQL Server – read documentation.
  8. Also, since this is an upgrade to a new box, make sure you are having enough space allocated for atleast 2-3 years of growth in mind for the database locations. Now is a good time for you to reassess some of the values defined for the auto-growth settings. Ideally, I suggest never allow SQL Server to grow but you pre-allocate it as part of your administration window cycle by properly monitoring SQL Server Data file free space.
  9. Before you install the Database on a new server, make a note of the Collation requirements for your applications. This is an important step and you don’t want get into a server which is Case-Sensitive while the application is built assuming case-insensitive.
  10. Don’t forget some of the critical addendum functions that you might have configured like replication, mirroring, clustering, service broker, maintenance plans, local user accounts, SQL Server Logins, service accounts, Linked servers, common backup path locations, configuration settings, DR strategies etc. All these need to be accounted and have to be changed accordingly.
  11. Whatever step you take, document them meticulously and run the document through a junior tester on a fresh SQL Server environment. This will test your upgrade process documentation and at the same time make sure that you are not missing any step to avoid last minute glitches. Document every single error encountered and the solution for the same – it is better to be prepared than to fail not being prepared.
  12. When I call out documentation, look out for hidden gems in your process like – Startup Processes, SQL Server Trace Flags that were enabled, Error Log settings, External DLL / CLR assemblies used, powershell scripts, any application Windows services that have been installed, external third party backup tools etc. Also make sure the server is secure by default and make sure any unnecessary services (SQL Browser, IIS, virus scanners, spooler etc) if running have been stopped.
  13. If you are planning to move into Virtualized environment, know what does it take to run SQL Server in Hyper-V Environment (though little outdated, is a worthy read). Also, read about running SQL Server with Dynamic Memory in HyperV Environments. I saw a session video in NA TechEd on this very topic which can be a good watch: Microsoft SQL Server Consolidation and Virtualization: Myths and Realities.
  14. More documents worthy for reading on the Virtualization subject are: Hyper-V is the Best Virtualization Solution for SQL Server, High Performance SQL Server Workloads on Hyper-V, Planning, Implementing, and Supporting SQL Server Virtualization with Windows Server 2008 R2 Hyper-V and Live Migration and Support Policy for SQL Server Virtualization.
  15. What are your plans for upgrade: in-place upgrade, side-by-side on the same box or side-by-side on a different box? Irrespective of the method used Keep in mind Point 1 :).
  16. Have a small test-stub workload where you are aware of the parameters and the expected results from the server. Make sure you run this pre and then post the upgrade and debug any anomalies based on the output. If you have automated test scripts, these are the best to be run against the server for a quick test with expected values.
  17. Have a failsafe strategy if things don’t go as per the plan. Your backup can come handy here and be careful especially if you plan to do a in-place upgrade as there is not much choice to come back other than using the backup route.

Know the fine prints

Please make sure you are aware of the specific call-outs mentioned in the Upgrade Technical reference mentioned above. During upgrade there can be specific features or settings that might need special attention. I am calling out some of them as I have seen at multiple customer upgrade process.

  1. The default setting for max worker threads in SQL Server 2000 is 255. When you upgrade from an instance of SQL Server 2000, the database engine keeps setting the value of max worker threads. However, it is recommended you change the value of max worker threads to 0 before the migration, to allow the Database Engine calculates the optimal number of threads.
  2. Changing the Compatibility Level while users are connected to the database can generate incorrect results for sets of active queries. To avoid inaccurate results, it is recommended to change the Compatibility Level with the database in order to a SINGLE_USER mode and then back to MULTI_USER. Do this post the upgrade activity.
  3. Look at server side settings from the older version, as you plan to move to new hardware’s some of these old values doesn’t make sense. Keep special note to MAXDOP settings, number of TempDB files etc.
  4. Just like the recommendation on Server Core, I highly recommend not to install any new components that you may not be planning to use in the immediate future like – Data Quality Services, PowerPivot, Master Data Services, Analysis Services etc.
  5. Look at the Security considerations and the protocols used for communication as part of upgrade. Each new version there are some changes and analyze the ports you have as standards open at the new environment. Security considerations during Install is documented and worth reading for every version upgrade.
  6. In earlier versions of SQL Server, the values ​​for the table and index row counts and page may be incorrect. Thus, databases that were created in versions prior to SQL Server 2005 may contain incorrect counts. After you upgrade a database from SQL Server 2000, it is recommended that you perform the DBCC UPDATEUSAGE to correct any invalid counts. This DBCC statement corrects the count of rows, used pages, reserved pages, leaf pages and data for each partition in a table or index.
  7. Update statistics for all Indexes after the Upgrade process. This is one of the most important step. Keep in mind also the Plan guides and hints that you have used in your application. Test these sections for any potential performance problems. Given the upgrade, SQL Server engine would have also changed and hence testing them matters. In case of Full-text catalogs, make sure these are also updated.
  8. As a sanity check, make sure to run a DBCC CHECKDB on each of the databases which have been part of the upgrade process.
  9. Over and above this, I also recommend checking each of the database properties which are critical. Check the recovery models, Auto-Close, Auto-Create-Statistics, Auto-Shrink, Auto-Update-Statistics and Auto-Update-statistics-Asynchronously. Also in this list, I highly recommend setting value of CHECKSUM for the Page Verify property at the DB properties level.
  10. Know and document the changes that need to be done on the application to start pointing to the new upgraded server. This might sometimes involve Connection String changes in Web Server deployment. Also, track if any reporting server and Sharepoint installations are pointing to the old servers as data sources. Even these need to be changed as part of movement.
  11. After all this, don’t forget to take a Backup of your system. You don’t want to do all the hard work and find out the HDD crashed the next day :). Backup and secure on a different media and location.

I think this very much sums-up what I had in mind based on various implementations that I have seen. These steps are very much towards migration of the SQL Server Relational engine but doesn’t talk about the SSAS, SSIS, SSRS workloads. I would again reiterate to look at the Upgrade Reference Guide mentioned above as a good guide to start.

A successful upgrade uses a cycle of: Plan, Document process, Test, Refine process, Test, Plan upgrade window, execute, verify upgrade and then opens for business.

Now that we have upgraded into the next version of SQL Server, now start looking actively at how we can start using the new capabilities available inside the next new release. I am just calling out some of them that are worth mentioning here for SQL Server 2012 release:

SQL Server: AlwaysOn Technologies

xVelocity in-memory technologies of PowerView and PowerPivot

BI Sematic Model

File Tables

SQL Server 2012: Express LocalDB

SQL Server ColumnStore Index

Data Quality Services

Security Enhancements – Contained Databases

Big Data Analytics and Hadoop Connectors

Microsoft SQL Server JDBC Driver 3.0

Microsoft Drivers 3.0 for SQL Server for PHP

Links worth keeping track as you upgrade:

Deprecated features: http://technet.microsoft.com/en-us/library/cc707789

Discontinued features: http://technet.microsoft.com/en-us/library/cc707782

Breaking changes in 2012: http://technet.microsoft.com/en-us/library/cc707784

Behavior changes in 2012: http://technet.microsoft.com/en-us/library/cc707785

Full-Text Search Backward Compatibility in SQL 2012: http://msdn.microsoft.com/en-us/library/ms143544

Hope these links and resources will be of help for you as you plan your upgrade and movement to yet another version of SQL Server. I would love to get your inputs and understand if there are any glaring steps that I would have missed mentioning as part of the checklist mentioned above.

7 Comments »

Continue reading...


 

Staying productive Working from Home too May 15th, 2012

Vinod Kumar

Working in big MNC’s there are some perks that as an employee you have and will relish – one of them is this “Working from home”. But how to maximize on the benefits is a challenge for many of us out there. I know because of this very reason that many of the companies atleast in India refrain from giving this Working from Home option. I am super addicted to this option, though I try to use with caution and minimally. There are interesting stats, from the Skype site that close to 34% of Americans works from home part-time it seems. There is surely an extra comfort, flexibility in your schedule, the extended morning sleeps and lastly the point to ponder is – are we as productive as we are if at office? Let me give you some of my known best practices I use to keep the same levels of commitment and get work done at the same time with flexibility.

I am not here to say this is a bad idea, but though a big time supporter of this concept – don’t want people to abuse the flexibility offered. Understand, for working mom’s these are great benefits. But at the same time to balance this life, there needs to be some method to the madness and I am here to call out some of them.

Juggling your time

The #No:1 thing that comes to my mind in this subject is – how should you be managing your time? This step includes to start the day looking at the work in hand and how the schedules are for you to play the whole day. Yes, you are taking a chance of staying at home to complete one-two tasks like pickup kids, get that plumbing work done, goto a bank maybe etc. So keeping that back of your mind – look how are the meetings lined up, how will you track your projects, when are you going to email, what are the background tasks that got assigned to you etc. Now keep blocking your time appropriately to make sure there are lesser distractions of emails, facebook, twitter or anything else at home. Make sure the reason why you decided to stay at home doesn’t interfere anywhere in the middle of the schedule you have in mind. If that being the case, you are better off taking the day off and giving that much needed time to family and sort things off.

Being Organized

I think this is very much in-line to the same point made above. But think – as freelancers or managers for projects, your time at home is crucial too because you get paid by the hour. I am a big time supporter that you need to have that critical TODO list. Do you track what you do during the day? Have you had a chance to see this tool? I had used it long time back RescueTime – it tracks how much time you spend on various applications and what are your patterns. Based on this it gives you an indicator to what are your trends. This can be your background tool to monitor yourself.

Use the Sticky Notes or I am more comfortable in writing the activity list and love to tick through the day. I know it is old style but it has worked for me over the years like a charm. You can use any method that works – the idea is to do it :).

Prioritize your job functions

A lot of them have asked me in the past, how I manage doing a number of things. Well, it is about the previous two points and finally about making time for everything that I want to do – work, blogging, time with family, shopping, entertainment etc. So there are ways and time when everything gets the much needed time slice from life. I call this out explicitly because if you are a developer then you need to write the code than troubleshooting some bug, if you are a freelancer then you shouldn’t be getting swamped into just the financial closure when you must be out there training / consulting / coding, if you are a manager then you should be looking at the project planning and alerts rather than building some random reports. So there are so many of these side activities that sometimes drain you away of the much need time for the important things. So this is what I call prioritize and deliver – delegate if required and have an appetite for NO if that must be used as extreme measure.

Final words

These principles are not specific to Working from Home but are critical for such tasks. I use these techniques irrespective of the situation and you will find a note or sticky note beside me when there are too many activities to complete in a given day. Prepare, plan, execute and track on progress. The cycle can be as simple as this for everything we do. The same can be also said for preparing presentations :).

What are your methods? Do share them so that we can learn from each other :). Happy working …

14 Comments »

Continue reading...


 

SQL Server 2012: ColumnStore Characteristics May 10th, 2012

Vinod Kumar

I was generally exploring for content to explain the nuances of using ColumnStore Index with SQL Server 2012. And interestingly during a conversation with Pinal, he had already written a number of blog posts on the same subject – fundamentals, ignoring columnstore, video demo of performance. Seeing this I thought maybe I want to write something different on the same subject and leave the basic learning to those posts as they make a good read.

Columnstore indexes group and store data for each column and then join all the columns to complete the whole index. These differ from traditional indexes that group and store data for each row and then join all the rows to complete the whole index. For some types of queries, the SQL Server query processor can take advantage of the columnstore layout to significantly improve query execution times. Columnstore indexes in SQL Server have key characteristics that differentiate it from a traditional row-based store. Let me list a few here, these are simple and good to know information:

  1. Just like the explanation I did above, unlike the traditional row-based organization of data (called rowstore format), in columnar database systems such as SQL Server with columnstore indexes, data is grouped and stored one column at a time. SQL Server query processing can take advantage of the new data layout and significantly improve query execution time. In this context you will hear the term xVelocity to describe the columnstore indexing engine.
  2. There is no concept of key columns in a columnstore index, so the limitation on the number of key columns in an index of 16 does not apply to columnstore indexes. But it cannot have more than 1024 columns.
  3. The index cannot be clustered index for columnstore. If a base table is a clustered index, all columns in the clustering key must be present in the non-clustered columnstore index. If a column in the clustering key is not listed in the create index statement, it will be added to the columnstore index automatically.
  4. It can neither be a primary key or foreign key and cannot have the INCLUDE keyword for adding columns.
  5. Columnstore indexes work with table partitioning. No change to the table partitioning syntax is required. A columnstore index on a partitioned table must be partition-aligned with the base table. Therefore, a non-clustered columnstore index can only be created on a partitioned table if the partitioning column is one of the columns in the columnstore index.
  6. The index key record size limitation of 900 bytes also does not apply to columnstore indexes. It cannot be unique Index and cannot include sparse columns. You cannot also include the ASC or DESC keyword as part of the index.
  7. Along with the columnstore index, SQL Server introduces batch processing to take advantage of the columnar orientation of the data. This can be seen in the Logical step of a Execution Plan called “Batch”.
  8. Only the columns needed must be read. Therefore, less data is read from disk to memory and later moved from memory to processor cache.
  9. Most queries do not touch all columns of the table to satisfy a query. Therefore, many columns will never be brought into memory. This, combined with excellent compression, improves buffer pool usage, which reduces total I/O.
  10. In this version of SQL Server 2012, the ColumnStore Index makes the table into ReadOnly mode. And it cannot be combined with Compression, replication, Change tracking, Change data capture and Filestreams.
    For Updates, there are always workarounds as discussed on MSDN.

DMV’s to keep track

sys.column_store_dictionaries: Contains a row for each column in a ColumnStore index.

sys.column_store_index_stats: Contains a row for each ColumnStore index.

Update: Seems like the this DMV will get deprecated, Pinal wrote about this.

sys.column_store_segments: Contains a row for each column in a ColumnStore index.

INDEXPROPERTY – Adds a property IsColumnstore to identify xVelocity columnstore.

This feature is strongly geared towards DW workloads and I had an opportunity to meet customers with TB’s of data to process in short time. And this feature was something they just loved using even given the restrictions. Some of the restrictions may be eliminated in future versions but this xVelocity concept is quite powerful to be ignored. If you do use this, drop a line !!!

11 Comments »

Continue reading...


 

Don’t get stuck in your career May 8th, 2012

Vinod Kumar

This topic has been on my mind for quite sometime and I wanted to pen it down today. I do know it is not a conventional easy topic too. With the March/April fever of performance reviews there is sense of accomplishment fore few and for some a sense of disappointment. This blog is not to outline how to outperform your reviews but it is to seed a thought to your short term excitements with the long term visions. I am a strong believer that everyone needs to have an answer to – “Where do you see yourself in 5 years time?”.

Often when I meet the new generation of kids with 2-4 years experience they come and tell me – “I am not going anywhere in my career”. Kids, don’t get me wrong here – “You are yet to have a career”.

It’s not what you achieve, it’s what you overcome. That’s what defines your career.

There are some things that will ensure you go no where in your career, and this is not possible without your consent. Let me call out some of the traits that will need to be changed if you want your career to go somewhere:

  1. Passing the buck: Please don’t stuck to this thought and don’t get rigid in your communication. I understand it is not your job, but are your trying to be helpful and making an attempt? It is this positive attitude in life that will help. Don’t avoid work (any work) and your chances to take responsibilities.
  2. We are not genius: I understand we don’t know everything that is put in front of us. It is fine to say – “I don’t know”. But that is not what you should be doing. If you make genuine efforts and add saying “yet let me find out for you”. Same as point 1, be helpful and get / give / find answers – not blank faces.
  3. Blame game: Stop doing a blame game. You need to stand up and stand tall if you want to in front of your colleagues. You don’t impress anyone by putting down someone else in front of all. If you are a manager, well accept in public but blame in private 1:1 – be a leader not a mere manager.
  4. Make time: If someone has come for help to you, acknowledge their existence and lend an helping hand. Don’t try to focus on the gossips but move on to help with the burning fire in front of someone. In a team everyone wins – not just the individual. Learning and sharing is a journey and make it memorable with people around you.
  5. Come in early, don’t call in sick: Exceptions are there to this rule. But if you made a commitment – make it happen no matter what. I am off the school of thought that if committed it is done with no compromises. If you constantly try to evade off then don’t think no one is noticing. In an organization you don’t (mostly) get a choice to pick the tasks which you like to do, if you don’t do it – someone will step up or will eventually do it.
  6. Improvise and innovate: I cant iterate on this very point. You don’t want to get step by step instruction on how to get things done. Innovate and don’t get stuck to the process boundaries that exist. It is always easier to think and do it our way rather than getting bogged down by our micro-manager to how it has to be done.
  7. Communicate clearly: Clarity of thoughts and clarity in communication will lead you a long way and I love to iterate it here.

If you know where you are going, then you don’t need a GPS or compass. You will make your own new way.

If you want to make merry of where you will be in your career, well know where you want to go. Make positive strides and keep working on the learning hunger of your’s. I have advised many with a simple question – “Where do you want to go?”. So this blog is to again  make that statement even more bold :). Do pass me your comments and what you think about it.

54 Comments »

Continue reading...


 

Access Denied: Run SSMS as Administrator May 4th, 2012

Vinod Kumar

I recently upgraded my machine to Windows 8 and there are so many changes that are worth mentioning as part of security enhancements for SQL Server 2012 and Windows 8 combinations. We will discuss in various blogs in the future, but here is one of the question I got from one of my ASP.NET developer that after he upgraded he started getting this below error for attaching an existing database.

image

Textually the error is something like:

TITLE: Microsoft SQL Server Management Studio
——————————

Attach database failed for Server ‘xxxxxx’.  (Microsoft.SqlServer.Smo)

——————————

Unable to open the physical file "C:\SQLSamples\AdventureWorks2008R2_Data.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

——————————

Worst was, the link for help from SSMS was also not getting him anywhere. Here are the sequence of things that I asked:

  1. Which OS are you running Windows 7 or Windows 8? Ans: Windows 8
  2. Has UAC been turned off or on? Ans: Off (asked why … but that is a different discussion)
  3. Great, did you start SSMS in Administrator mode and try? Ans: Well, UAC is off right? Then why should I.
  4. Me: Trust me :)
  5. Ans: Woala, it is now working.

The solution is not specific to Windows 8 but can be on Windows 7 too, tough it will be interesting to see how we have made “Security by default” more restrictive on Windows 8. Simple solutions for simple tasks :)

Do tell me if you have encountered this. Hope this helps.

2 Comments »

Continue reading...


 
« Older Entries