MSBI (SSIS/SSRS/SSAS) Online Training

Tuesday, February 26, 2013

Upgrading to SQL Server 2012


Hi ,
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:


xVelocity in-memory technologies of PowerView and PowerPivot






Security Enhancements – Contained Databases




Links worth keeping track as you upgrade:





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.

Thursday, February 21, 2013

Online MSBI Certification Training (SSIS,SSAS,SSRS)


Hi Aspirants,

Don’t miss great opportunity; increase your resume weight by adding certifications on your technology.

It’s just week training, 100% guarantee that you will clear the exam.



Write an email for additional details: nv.ravindranathreddy@gmail.com
Value Adds:

1 week online classes (1 hour daily)

Online Exams

Complete idea on MSBI suite

Self confidence to write certification

Certification Details:

Ø  MCTS:70-445

Ø  MCTS:70-448

Ø  MCITP:70-452

Ø  MCITP:70-450

Ø  ITIL Foundation



Please refer below Microsoft site for more details about MS certifications.




 

Microsoft Sql Server DBA (70-450):


ITIL Foundation:


Wednesday, February 20, 2013

"Performance Engineering" - Online Training by the one of the world Expert's



Hi All,

The best world leader’s will make you learn the what is Performance Engineering ???

Connect with us to improve your performance.

Manual/Automation Testing tool online training with efficient trainer’s


Cheers,

Regards,

Ravindra

Monday, February 18, 2013

MSBI Online Training – Job Guarantee!!


Write an email for further enquiry: msbimaster4u@gmail.com
Value Ads:


Ø  The best coaching & the best material



Ø  Just 45 days

Ø  24 X 7 phone support

Ø  Free guidance till placement

Ø  Real time scenarios

Ø  Preparation for interviews


 






Syllabus:

Datawarehouse

Introduction to Data Warehousing Concepts

i. What is Data Warehousing?

ii. Need for Data warehousing

iii. OLTP Databases Vs DW

iv. Multi-dimensionality

v. Star Schema

SSIS

Module 1: Introduction o SQL Server 2008 Integration Services

This module introduces the role that Integration Services plays in extracting,

transforming, and loading data. This module also describes

the tools that you can use to build and manage Integration

Services solutions.

• Overview of Integration Services Solutions

• Integration Services Tools

Module 2: Developing Integration Services Solutions

This module provides an overview of the development tasks that are involved in

creating an Integration Services package. After completing this module, you will be able to create a basic package.

• Creating an Integration Services Solution

• Using Variables

• Building and Running a Solution

Module 3: Implementing Control Flow This module introduces the tasks and precedence constraints that you can

use to implement control flow in an Integration Services package .

• Control Flow Tasks

• Control Flow Precedence Constraints

• Control Flow Containers

Module 4: Implementing Data Flow

This module describes the data flow sources, transformations, and destinations that you can use

to implement a data flow task in an Integration Services control flow. It also explains how to use

data flow paths to direct valid and invalid rows through the data flow.

• Data Flow Sources and Destinations

• Data Flow Transformations

• Data Flow Pipeline

Module 5: Implementing Logging

This module discusses how to use logging in an Integration Services package, and explains how to

configure and use logging providers to generate information about a package’s execution.

• Overview of Integration Services Logging

• Implementing Logging

Module 6: Implementing Checkpoints and Transactions

This module explains what checkpoints are and how to implement them . It then discusses transactions, and

describes how you can implement transactional data access logic in an

Integration Services package.

• Implementing Checkpoints

• Implementing Transactions

Module 7: SSIS Administration.

This module discusses how to create Package Configurations and how to deploy Integration

Services packages to production servers. Package Configurations

• Deploying Packages

• Managing Packages

SSRS

Module 1: Introduction to Microsoft SQL Server Reporting Services

This module introduces the role tha t Reporting Services plays in an organization's reporting life

cycle, the key features offered by Reporting Services, and the components that make up the

Reporting Services architecture.

Overview of SQL Server Reporting Services

Installing Reporting Services

Reporting Services Tools

Module 2: Authoring Reports

This module introduces the fundamentals of report authoring, including

configuring data sources and data sets, creating tabular reports, summarizing data, and applying

basic formatting.

Creating a Basic Table Report

Formatting Report Pages

Calculating Values

Interactive Navigation

Displaying Data

Module 3: Manipulating Data Sets

This module explores data sets to a greater depth, including the use of alternative data

sources and interacting with a data set through

the use of parameters. Students learn how to dynamically modify the data set underlying

a data region by allowing parameters to be sent to the underlying query. They also learn

to use best practices to implement static and dynamic parameter lists when interacting

with queries and stored procedures.

Defining Report Data

Using Parameters and Filters

Using Parameter Lists

Using web service

Module 4: Using Report Models

This module describes how to create a report model so that business users can create their own

reports without using the full Report Designer development environment. Students also learn

how to use Report Builder to create a report from a report model.

Creating Report Models Using Report Builder

Module 5: Publishing and Executing Reports This module explains the various options you can

use to publish reports to the report server and execute them.

Publishing Reports

Executing Reports

Creating Cached Instances

Creating Snapshots and Report History

Module 6: Using Subscriptions to Distribute Reports

This module describes how to implement subscriptions so that you can distribute reports either automatically

by e -mail or by publishing reports to a shared folder. Introduction to Report Subscriptions Creating Report

Subscriptions Managing Report Subscriptions

Module 7: SSRS Administration

This module discusses how to administer the Reporting Services server, how to monitor and

optimize the performance of the report server, how to maintain the Reporting Service s

databases, and how to keep the system secure .

Server Administration

Performance and Reliability Monitoring

Administering Report Server Databases

Security Administration

SSAS

MODULE 1:

Introduction to Analysis Services

This module introduces the role that Analysis Services plays in an organization's, the

key features offered by Analysis Services, and the components that make up the Analysis

Services architecture

OLTP, DWH

OLAP TYPES

SSAS Architecture

Dimension Tables

Fact tables

Cubes

Analysis Services Tools

Module 2:

Cube Design process and Concepts This module introduces the role that Cube plays in Analysis Services, and the

components that make up the Analysis Services Cube

Dimensions

Measure Groups

Creating simple calculations

Aggregations and Hierarchies

Dimension Relationships

Translations

Perspectives

Module 3:

Advanced Cube Design Process This module introduces Advanced Methods like KPI,

Actions and Write back that make up the Analysis Services Cube.

MDX introduction and Queries

Implementing KPI

Implementing Actions

Using Write back

Module 4:

SSAS Administration

This Module provides understanding of production management activities like security, deploying, processing and

scheduling Analysis tasks

Applying Security Roles

Levels of Security VLDB

with partitioning OLAP

Processing

Monitoring Cube Activity

Deployment and Scheduling

Planning for Disaster and Recovery