Data Management - Author(s): Ted Krueger (onpnt)

SQL Server Baseline Collection

Ted Krueger (onpnt) — Tue, 07 May 2013 10:38:00 +0000

SQL Server baseline collection is an important task that is too frequently overlooked. Collecting information on how SQL Server is both providing data services and reacting to how those data services are being utilized, creates a foundation for efficient troubleshooting and predicting growth. Take a typical problem in which a customer (anyone you are providing data services to internally or externally) comes to you and states something is slow and they think it is the data services. In most situations, this would lead to a drawn out process of looking into the health of the instance directly related to the situation. What if we can first ask - and answer - the question, “What is normal performance for the period in which the data services seem to be slow?”

In reality, troubleshooting is a methodology that has an equation simply stated as – problem * identification / normal operations = solution. This means that the problem must have an identity in order for a solution to be developed. The solution cannot be sound or stable without identifying the problem. Furthering this, we can ascertain that a solution can be an evolving equation as well. A solution required, in many cases, forms a further problem in the form of changing the data services as they are at the time the initial identified problem was uncovered. This can be in the form of growth, maintenance, introduction of new variables and so on.

As we can see, this question of “what is normal” is hard to answer as an overall solution without identification and more so, a basis of what is a normal running status.

Baseline Collection

Collecting information on SQL Server has been extremely simplified since the release of 2005. This has been made possible by the exposure of data management views, system performance information and other metrics that we can easily retrieve and store for analysis. Even with this ease of retrieval, many times the development effort to set up an automated solution is foregone due to the resources available (or lack of resources). Fortunately, several SQL Server experts simplify this task further by releasing prewritten queries and distinct metrics that can be beneficial to retain for analysis over periods of time. Glenn Berry of SQLskills.com is one of those experts. Glenn has shared with us a series of diagnostic based queries that retrieve information about how SQL Server is running. These queries also expose information that can be retrieved on a time interval to base analysis over a time period. These can be used to form a baseline of how SQL Server is running. With this information being collected, one can fill in the much-needed information to the equation we discussed earlier and base a solution that is more sound and stable.

Even with the work that Glenn has done for us, there is a small amount of effort needed. In the case of the queries Glenn provides, SQL Server Integration Services (SSIS) retains much power in a rapid development platform that can be automated for later executions.

After reviewing the queries Glenn has provided, 32 distinct tables can be formed from them. Creating all these tables may seem at first to be a daunting task but it can be simplified. Working through Glenn’s SQL script, identify the segments and queries that retain value for collecting them over a period of time. Run each statement taking advantage of the SELECT INTO statement.

Note: before reading on, please ensure to always show the recognition that is deserved while using anything someone has provided to the public for use. In this case, always place the header in each area the code is used to show Glenn as the author and SQLskills.com as the supporting company.

-- SQL Server 2008 R2 Diagnostic Information Queries

Code: tsql

-- SQL Server 2008 R2 Diagnostic Information Queries
-- Glenn Berry
-- April 2013
-- Last Modified: April 15, 2013
-- <a href="http://sqlserverperformance.wordpress.com/">http://sqlserverperformance.wordpress.com/</a>
-- <a href="http://sqlskills.com/blogs/glenn/">http://sqlskills.com/blogs/glenn/</a>
-- Twitter: GlennAlanBerry

-- Note: A number of these queries will only work on SQL Server 2008 R2 SP1 or later
-- They are all noted in the instructions
-- SQL Server 2008 R2 RTM was retired on July 12, 2012

-- They are all noted in the instructions

-- SQL Server 2008 R2 RTM was retired on July 12, 2012

If an author does not provide a header such as the one above, add one stating the source, author and date.

For example: For the SQL Server 2008 R2 diagnostic information queries, the hardware information query pulling from sys.dm_os_sys_info, holds value and should be retained and monitored for change. This is evident from wanting to know how a baseline is altered by hardware changing over time. To make a table based on this query, simply add an INTO <table_name> to the statement and the table will be created. Remember to always verify the data types that SQL Server has created are accurate by sampling the data and make adjustments as needed.

Other considerations while creating these initial tables are the instance name, collection date and, if applicable, the database name. Without these pieces of information, a baseline cannot be formed. Make these small adjustments so the three key pieces of information are captured. Using @@SERVERNAME, GETDATE(), and DB_NAME(DB_ID()), they can be obtained quite easily.

Code: tsql

-- Hardware information from SQL Server 2008 and 2008 R2 (Query 8) (Hardware Info)
-- (Cannot distinguish between HT and multi-core)
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)],
sqlserver_start_time --, affinity_type_desc -- (affinity_type_desc is only in 2008 R2)
,@@SERVERNAME [InstanceName]
,GETDATE() [CollectDateTime]
INTO Hardwareinfo
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

After performing this task for all the information to be collected, the tables will start to look much like the ones below. Ensure all the tables that are specifically designed for database-level information including the database name.

SSIS Automation

After you’ve created all the tables found to be of value, the SSIS package can be created to automate loading the data based on a schedule. In a stripped down form, the SSIS package can be executed with nothing more than the use of a series of Data Flow Tasks (DFT) and a Foreach Loop Container. The Data Flow Tasks will be a direct source to destination loading scenario except for the initial task of loading a table with the information for the instances. Loading the information about the instances is a task that requires a lookup to decide if something has changed. If nothing has changed, there is no need to insert duplicate rows, which will only make reporting off the information more difficult in the future. This lookup task would lead off the entire process.

Internally to the DFT, the process would be a source and then a lookup on the destination. This would allow the decision to be made if the data requires a new row or an update.

In the case above, the row is always updated. The process can also make the decision to do nothing or validate what has changed. In the case of instance information, the count of the overall rows and width of the data is typically so low, the update should not pose a performance problem.

The next series of DFTs are all similar. Group tables together so multiple queries can be executed and data moved in parallel. This will speed up the overall execution time of the SSIS package.

Take the same Hardwareinfo query from earlier. For a DFT to load this table, it would be an instance level task and not in the Foreach Loop Container.

This DFT is nothing more than a direct source to destination

Until we get to the database specific queries, the DFT creation has been fairly simple. With database specific queries, however, we need to obtain a listing of the databases that should be evaluated and have data collected. This can be done by using the Recordset Destination populated by a source adapter based on the following query.

Code: tsql

select name from sys.databases where name not in ('master','tempdb','model','msdb') and state_desc = 'ONLINE' and user_access_desc = 'MULTI_USER'

Now the Foreach Loop Container can be based on an ADO Enumerator and each internal DFT to the Foreach Loop can be executed only for the specific database that is currently allocated to a variable the Foreach Loop specifies in the object source variable. This would appear as shown below in the Foreach Loop editor.

Note: setting the MaximumErrorCount higher will prevent certain DMV queries that would error on databases from failing the entire package. This would allow the collection of databases even if one database caused an issue when the query was executed.

The finished SSIS Package would appear as shown below.

Baseline Reporting

Up to this point, the effort put into the baseline collection process has been minimal. Without a doubt, the reporting aspect will be a time consumption that far outweighs the SSIS or database creation to retain the information. Of course, skills may be much higher than this author’s in creating visualization of data. In fact, for the task at hand, I enlisted the expert knowledge of Jes Borland to offer a hand in making a representation of a few pieces of that data in the collection database.

Without visually being able to review the baseline data, it does not offer much value. The baseline data can be queried when needed but the overall needs of a baseline do not end with troubleshooting. We can obtain the needed information for requesting new hardware, consolidation needs and overall knowledge of data growth and business requirements that demand the need for SQL Server and the resources to evolve.

We can also obtain a quick review of how objects like indexes are being used. Indexes are evolving objects, and in that evolution, daily, weekly and monthly changes occur. Knowing how they are being used and evolving is critical to making decisions to adjust indexes so they fit the needs of the data services.

For example: looking at a segment of 5 days based on the collection in the IDXReadWriteStats table, we can (or, Jes can) design a chart as shown below on how the indexes are being read.

In this chart, we can quickly see on 4/1/2013, the pk_tblEntry (a master data table), was read heavy but then pulled back on reads on the other collection dates. This could show a beginning-of-the-month task that would affect the data services. Thus, the problem that could arise is the same situation where a customer comes to the team with a slow data service. If the date lands on the first and the database is directly related to the master data database, we know the entire equation to the problem and solution.

Summary

Baselines are a critical information collection process to all data services. This doesn’t stop with SQL Server but spreads to all data services. With the help and validation of resources available online in the form of scripts, blogs and articles, a solution can be development and implemented with a much lower amount of resources needed over the complete creation from the bottom, up.

If you would like to play with the SSIS Package that was written for this article and the database, you can download this compressed file. The database is SQL Server 2012. Restore the database and add the DTSX file to a new solution in SSDT 2012. Edit the variable User::SQLInstance to the instance you would like to start collecting from.

Original post blogged on LessThanDot.

Missing index suggestion – Duplicate Index

Ted Krueger (onpnt) — Sat, 20 Apr 2013 10:41:00 +0000

Missing index suggestion – Duplicate Index

The missing index feature, while tuning or checking estimated and actual plans, is very helpful. Something that is key in that sentence is the word, helpful. Don’t run out and simply create all of them. Make sure it is a viable solution that does not implement the three major faults in index strategies: duplicates, overlapping and misalignment.

In SQL Server 2008 and previous versions, the missing index suggestions had a little feature (read as bug) that would suggest an identical index to what was already on the table. In fact, in some cases, it would use the index that was a duplicate and still suggest to create it. You can see a great article on this from Paul Randal, “Missing index DMV bug that could cost your sanity…”.

With SQL Server 2012, this was thought to be resolved but we still can end up with the same situation.

For example, the table the example below is based on is a 290 million row table, partitioned by date. The date value is a range from the first day in a month, to the last. There is also a record ID for other purposes that is set with IDENTITY(1,1). When querying by this record ID, the plan suggests creating an index that is identical to the one being used in the plan. Partitioning, the query and the table structure is not what we will talk about today. What we will talk about is the reaction SQL Server 2012 took and suggested to make this query more effective. Remember – suggestions are, suggestions.

Query

Code: tsql

select max(recordid) from myTable where recordid > 1

Plan

The missing index details

Code: tsql

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[myTable] ([recordid])
GO

The index, IDX_RECORDID_ASC definition

Code: tsql

CREATE NONCLUSTERED INDEX [IDX_RECORDID_ASC] ON [dbo].[myTable]
(
[recordid] ASC
)
GO

As we can see, the two indexes are duplicated. If you were to create this suggested index, you would have a major performance impact due to the size of this table.

Summary

We do not always see performance issues from duplicated indexes from blindly creating indexes suggested by SQL Server. This is typically due to the fact that tables may not be large enough at the time to really see how the impact can degrade performance. When indexing, you always want to think, at a minimum, 20 times larger than what the table is and how it may impact how the table is used. If you have that mindset, even if it does not always apply to table like metadata tables, you’ll implement better indexing and better management solutions.

Original post blogged on LessThanDot.

SQL Azure Log Space Errors 40552

Ted Krueger (onpnt) — Mon, 15 Apr 2013 17:02:00 +0000

If you are dealing with SQL Azure and a large data volume, you’re bound to run into this error.

Msg 40552, Level 20, State 1, Line 1
The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction

This error is more of a pain than anything and you can work around it.

The structural architecture that SQL Azure uses and all around resource allocations, doesn’t allow us to go in there and simply size a transaction log like we with SQL Server on metal. At least, I haven’t found one. With that, we run into a mess of headaches when we have anything over a few hundred thousand rows in one table. Here is what you can do, to get beyond this error.

Changing Data and Log Usage Errors

Changing data means just that – updating, inserting or deleting large amounts of data. If you run into this error while loading data, try to perform the age old method of batch processing data. Now this goes against almost every fiber of a performance mindset given the advancements in data loading and transformation. However, it’s a life we have to work around while in SQL Azure. With SSIS, you can create multiple threads and work on each thread while only sending so much at once to SQL Azure. SSIS is fully capable of batch processing data also.

Creating, Rebuilding a Big Index and Log Usage Errors

Indexing is what got this article to be published. If you have a few hundred million rows in a table, create a nonclustered index on that table, you will undoubtedly result in the log space usage error. For a moment, this raised great concern. Truly, the data is no good in SQL Azure if you cannot read it in under 30 minutes, preferably seconds. So you need indexing to support the queries that are going to be used to read the data effectively. Luckily, SQL Azure does support ONLINE operations in indexing.

In this case, the ONLINE operation with a CREATE INDEX statement provides the functionality of the index to be built by taking advantage of the build main phase. This performs the primary needs of reading the table and then inserting by means of bulk loading operations, into the new index.

To create the index with ONLINE, you simply specify the ON value in the options.

Code: tsql

CREATE NONCLUSTERED INDEX [IDX_SalesOrderID_COVER_ASC] ON [dbo].[IndexPageCount]
(
[SalesOrderID] ASC
)
INCLUDE ([DueDate],
[ShipDate],
[SubTotal]) WITH ONLINE = ON)
GO

This “should” allow the index creation to complete. It will take longer for the creation but, the importance of having indexing is without a doubt, not something you can give up. Unless the data is simply a DR strategy or some sort of backup.

Original post blogged on LessThanDot.

Buffer Latch Error Restoring SQL Server Database

Ted Krueger (onpnt) — Wed, 10 Apr 2013 07:39:00 +0000

When working on SQL Server that is stretching IO and the subsystem to its limits, you are bound to see a buffer latch time-out error at some point. This is typically, "Time-out occurred while waiting for buffer latch type 2 or 3". If you do, it will usually occur while you are performing a large maintenance task, integrity check or something that really impacts IO to a level that causes the buffer latch time-out. In a recent case, I ran into this during a restore operation of a database that was a few hundred GB. This usually is a fairly quick operation and one that doesn’t cause an IO issue with SQL Server but in this case, the restore was pushed to a single SSD drive. This was only due to the restore being temporary and the SSD drive was free to handle the task.

In this case, the restore statement was as simple as it can get.

Code: tsql

USE [master]
RESTORE DATABASE TempRestore FROM DISK = N'E:\Backup Data\TempRestore.bak'
WITH FILE = 1, MOVE N'TempRestore' TO N'I:\TempRestore.mdf', MOVE N' TempRestore_log' TO N'I:\TempRestore.ldf', NOUNLOAD, REPLACE, STATS = 5
GO

The restore ran fine, as shown below, until it hit the database version upgrade section. In this case, this is a SQL Server 2008 database, restoring to SQL Server 2012. Or, database version 661 to 706.

5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
35 percent processed.
40 percent processed.
45 percent processed.
50 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
90 percent processed.
95 percent processed.
100 percent processed.
Processed 532576 pages for database 'TEMPRESTORE', file 'TEMPRESTORE' on file 1.
Processed 4 pages for database 'TEMPRESTORE', file 'TEMPRESTORE_log' on file 1.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Msg 845, Level 17, State 1, Line 2
Time-out occurred while waiting for buffer latch type 3 for page (1:0), database ID 27.

As shown, the error occurred upgrading the database and the restore failed. At this point, the database is left in restoring state or no recovery. What really isn’t shown is the fact the database restore was really done and the database upgrade was all that remained. This is where knowing restore stages and processing is a good piece of knowledge. (To learn more, "Understanding How Restore and Recovery of Backups Work in SQL Server").

Since the database was restored successfully and the only part that remained was to bring it online, upgrading the internal database version, the following statement should resolve the problem without requiring a completely new restore execution. In most cases, a restore is a one-way street. Start a restore and if it errors, there is no other way but starting over. In this case, don’t be so quick to take that ultimatum.

Code: tsql

RESTORE DATABASE TempRestore WITH RECOVERY
GO

The resulting messages below show the restore was at a state that allowed the call for recovery to complete and bring the database online by upgrading the internal version. Now, a critical step here to verify the databases integrity is to run a CHECKDB before allowing activity on it. This is even more critical if this is a restore that is required for production. Do not skip a CHECKDB on a database if this happens to you.

Converting database 'TEMPRESTORE' from version 661 to the current version 706.
Database 'TEMPRESTORE' running the upgrade step from version 661 to version 668.
Database 'TEMPRESTORE' running the upgrade step from version 668 to version 669.
Database 'TEMPRESTORE' running the upgrade step from version 669 to version 670.
Database 'TEMPRESTORE' running the upgrade step from version 670 to version 671.
Database 'TEMPRESTORE' running the upgrade step from version 671 to version 672.
………
Database 'TEMPRESTORE' running the upgrade step from version 700 to version 701.
Database 'TEMPRESTORE' running the upgrade step from version 701 to version 702.
Database 'TEMPRESTORE' running the upgrade step from version 702 to version 703.
Database 'TEMPRESTORE' running the upgrade step from version 703 to version 704.
Database 'TEMPRESTORE' running the upgrade step from version 704 to version 705.
Database 'TEMPRESTORE' running the upgrade step from version 705 to version 706.
RESTORE DATABASE successfully processed 0 pages in 289.824 seconds (0.000 MB/sec).

Summary

If you run into the error, “Time-out occurred while waiting for buffer latch type 3 ...” at the end of a restore, run a restore with recovery on the database before taking the long way around and running the entire restore again. If the recovery is successful, run a CHECKDB to ensure the integrity of the database is good and if it is, your error will not have caused you a much longer and time consuming task of running the entire restore over.

Original post blogged on LessThanDot.

Loading Large Volumes of Data into SQL Azure with SSIS

Ted Krueger (onpnt) — Fri, 22 Mar 2013 20:34:00 +0000

Loading data into SQL Databases (Azure) is fairly simple. With SQL Server Integration Services (SSIS), the task becomes even more trivial as with many ETL tasks that we’ve done in the past from data source to data source. Truly speaking, a data source is just that, a data source. The true task at hand is in the preparation and transformation of the data between the source and destination. Viewing SQL Databases as what they are, just another data source, makes the design of what you need to do less complicated.

Simple Testing

A basic test from a box version of SQL Server to a SQL Database is pretty uneventful. In the past, these tests have proven effective with only a few catches. A few of those are

1) Network, network and network. Your line to the “cloud” is critical to stability and speed.

2) Disconnections can be painful so plan for them. Have a restart point.

3) Manipulate your connections and data flow so they are tuned for a push to a cloud environment.

Before going too far, the testing so far has been limited to SSIS. BCP and Bulk Copy are two other options that should be investigated if you run into performance problems. However, SSIS being the flagship ETL for Microsoft Platforms, it is a given this is the tool you will look at initially. There is a need to state, “Use the right tool for the task”.

The first thing you can do in order to perform a simple test of pushing data to a SQL Database is go out and get a trial of Windows Azure. The trial is pretty small in terms of nodes and performance, but it serves as a great introduction to working with Azure. You can connect to SQL Azure with a number of methods. SSMS is typically going to be a common one if you are a DBA type or used to working out of SSMS. To connect to SQL Azure from SSMS, you can follow the instructions here, “Connecting to SQL Azure from SQL Management Studio 2008” or for a more visual look and addition notes on firewall changes, “Connecting to SQL Azure with SQL Server Management Studio 2008 R2”. With SSMS 2012, all of these steps are identical and nothing changes.

Once you configure your SQL Database, a good test is to push some data from AdventureWorks. This task is almost identical to the task of pushing data from one SQL Server to another. Create a connection (ADO.NET or OLEDB) for a source from SQL Server and AdventureWorks and then create a destination using the same guidelines and options as how you would connect to SQL Azure from SSMS.

Add a data flow

Push some data

This article will not go into the basic steps of the SSIS setup but is more of a guide to what you need.

Some things that are important and also recommended from the SQL CAT team when it comes to SQL Azure.

1) Use multiple streams. Cut your data up into multiple packages or data flows and run concurrent loads

2) Manipulate your network packet sizes given the concept of pushing data out to the internet ( that is the best way to say it)

3) Think about indexes. If you have a ton of indexes, consider disabling them, dropping them and so on for a data load situation. This is the same with any data loading event.

“Loading data to SQL Azure the fast way” is a great resource for these and other testing and options to pay close attention to.

Lots of Data

The simple test of pushing data into SQL Azure is excellent for getting used to the slight differences and dealing with throwing data out into the cloud. The problem you run into is going to be volume of data. This is where the typical trial will cause some suffering on performance.

Recently, I had the chance to finally load a real life amount of data into SQL Azure and I wasn’t all that happy with it. Now, before going on, there are some things that could make it better. More nodes in the cluster, a thicker network, possibly a design change to the table. The list could go on but we’re talking about real life and I wanted to share this experience in case you run into it and know what to be prepared for. Remember, this is pushing over the internet. We’re not hard-lined into a network so that must be taken into account when we say, “it was slow”.

The amount of data that needed to be loaded from a SQL Server instance to SQL Azure was 90GB, comprised of one table. The table was thin at 20 columns and a maximum row size of 233 bytes. This information is important when considering your packet size.

The test performed was in SSIS with 4 segments of the data needed to load.

The packet size was lowered slightly and use bulk load when available was checked. Also, the batch size was left at the defaults.

How long?

This was the painful part and really, why I’m writing this blog – prepare yourself.

The SQL Azure destination had no indexing on it to enhance the ability to load the data. The schema was identical as well so the data flow was a direct, source to destination (as shown above in the image). The rate of loading the data was around ~500,000 rows per 14 minutes. Minutes is not a typo. Yet again, this is pushing over the internet to the cloud.

Given this rate and the volume that needed to go up being around 260,000,000 rows in the first load, the estimated total time for this task was around 121 hours. Whoa!!! There’s some planning that needs to be done here, resources increased, horses kicked…something.

Summary

This article wasn’t intended to show you a step-by-step SSIS setup to load data to SQL Azure. Truly speaking, it is just another data source. What we did take away is the time that is needed when you are loading a massive amount of data to SQL Azure as part of either a migration to the cloud, integration of historic data or other needs that have pushed your data into SQL Azure.

Plan for these lengths of time, take the options that need tuning into consideration, look at testing other load methods, and make sure the other cloud options are investigated.

The next step that will be performed is a load into RDS to see if there is improvement. That will be posted in another article and should give a good view into the choices available.

Lastly and to reiterate, this was a large volume data load as an initial setup of SQL Azure as a source. Typically, we do not push 90GB tables into the cloud daily (not sure you could) so take this as an article outlining the need for really paying attention to your planning and designing when converting over to SQL Azure, if that has been a decision that was made.

Original post blogged on LessThanDot.

SQL Sentry - Providing Performance and Knowledge

Ted Krueger (onpnt) — Thu, 21 Mar 2013 07:51:00 +0000

If you’ve read my past blogs, you’ll know that I believe SQL Sentry Plan Explorer is absolutely a must for assisting in execution plan review, tuning and troubleshooting. Recently, SQL Sentry went to the next level with the services offered in Plan Explorer. The next step was to provide an easily accessible feature to one of the best execution plan and optimizer minds I know in Paul White, but a community view for guidance in assisting with execution plan questions. The feature is built into Plan Explorer and allows you to upload an execution plan directly to SQLPerformance.com. This site offers Q & A, accompanied by a description of the plan and comments, and questions or inquiry of how the exact plan or operations are working.

I’m not going to run through actually performing an upload because Aaron Bertrand already has a detailed article over on SQLPerformance.com on how to upload and review your plans on the site. With Aaron’s article, I didn’t want to just rewrite something that is already perfectly outlined, as Aaron always does. What I do want to say is, the feature being handed out by SQL Sentry within Plan Explorer is extremely valuable in a lot of ways, but most of all, it shows the commitment SQL Sentry has for delivering a truly useful product and ensuring everyone has access to the assistance we need to truly result in the most optimal execution plan performance possible. This is an easy extension of resources that expands an individual’s or team’s ability to resolve and learn about the optimizer and how execution plans are created, work and can be manipulated.

If you haven’t downloaded Plan Explorer, give it a try. If you find yourself not wanting to work without it, give the Pro edition a try next. You won’t regret it. You also will have an extremely hard time working with execution plans in SSMS once you start using Plan Explorer Pro.

To read more about Plan Explorer and the Pro features

A Glance of Plan Explorer Pro’s Deadlock Analysis

SSMS Add-in for Plan Explorer Update

Plan Explorer Pro – First Glance

Original post blogged on LessThanDot.

Setting up Mirroring to SQL 2012 Availability Groups Encryption Error

Ted Krueger (onpnt) — Wed, 20 Mar 2013 11:43:00 +0000

With SQL Server 2012, the power of Availability Groups is arguably one of the best high availability, disaster recovery, reporting, and impact-offloading features to be released. Administrators can now offload reporting to read-only mirrors, offload backup tasks to secondary replicas, and geo-cluster and send their replicas out with asynchronous capabilities for disaster and recovery. One extremely useful method for upgrading SQL Server 2008 or 2008 R2 to SQL Server 2012 is to utilize mirroring. This lends itself to as little downtime as possible when the switch to the SQL Server 2012 database is actually performed.

With all of the availability group power added, it is common to take advantage of the new features and implement them outside of other databases that may still be on 2008 and awaiting an upgrade plan. Since availability groups and mirroring utilize the same endpoint, there is one key setting that needs to be altered in order for you to successfully setup a mirror to an existing availability group – encryption.

When setting up availability groups, the default endpoint encryption is AES or Advanced Encryption Standard. However, with mirroring prior to 2012, the endpoint that is created by default is RC4 or Rivest Cipher 4. It’s important to note, this is more than likely due to RC4 encryption being deprecated , as pointed out in the note contained in the linked article.

The difference in encryption types can cause a problem. If you have an existing availability group and the default endpoint Hadr_endpoint created, and then attempt to create a mirroring session to a database not in the availability group for upgrade purposes while letting the wizard create the default mirroring endpoint or preexisting mirroring endpoint, the encryption difference will cause the error shown below.

The error above can be caused by several things. In fact, mirroring is known to be a struggle to troubleshoot given the multiple possibilities. The mirror may not be rolled forward enough, the communication may be a problem between the instances, the security not set, and so on.

With this exact situation, the endpoint set for NODE1 is an availability group created endpoint and set to an encryption of AES. The mirroring endpoint on the principal is set to RC4. Given the encryption difference with these endpoints, the mirror is unable to establish a successful synchronization. This is also common in mirroring when a setup is performed with one side, the principal, having encryption checked but the mirror does not have encryption specified.

To fix this problem, the best solution is to not tear the availability group down by removing the endpoint or altering it. It is better to start with mirroring, since the design and implementation of the availability group is more than likely a production situation. To change the mirroring endpoint, run the ALTER ENDPOINT statement. First, script out the Hadr_endpoint in your availability group setup to ensure you have the right encryption algorithm.

Right click the endpoint in SSMS and choose, script to new query window.

Code: tsql

CREATE ENDPOINT [Hadr_endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

Now, on the database server that is pre-2012 that you wish to setup mirroring on to use as an upgrade method, run the ALTER statement below.

Code: tsql

ALTER ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

After running the ALTER statement, stop and start then endpoint

Code: tsql

ALTER ENDPOINT [Mirroring] STATE=STOPPED

Code: tsql

ALTER ENDPOINT [Mirroring] STATE=STARTED

Note that this will interrupt or change any other mirroring that you may be utilizing. If you have mirroring being utilized on the 2008 instance, it would be best to look to the availability group endpoint as a change. Ensure that no other data services are interrupted by this change. If other data services are interrupted, the concept of the upgrade with minimal downtime is negated by forcing a loss of time in preparation.

Once this is completed, mirroring from 2008 to the 2012 instance with the availability group will start successfully.

Summary

Using mirroring as a tool for upgrading databases that require high uptime is a great and highly successfully method. While all the primary administration and preparation work can be performed behind the scenes with little to no interruptions to the data services and the actual upgrade task is performed in seconds. While using this powerful method for upgrading to SQL Server 2012, if you run into a situation where availability groups have been set up and you want to upgrade a SQL Server 2008, ensure the same encryption from the mirroring endpoint to the Hadr_endpoint is being utilized. This will prevent unwanted issues when configuring your mirroring state in preparation for a minimal downtime upgrade path to SQL Server 2012.

Original post blogged on LessThanDot.

Red Gate Releases SQL Backup Pro 7.3

Ted Krueger (onpnt) — Wed, 20 Mar 2013 09:57:00 +0000

Red Gate recently released a new version of their great product, SQL Backup Pro. The one thing about this version that may get your fancy on is the buzz around cloud storage. For most administrators, both DBA and Domain alike, the cloud has offered a great deal in terms of disaster and recovery by throwing just about anything out there that you need offsite and is critical to ensuring a safe recovery point. With this new version of Backup Pro, I can see the tool becoming even more widely used for it’s already ease of feel to getting your backups in SQL Server done and safeguarded. I have been around long enough to have used LTO tape for olouffsite storage and have felt the pain of getting those tapes back in the event of a data disaster. No one likes that situation. No one! Cloud type storage has thrown the tape days to the curbs (although, still have merit in tape) but with tools now integrating and services backups to a cloud based service, our options are greatly improved.

As with most great products out there that I strive to use to make sure my work is efficient and stable, SQL Backup Pro is available for a trial version so you can give it a try yourself and see if it fits with your SQL environment.

Some key points from the Redgate’s site

With SQL Backup Pro 7.3, you can quickly, simply, and securely upload and store a copy of your backups in the cloud, protecting your data from onsite disaster.

Get backups off site quickly and simply, without the hassle of tape
Recover your data whenever you want
Manage your backups online

You’ll need a Hosted Storage account, SQL Backup Pro 7.3, and that’s it - we’ll give you your first 5GB of storage free.

Look for a review to be coming soon from myself and possibly other Lessthandot bloggers on SQL Backup Pro 7.3.

Go give it a try. Red Gate has always delivered and they are keeping to that with additional features like this service.

Original post blogged on LessThanDot.

Windows and SQL Server 2012 Test Setup

Ted Krueger (onpnt) — Fri, 15 Feb 2013 23:45:00 +0000

SQL Server 2012 Test Setup

I’m often asked what I would recommend for a development or test environment on a local machine for working with SQL Server 2012. Often, requirements increase or change with the next, greatest version of SQL Server but when it comes down to it, for testing, this setup is the same for SQL Server 2008. Keep that in mind if you are in need of a 2008 solution and not ready for 2012. This article will describe my setup. There will be some follow-up articles that will go through the steps that are needed to mimic the setup.

So, what is the setup?

I first have to consider what I’m in need of testing; configuring and scripting those configuration changes out, developing for, collecting performance or, simply, looking into the internal mechanisms of SQL Server and features. This leads to having not one but several virtualized configurations available.

I place these into two categories.

SQL Server Setup/Configuration/Operations
SQL Server Integration/System utilizations
I said 2 but 3 retains both 1 and 2 for articles, presentations and blogs so it deserves a 3.

This covers many situations where SQL Server would be needed. Especially as a consultant, preconfiguring a setup is a must. Recording that setup is even more important. This setup is close to a “real-life” configuration (with the exception of more nodes added). So this could be utilized to run through an Availability Group configuration, Cluster configuration, Cluster failover testing, Availability Group testing, or more. It also provides me with space for testing SSAS, SSIS and SSRS with SharePoint from configuration to deployment and development. Truly, there isn’t much we couldn’t do with this other than older version support.

One important note: make clones of all your stripped setups. This means, get Windows Server 2012 setup – clone it, get Server Core setup – clone it, get SQL Server 2012 SP1 setup – clone it, get a Domain setup – clone it, get Windows Admin machine setup – clone it. Save those clones off on an external drive. When the testing causes issues in recovering the initial setup, simply put it back to a snapshot or put the full clone over the current virtual machine. Just remember, configure the network settings, DNS and so on before cloning and making snapshots. This will make replacing machines easier and quicker. They will simply drop in and start working together again.

SQL Server/Configuration/Operations

This encompasses the need for one or more SQL Server instances at my disposal for determining configurations that I would need such as Availability Groups or digging into a performance problem and stressing a system while tuning problems.

This setup is as follows

Or, as it appears in VMWare

Of course, not all these virtual machines are running nonstop. Allocating enough resources to them on a laptop would be next to impossible, if they were.

The setup contains a 3-Node cluster running Windows Server 2012 Server Core, SQL Server 2012 SP1 and availability groups set up for synchronous commit to Node-2 and asynchronous commit to Node-3. Another machine is set up and acting as a domain controller and file services. All the database files are placed on the server set up for file services. This way, the Server Core machines are constrained to resources they will only need to ensure the task being performed, will be successful.

As a second stage or area of this setup, 3 other nodes are configured and running Windows Server 2012 and SQL Server 2012 SP1. This is due to Server Core simply not being able to perform some tasks that are needed.

The last part of this setup is a virtual admin control machine running Windows 8 Enterprise. This machine is a key piece to the entire setup. The admin control machine runs all server tools for configurations and monitoring needs while running any test or processing. The control machine also acts as a deployment machine to the cluster or SQL instance. The control machine is also needed more as the entire network this setup is done on is completely set on an island from the host network. With that, internet connectivity is more difficult to access or setup. While it can be done, I chose not to so the interference of that variable is not part of the work I am performing.

SQL Server Integration/System utilizations

This setup is almost identical to the first setup but it has a completely different configuration need and feature services installation performed. The cluster is set up the same: 3-Node, Windows 2012 Server Core and Full GUI, SQL Server 2012 Instance SP1. However, this installation is configured with services like SSAS, SSRS and SharePoint over the SQL Engine. Each SQL Server instance takes on the configuration needs that those services require while utilizing SQL Server. The OS is configured slightly differently as well as the VM client and resources allocated. In all, the setup is a clone but requires a different level of configuration.

How the **** do you run all that?

Of course, all this running on a regular laptop is rough to chew on. Luckily, Solid State Disk (SSD) has come down a great deal in cost in the last year. In order to truly get this setup to function well, you’ll need a laptop maxed out on RAM (16 to 32GB) and optimized with one or more SSD drives. For my setup, I run an SSD in a drive caddy instead of having a DVD ROM. Not much comes on DVDs these days, and over the last 4 months, I have not had to plug the DVD ROM into a USB port once. In addition, I enlist one external SSD drive and another external HDD. The SSD holds as many virtual machines as I can manage on it. The slower HDD drives are used for file storage, database file storage and anything that I can manage to use on slower disk. Much of the integration/system utilizations (a fancy way of saying, applications) run on the HDD drive.

All of this is separated from the local HDD that is running the actual laptop. It’s important to manage the resources much like managing a virtual server – host to client. My host, the laptop, is managed as much as possible so it will not consume more than 6-8GB of RAM and everything is set to the C Drive, or local HDD that it came with. At some point, the primary will be converted to SSD, but for now, that isn’t in the budget. As much as the host is monitored for resource usage, the clients are as well. They are started and stopped only during the times I need them. For example, the domain controller could be set to run in the background but that would consume 1.5GB of RAM and make the IO on the caddy SSD work a little harder. There is a local instance on the primary of SQL Server that still utilizes the caddy SSD for anything being done like working with execution plans, designing databases and so on. So there is a line that is crossed. That is the game of using all the resources as efficiently as possible. Since I have the ability to turn things off and on, it makes it easier not to choke the laptop altogether. That being said, all local services are always set to manual startup. There will be no rogue instance on this machine. It simply cannot handle that occurrence or it may be starved to death (or, blue screen).

Windows 8 Pro

With everything I’ve mentioned here on how I set my laptop up for what I need, there is one important part to all of this: Windows 8. Windows 8 has provided me with a much easier to manage experience with all of this. There is a nice separation as I move between the virtual experience and back to the laptop itself. I would have to say, Windows 8 has provided a minimum of a 20% increase in my overall productivity working with a laptop 24/7.

What’s up next?

As a follow up to this, I’ll run through setting up one part of a 2-Node Server Core 2012 cluster, SQL Server 2012 HA, domain controller, and Windows 8 admin control machine. If you are limited to a less beefy laptop, you can get away with the 2-Node setup for doing most of what you need. With resources, if they get to a certain level, we need to move to either a standalone machine or a server chassis. Remember, these are just laptops. This will not be restricted to just these setups either. Take advantage of virtualization and make a virtual machine of as many servers and machines you may run into. One 500GB external HDD can contain Windows 2003, 2008, 2012 Servers, SQL Server 2005, 2008, 2012 instances, Windows 7, 8 machines, Domain Controllers, File Servers…you get the point.

If you can’t wait for the articles showing the stages of setting this up, the below links will get you through the complete setup. These links were collected during the process of setting things up and proved valuable to completing it.

Original post blogged on LessThanDot.

Define Microsoft Business Intelligence for me?

Ted Krueger (onpnt) — Tue, 29 Jan 2013 20:45:00 +0000

What is Microsoft Business Intelligence? I’m not asked this question very often because it’s typically an assumption. As we know, assumptions can lead to misguidance or misrepresentation of a topic. This topic has been defined in so many different ways; I ask it in almost every interview I perform that has the word, “Data” in it. The reasoning for asking that question isn’t directly to say a person is wrong or right but to see the range they’ve taken on in knowing all the factors that lead to end solutions in data.

First, I should probably answer the question as it is, from my own experience with Microsoft Business Intelligence.

Microsoft Business Intelligence is a set of tools, features and services that help business visualize data. The evolution of data in which it transforms from raw data to an analytical view or visualization of data, is the overall process of both data services and business intelligence. In the concept from raw data to analytical or visualization, business intelligence lies on the right side of the model – analytical and visualization. With Microsoft, this encompasses technology such as PowerPivot, Power View, SQL Server Analysis Services, SQL Server Reporting Services, SQL Server Warehouse fundamentals (a cool way of saying, OLAP designed data storage) and pieces of SQL Server Integration Services.

As we can see, my definition does not have much revolving around SQL Server form a performance stand point. The word performance lends itself to many areas, such as database design, SQL Server setup/configuration/maintainability, ETL and scalability.

A great example of how these two topics relate but can be considered on a different level of focus is the path one or more OLTP designed databases take to an OLAP designed database. With Microsoft, we look to a normal on-premise landscape that consists of one or more transactional databases housed on SQL Server; a push and pull method using SQL Server Integration Services as a high performance extract, transform and load structure; ending with SQL Server Analysis Services in a cube type layout of the data after manipulation or, real time manipulation occurring.

Seeing this path, we’ve only touched on the Business Intelligence area with the ending on SSAS. Once this task is completed, the real BI landscape sets in with the visualization of the data. During this entire transfer, BI and Performance cross each other in a few areas. To best illustrate this crossing of utilization, we can draw a guide.

As we can see from above, there are two areas of overlap, SQL Server Analysis Service and SQL Server Integration Services. This comes in because both have a performance aspect and a BI aspect to how successfully they will be utilized. SQL Server Analysis Services relates directly to how the data is stored and the analytical behavior it takes on pulling calculated fields into a visualization form. A poor OLAP design can create a bottleneck or ineffective ability to utilize SQL Server Analysis Services without added effort on altering the data. This is even more relevant in SQL Server Integration Services. While performance is important in SQL Server Analysis Service, it is brought to an entirely new level in SQL Server Integration Services. The extraction of data requires a high performance method so the sources and destinations can withstand the high volume of requirements. SQL Server Integration Services is also highly accountable for in-process transformation or manipulation of the data. The transformation of the data is also a key area that requires being attentive to how it is done as it relates to performance.

Once we define what business intelligence is and can be for business, the next step is defining how it relates to implementing and structuring the design of a business intelligence implementation. Taking how we define business intelligence has a direct impact to how we gauge infrastructure and technology utilization, and skills utilization. This leads to cost effectively placing resources accurately as it pertains to budgets, purchasing of the right technology and services.

Summary

With any high level topic or focus point in technology, defining how we utilize it effectively from the correct impact of skills to hardware consumption will define the success of consuming the technology into your business.

While SQL Server Analysis Services and SQL Server Integration Services share resources from both performance and business intelligence, the two skillsets are unique in mastering their areas. Fully understanding one takes a great deal of discipline and motivation. Falling into a range of working on both can lead to a lack of fully representing the sides as best as they can be.

Original post blogged on LessThanDot.