SQL SERVER – Finding the Last Backup for All Databases – Notes from the Field #016

[Notes from Pinal]: During my presentation, I always ask developer a simple question – When did you take your last database backup? I get two different kinds of answers – 1) few replies with No Idea and 2) few replies with some date time. When I further ask if they are confident that their backup was taken successfully, I see confuse faces. Well, in this episode of Notes from the Field Tim answers the very same question with answer.

Linchpin People are database coaches and wellness experts for a data driven world. In this 16th episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains a very simple script to find when was last successful backup of all the database was taken.


As data professionals (those responsible for supporting database environments) one of our most important task is making sure that we have proper backups. I regularly have to perform audits of SQL Server environments and one of my first checks is to make sure that backups are being performed.  A quick check is to see when the last full backup was made.  You can do this by running the following script.

SELECT  a.Name AS [DB_Name],
COALESCE(CONVERT(VARCHAR(12), MAX(b.backup_finish_date), 101), '-') AS LastBackup
FROM    sys.sysdatabases a
LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = a.name
GROUP BY a.Name

This will only tell you when the last full was completed. If you are running daily full backups then this will be sufficient. The script I personally run can be located on a recent blog post. This post includes a script that will get the database name, recovery model, most recent full, most recent differential, and the last two transaction log backups.

Anytime I talk with someone about backups I have to stress the importance of validating your backups. You need to have a process in place to regularly validate your backups by restoring them to another environment. Backups that can’t be restored when you need them are useless.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Note: Tim has also written an excellent book on SQL Backup and Recovery, a must have for everyone.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Expanding Your Skills – Notes from the Field #015

[Note from Pinal]: This is a fifteenth episode of Notes from the Field series.  We all want to learn more and progress in our career. It is human nature to look for change and variety after a while. In most of the cases, it is quite easy to move inside your organization if you are an expert in the skills of the other technology. For example, if you are a developer and have the skill set of DBA, you can easily switch to that job if there is an opening. However, the biggest challenge which we all face is how to keep ourself updating with new technology and expand our skill set when we are so busy doing our day job.

In this episode of the Notes from the Field series database expert Brian Kelley explains a very crucial issue DBAs and Developer faces when they want to advance in their career and expand their skills. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


I’m sure the client was rather surprised by the questions I was asking. I was brought in to help consult on their SQL Server security and I was asking questions about OS practices, about intrusion detection, about firewalls, about their networks. We were 30 minutes into the call and I hadn’t gotten to SQL Server yet. Knowing that the question, “Aren’t we going to talk about SQL Server?” was building in the client’s mind, I simply stated, “I know you’re wondering why I’m asking so many questions that aren’t related to SQL Server. I’m trying to assess the whole environment because SQL Server is just one part of the system. A weakness in any part of the system could lead to the breach you don’t want.” There was an audible sigh as the client understood.

As SQL Server DBAs and database developers, we are charged with understanding how SQL Server works. However, as IT professionals, it behooves us to be more rounded. SQL Server is a part of the overall system. It’s an important part, because our systems are data driven and SQL Server holds the data. However, understanding more about the other pieces of the system helps not only in security, but in overall operations and troubleshooting.

When I speak about professional development, I suggest DB pros increase their skills in the following areas:

  • Learning the basics of how the Windows operating system functions
  • Knowing and being able to write in a scripting language
  • Understanding how TCP works, how basic routing functions, and how name resolution (DNS) is accomplished.
  • Developing a rudimentary understanding of the hardware components of servers.
  • Practicing and becoming proficient on performance monitoring beyond just the application or SQL Server.

The majority of people can’t attack all of these at once. My advice is to pick one, bring it up to an acceptable level of competency, and then move on to the next area. So how do you start? Andy Leonard, speaking of SSIS, suggested understanding the resources you have available to you and then putting in the work. I agree with that, but I am going to expand on it. Here’s a reasonable action plan:

  1. Get some introductory resources on the subject. These could be books, articles, free training, paid training, or anything that begins to expose you to the subject. Don’t worry if some or a lot of what you’re looking at you don’t understand. What you’re trying to do here is get more familiar with the subject so you can figure out what you don’t know.
  2. Continue to investigate potential resources so you can dive deeper and learn more.
  3. Develop an environment for you to practice. This could be a new set of VMs, it could be a laptop that you only use for whatever it is you’re practicing, but outfit yourself with what you need to get started.
  4. Identify what it is you need to learn.
  5. Develop steps to cover those items. Ensure you include adequate practice for what you’re learning. Also include milestones to celebrate in order to help maintain your motivation.
  6. Put in the work!

Also, be flexible. Be ready to adjust your plan at any of these steps. However, don’t be too flexible. In other words, don’t adjust without a good reason. Just because something is the “new shiny” is not a good reason to adjust. Learning that you missed something important in your research that you should know, well, then you better adjust your plan. But most of all, keep pushing forward.

If you want to get started with performance tuning and database security with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SysTools SQL Recovery Software – An Experiment to Recover Database Corruption

NOTE: This was my experiment with database corruption. If your database is corrupted, you should contact professionals who do this as their primary job. Always make sure that you do experiments on the copy of your database on your development server, rather than on production server.

SQL Server helps to maintain the pace of managing database orderly, following the step by step procedure of updating and handling the Structured Query Language. Duties and responsibilities of database administrator are ever changing as well as challenging from the proper implementation of the system process of being prepared with the recovery solutions for cases of emergency. DBA (Database Administrator) should make sure that the database gets recovered and restored if error messages trouble or corruption issues are reported in the Server. SQL database may get corrupted, inaccessible or even dropped for what so ever probable reasons. The efficiency of an SQL Server admin is calculated according to the reliability, completeness and perfection exhibited by the recovery process they selected. SQL Recovery software can prove to be a perfect solution for most of the SQL MDF and NDF related corruption issues.

SysTools SQL Recovery software is the tool of choice by many SQL DBA because of the multiple functionalities it exhibits while resolving the troubles with SQL database files. The main peculiarity of this tool is that it succeeds where even the inbuilt disaster recovery options for MS SQL server fails. The procedures usually practiced by DBA after SQL database corruption is declining towards failure because of the complex, incomplete steps they provide. The disaster recovery points set by Microsoft® for SQL server like Database mirroring, Transactional replication/warm standby server, Failover clustering, or the most preferred backup and restore feature is reported to end up in disaster if attempt to restore the database went wrong. Here comes the relevance of SQL Recovery software that works with perfection with all the user friendly features added to make SQL MDF/NDF file recovery successful one.

What Makes SysTools SQL Recovery Software One Outstanding Solution?

SQL Recovery software has been selected the most amongst many other names available in the online market belonging to the similar arena. The tool holds accuracy of provisioning SQL database recovery owing to its features and updates. Capability to restore database from corrupt to healthy format even after the encounter of error messages related to the issues like database not mounting, damaged RAID Strip, etc. Also the potential of repairing the database out of failure in Raid Rebuild attempt, RAID setup, etc. with regard to RAID controller and RAID array along with software/ hardware troubles, file system/OS failure etc. is amongst its greatest praise. Some of the software qualities have been listed with the vision of making it easier for SQL Server Administrators or users to confirm the best possible solution for their database recovery.

Performance Enhancers

  • Quick and Advance Scan feature to make scanning of MDF/NDF file specific. For normal file corruption the option for quick scan is suggested, but for severe corruption issues the advance scans is the one to select.
  • MDF and NDF database file recovery process is possible for the SQL server version 2012, 2008, 2005, 2000 and 2008 R2.
  • Advance scan and repair process offered for tackling with both; primary and secondary database corruption / inaccessibility.
  • Deleted SQL database recovery is also offered (tried and tested) with the assurance of database recovery along with maintenance of processing accuracy without data loss.
  • Recover SQL Rules, Triggers, Stored Procedures, Functions, Views, Rules, Triggers, primary and unique keys.

Potentiality At Recovery

  • Save database scan as .str files to face situational needs where either the process got interrupted or was intentionally intervened due to the large size.
  • Auto detection of SQL Server version made the process run without any trouble of platform mismatch.
  • SQL Server compatible SQL scripts can be created in case the database has to be created in the absence of SQL Server setup.
  • Recover SQL database in case even if the DBCC CHECKDB command fails to give accurate results.
  • XML data is also recoverable if XML type columns or variables are created in SQL Server.

Software Pros Reflecting Facilitative Proficiency

Erroneous Corruption State

Error based corruption is the worst of all as compared to others, especially when the messages don’t reflect the cause of occurrence while pose a database inaccessible. Recovering data from such circumstances using this program is effectual due to the guarantee of recovery it extends to users even after a delay at being attended. Delay caused at attending error based issues encountered in SQL Server lead to severity, thus, diminishing the probability of recovery; which certainly isn’t the case with SQL database recovery program.

Damaged Allocation Pages

A lot of complexity is involved in recovering allocation pages while the most challenging thing about it is that; an entire database has to be repaired for a single allocation page to be recovered from the damage. The SQL MDF recovery program is well equipped with the convenience and freedom of recovering tables, views, triggers, etc. Selectively as per the requirement is. This helpfully reduces the Recovery Time Objective for database recovery without compromising on Recovery Point Objective.

Database In Suspect Mode

An SQL Server database gets caught up into suspect mode in the extreme of cases due to; corruption or severe internal damage. SQL Server takes this step in order to maintain the consistency of transaction under the Relational Database Management System’s ACID property. But fortunately recovery from even this case of damage has been tried and tested with the SQL database recovery program with successful results.

NOTE: Unlike other recovery methods; while using SQL Recovery program, Server downtime won’t be faced as it is an independent application that is executable without SQL Server availability. Hence, you can continue working on the Server (with other databases) while the recovery is in progress.

The tool is capable to resolve most of the corruption issues that could possibly take place in an SQL database like: schema corruption, consistency corruption etc. Taking any scenario where DBCC CHECKDB command fails the SQL Recovery tool helps to recover the data maintaining integrity in intact form. Manual SQL database repair trouble can be resolved with the user friendly interface proposed by software for SQL Recovery.

NOTE: Free demo setup of the software is meant for trying out your hand on the tool before going to purchase its full version to gain confidence of performance perfection. However, it is limited to save only the STR file and show a preview of the entire scanned content of MDF/NDF files. To export and save recovered data, full version license is apparently required.

How Software Proceed to Resolve SQL Database Corruption?

Once the software is downloaded and installed in any of the Win OS versions, users can run it following the default path as:

Start>>All Programs>> SysTools® SQL Recovery Software>>SysTools® SQL Recovery Software

  • Select option to open MDF file from the system location it is saved at.
  • To load the damaged MDF file browse the location, select the file and then Click Open.

MDF and NDF file recovery is possible for that users need to customize the options:

  • Under tab for Scan Options, Select Auto detect SQL server file (. mdf) version to let tool, identify the file version or in case if you know the current version you can manually select the SQL Server version (make sure you select the correct version of SQL Server because wrong selection can result in inconsistent results.)

  • Under tab for NDF Options, select “Let Me Choose SQL Server Secondary Database Files”, select options to Add files, Folder or Remove File respectively according to the requirement and then Click OK.

  • Browse, locate, select NDF file and Click Open.
  • Later, the process report of automated MDF file scanning can be seen.
  • To save time and effort of scanning large MDF files, user can save the scan as STR files. If want to get .str files, click Yes, otherwise go for NO and proceed to the next step.
  • To save the STR file, select a desired location for its storage. Click Save.
  • Preview tables, stored procedures, triggers, rules, etc in the preview screen for MDF/NDF files.
  • Select Export option to save recovered database items from MDF and NDF files.

To save the SQL database files safely in healthy format, customize the options:

To export and save data as MDF file:

  • To export recovered data as MDF format go for “export as SQL Server database”.
  • Select “Create Destination Database”.
  • For SQL Server authentication, fill in the database credentials i.e. server name, database name, user name, password.
  • Select export feature as “with only schema” or “with schema and data”.
  • Click Export/Save option.

To export and save data as SQL Server compatible SQL scripts:

  • Check and select the desired component of the SQL database file that you want to be exported.
  • “With only Schema” or “with Schema and data” you can export SQL database.
  • Click Export/Save option to export recovered data as SQL Server compatible SQL scripts.
  • Browse location to save the output SQL file and click OK.
  • Get progress report of SQL scripts saving process.

Click OK to complete the Export process.

Secondary Measure For SQL Database Recovery

In case the time taken for recovering corrupt SQL database doesn’t suit you and a healthy LOG file for the same MDF is available then; try SysTools SQL Log Analyzer. The application helps analyze transactions performed on a database and recover for which it employs the usage of the corresponding LOG file. The combination of a log analysis and database recovery program helps in reducing the Recovery Time Objective to a lower extent as compared to that of the SQL Recovery program.

Have a look at its processing technique:

  1. Log file along with the corresponding MDF file is required for recovering the database through thorough analysis of its transaction.

  1. The software automatically loads the corresponding SQL database for the LDF file you browse for, in case both the files are available in a same folder. Also, you can browse and add the associated MDF file manually in case of its availability in a different location.

  1. Transaction details of the selected Log file are listed soon after the scan is completed, letting you preview all the operations performed on the associated database.
  2. Meanwhile, for exporting database, you are provisioned with three respective options:
    1. SQL Server Database (If SQL Server is available)
    2. SQL Server compatible SQL Scripts (Unavailability of Server)
    3. CSV (For transaction log details)

End Observation And Conclusion: With the widespread usage of SQL Servers worldwide, especially by large scale organizations makes downtime of the Server due to damaged database unaffordable. Meanwhile, SQL Recovery and SQL Log Analyzer both are predominantly dependable for resolving cases of MDF / NDF file corruption. A wise selection is all what it takes to overcome the trouble caused by an inaccessible SQL Server database.

NOTE: This was my personal experiment with database corruption. Always make sure that you do experiments on the copy of your database on your development server, rather than on production server. Once verified that your database is 100% recovered, you should experiment on your database. This blog takes no responsibilities whatsoever.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Learning SSIS – Where Do I Start? – Notes from the Field #014

[Note from Pinal]: This is a new episode of Notes from the Field series. SQL Server Integration Service (SSIS) is one of the most key essential part of the entire Business Intelligence (BI) story. It is a platform for data integration and workflow applications. As wikipedia says – It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data.

In this episode of the Notes from the Field series I asked SSIS Expert Andy Leonard a very crucial question – How to learn SSIS and where do we start with it? Andy was very kind to answer the questions and provides plenty of information about how a novice developer can learn SSIS from the beginning and become expert in the technology.


Where Do I Start?

I often meet people who want to learn SSIS but don’t know where to get started. There are several great places to learn SSIS.

Articles

I recommend the Stairway to Integration Services at SQL Server Central, but I am clearly biased – I wrote it (and I’m still writing it…). The stairway is a systematic walk-through laden with images which, I believe, helps when learning something new.

Books

There are lots of good books on SSIS. You can learn something from all of them. At Amazon, you can find an assortment of books by the major publishers such as Apress, Microsoft Press, and Wrox – as well as self-published works – by simply searching for SSIS.

Free Training

There are a number of online resources where you can obtain free training. Individuals and companies offer training on YouTube, Vimeo, and other online video websites. The Professional Association for SQL Server (PASS) stores recordings of Virtual Chapter meetings and various presentations.

You can also obtain free training at events such as SQL Saturday or local SQL Server user groups. The Linchpin People blog contains some free SSIS video training.

Free training is usually good training, but not always. One caveat about free training: You often get what you pay for.

Paid Training

You can opt for professional training delivered in-person, remotely, or via video. There are several sites that provide excellent SSIS training via video. Some are subscription services for which you pay a yearly or monthly fee. Others allow you to purchase a course of collection of courses.

In-person training is usually the most expensive option. Why? Remember: you get what you pay for. Many students who attend the Linchpin People SSIS training courses report learning more during the Day 1 morning session than they knew entering the classroom. In-person training is the best and fastest way to obtain a working knowledge of SSIS.

Why So Much?

So why does in-person training cost so much? First, the curriculum must be developed. This may sound trivial – putting together some slides and talking points and a handful of exercises – but it is not. I spent almost a year developing the labs for Linchpin People’s course: From Zero to SSIS. Why so long? Several reasons. I wanted the content to:

  1. Represent real-world data (No one’s data is as clean as AdventureWorks).
  2. Build from a central starting point (spiral-out).
  3. Emphasize the techniques and SSIS components and tasks you will use as an SSIS Developer, not deliver a survey of all SSIS components and tasks (some of them don’t work very well, after all).

(Shameless plug: I am personally delivering From Zero to SSIS 19-23 May in Reston Virginia, and SQL Server Integration Services 2012 Design Patterns 8-11 Sep in London, England.)

Time and Hard Work

There is no substitute for spending time working hard to learn anything, and SSIS is no different. In fact, learning SSIS will take more time than learning almost any other technology. Why? Integration Services is a software development platform for moving data. Learning it well requires understanding both database technology and software development.

As I tell students in my SSIS training, “If it was easy, anyone could do it.” Many people are not interested in learning something difficult. The result is there are not enough SSIS developers to do all the available SSIS work. So if you learn SSIS, you should enjoy some measure of job security.

Working in Information Technology requires a commitment to lifelong learning. You should not expect to be able to learn one version of SSIS – or one version of any technology – and be able to work in that version of the technology for the next 20 years (you will be fortunate to be able to work in a single version of a technology for 20 months!). If you don’t like “keeping up,” Information Technology is not the right field for you.

You will need to invest time to learn SSIS, and you will need to continue investing time to improve your SSIS skills and keep up with changes in new releases of the technology.

Conclusion

You can learn SSIS. You need to make up your mind that you are going to learn it and let nothing stop you. Treat each obstacle as “something to overcome.” Accept no shortcuts. Do the work. Put in the time.

I have the utmost confidence in you. You can do it.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – 9 Things You Should be Doing with Your Backups

SQL Server backups are essential for any business that store their data in SQL Server. The following is a list of best practices you should be following if you are the person in charge with maintaining your organization’s databases.

1. Backups should not be stored in the same physical location as database files

This very simple rule will save your business from a lot of difficult situations in the event that a physical drive becomes faulty.

In case something like this occurs, you sahould have the possibility to use other drives or to remote to a different location that has stored a backup of your database so that you are able to restore your database from that location.

2. Backup schedules are set up

Another good safety precaution is for your backups to always be accompanied by proper schedules which have been established to meet the application needs and your particular business requirements.

Creating backup schedules is very important because as time passes and your backups get older, the risk of data loss becomes higher, unless you are protected and you have a way to reestablish all the data up to the point of failure.

Backup schedules will provide you a consistent and constant history of your data which will always be useful, not only in disaster situations.

3. Test the restore procedure of your backup on a test server

You should always try to restoring your backups on a test server and make sure that you can recover all of the data with all the options you need to use during a restore on the main server.

Just because a backup process has finished successfully, this will not guarantee that the backup can also be restored. You might not be aware that your backup was not created correctly, and when trying to restore it on the main server, the process might fail due to media corruption or other factors.

4. Make use of all available verification options when doing backup

Another good practice is to use all options provided in the process of backing up your database in order to make sure that the end result is a transactionally consistent backup.

If you are using T-SQL scripts to back up your database, then make sure that when using the BACKUP command you also add the CHECKSUM parameter, in the WITH clause, which will imply that each page will be passed through a checksum operation to ensure the consistency of your backup on the backup media.

BACKUP DATABASE MyDatabase
TO DISK = 'Z:\MyBackups\MyDatabase.bak'
WITH CHECKSUM;

If you prefer doing your backups manually through a visual interface like the one available in SQL Server Management Studio (SSMS), make sure to check the first two checkboxes in the Reliability section, on the Options page.

These two options, Verify backup when finished and Perform checksum before writing to media will add a level of verification to your backup process which will ensure your backups are consistent.

If you choose a different wizard to do your backups, like the one available through SQL Server Maintenance Plans, make sure to tick the box that corresponds to Check Database Integrity option.

A different piece of software which I prefer to use to use for this operation, mainly because of its simplicity in use, is SQLBackupAndFTP. It has all the most important options in an easy to access and intuitive interface.

5. Do a FULL backup daily

Based on the needs of your organization you should choose a recovery model that will allow you to protect your organization’s against data loss. If your organization can afford to lose 15, 30, 60 minutes worth of data, choosing a simple recovery model is the option you should go for.

In this case, having a FULL database backup is the best method of protection against data loss in any data protection plan and in conclusion, most cases would require it to be performed daily, despite the overhead added by the time required for such an operation.

If your organization’s databases are small with easily recoverable data, then a FULL backup should be the way to go.

As I am already using SQLBackupAndFTP for my backup process, I find it very convenient that it has, by default, set its backup schedule to do a FULL back up every 24 hours.

This is very helpful and this way the risk that a detail that is most important will be eliminated.

The biggest advantage of this best practice is that the recovery is easier as the backup is just one file and no database log is needed, while the downside for this type of backup is that data that can be recovered is only up to the time of the backup and depending on the size of your database, it can take up a lot of disk space.

6. Do differential backups more frequently

If you consider doing FULL backups are too expensive for your organization, from a resource availability and data loss point of view, there is the option of doing differential backups of your databases, which can and should be done more frequently than FULL backups.

In terms of advantages, differential backups will take less time to complete and also less disk space as they contain just the data from the last FULL backup operation. No database log is needed in this case either and the restores are more precise in terms of data.

The disadvantages of this would be that still, the data that can be recovered is only up to the time of the restore and the restoring process is a bit more complicated as there are two files involved.

7. Do transaction log backups even more frequently

The following most important step in doing backups, after FULL and differential backups, would be to back up the transaction log. As the transaction log contains all the recent activity in the database, it could be used to make a restore of the database to a specific point in time, which is its biggest advantage.

Doing a transaction log backup also truncates the log, which keeps it from becoming full. Similar to database backups, transaction log backups can be done while the system is active.

If your organization has a high activity with some of its databases, doing a transaction log backup every 10 minutes is recommended, while other databases which are less active might have their transaction logs backed up every 30 minutes or 60 minutes.

The negative side of this type of backup is that the database is required to have transaction logging activated which will increase the size of the database and the effort required when doing the restore process.

8. Practice recovery operations

A successful company is flexible and quickly adapts to the changes in the market. In such a case, where business requirements could change unexpectedly, this could mean that your backup strategies can become obsolete.

Testing your backup strategies on a frequent basis and covering different scenarios that might appear, scenarios that include both system and individual database restores will ensure that your backup plans will have the expected efficiency and will work at the time they are needed.

9. Regularly back up system databases

Even though backup strategies will save you from losing user data, it is incomplete without a backup plan for your SQL Server system databases, master, model and msdb databases.

These databases are essential because they contain system configuration along with SQL Server job information which has to be restored in case of a total system restore.

You should also keep a strict and frequent plan of backing up your system databases, preferably on a daily basis, if you’re frequently changing instances. Otherwise, for installations which are more stable, you can do this backup operation with less frequency.

If you have already considered taking a look at SQLBackupAndFTP for your backup process, I still have good news regarding this software.

As you can see above, it also offers you the possibility to backup, along with your user databases, the system databases just with one extra click. You still have the options to back these databases up with a schedule and to your favorite location, be it on the same disk or in a different location.

Conclusion

If you are to follow these best practices in your backup process you will eventually find an optimum and the most efficient combination of steps to manage your organization’s databases. Software is here to help us and make our lives easier, while making us more efficient. This is why, for some of the steps above I recommend using SQLBackupAndFTP, which in my opinion has the most complete and easy to use set of tools necessary to manage your databases.

Also, by incorporating any of the steps above in your backup practices you are going to improve your organization’s efficiency against data loss and speed up the recovery process of your data.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Virtualized SQL Server Performance and Storage System – Notes from the Field #013

[Note from Pinal]: This is a new episode of Notes from the Field series. A common comment I often hear from the developers is – “I have virtual environment and I have followed all the tricks and tips which I should apply to SQL Server but still I do not see a performance improvement to my server. I believe virtualization is not a good thing.” The matter of the fact is that we do not know how to tune virtualized server. Regular SQL Server where we have dedicated server and virtualized SQL Server have few different tricks associated with them.

In this episode of the Notes from the Field series database expert David Klee explains a very crucial issue DBAs, and Developer faces in their career – how you can improve performance of overall server. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of David in his own words.


Out in the field, the sheer number of trouble scenarios that you run into is mind boggling. But, over time, you start to see a number of patterns appear, and you start to build this list of cause-and-effect relationships in your mind. The list of common patterns helps you be more efficient at troubleshooting different problems. In having virtualized SQL Server and other mission-critical workloads for quite some time now, storage performance is the number one recurring item on that list. Let’s discuss why this is, and how to get underneath the symptoms and into the root causes of some of these common themes.

Performance Collection

Paramount to any discussion on storage performance, in both the physical and virtual worlds, is performance statistic collection. What amount of I/O performance do your SQL Servers consume throughout the day or week? You cannot begin a conversation about good or bad storage performance without having a usage baseline to compare the current system state against. Use your favorite performance statistic collector (Perfmon is mine) and collect data around the clock. Use this data to start to trend your usage. Common Windows performance counters that you should sample for disk include:

Counter Group Counter Set
PhysicalDisk % Idle Time
Average Disk Bytes / Read
Average Disk Bytes / Write
Average Disk Read Queue Length
Average Disk sec / Read
Average Disk sec / Write
Average Disk Transfer Bytes / sec
Average Disk Write Queue Length
Current Disk Queue Length
Disk Read Bytes / sec
Disk Reads / sec
Disk Write Bytes / sec
Disk Writes / sec

Remember to use the option to sample all of the drives individually instead of just the total aggregate counter.

Scenario

Take this scenario as an example. You currently have 20 mission-critical SQL Servers that you are looking to virtualize. Each one exhibits steady-state business-day storage utilization of 8,000 IOPs (with bursts to 15,000) and 45MB/s (and bursts to 275MB/s). Given the state of your virtualization environment regarding CPU and memory consumption, you feel comfortable with six SQL Server virtual machines per physical host. Do the math and do you find that each host can handle the aggregate storage workload? It equals between 48-90K IOPs and between 270-1650MB/s, per host.

Uh-oh.

Virtualize these machines and you most likely cannot achieve that level of performance at each host. You now have a performance bottleneck based on your physical server’s performance, and you are going to notice.

Now What?

Each of your hosts and shared storage devices (usually a SAN) should be carefully examined before you virtualize your SQL Servers. The SAN itself should be configured in a manner that can exceed your aggregate I/O requirements, both per host and per LUN. RAID types, controller counts and cache values, disk group configurations, whatever – should all be able to exceed what you need. Most of the time, the core SAN can handle your requests without a problem. Other times, more spindles or SSDs, controller cache, or reconfiguring RAID types can possibly fix the situation.

At the host level, though, things get interesting. Each host will have some means to connect to the storage (fiber HBAs, Ethernet adapters, etc.). A default configuration is usually what I see in the field, which is usually not optimal for resource-intensive workloads such as SQL Server. Round-trip path performance may be slow, active multipathing may be misconfigured or not configured at all, individual paths can be congested and not load balanced, or not enough overall paths to the SAN may be available. Any one of those items can cause an individual VM’s performance to suffer, and in aggregate this impact can be very devastating to the overall performance of all of the VMs on a host.

At the VM level, you have some tricks that can help you improve performance even more. Configure your SQL Server VMs with multiple virtual disks and spread out your workload. A common disk layout for my SQL Server templates is as follows.

Letter Purpose
C: Operating system
D: SQL Server home and system databases
F: User database data files (1 of X)
L: User database log files (1 of X)
T: TempDB database data and log files
Z: SQL Server database backup target

By utilizing multiple virtual disks, you allow the hypervisor to more efficiently multipath the I/O requests rather than funnel all of that I/O traffic down one route. You can then place the virtual disks on the appropriate type and configuration of storage. For example, the OS disk can go on a SAS tier of disks. The backup volume could go on a SATA tier, as well as rarely used archival data. Commonly used data could get placed on SSDs. Maximize your performance where you need it, and minimize costs where you don’t quite need top performance.

Another quick tip is that for VMware-specific environments, you can utilize the VMware Paravirtual SCSI driver. You can get a sizable performance improvement for a virtual disk. Michael Webster has a great post demonstrating the performance differences here, and I have a how-to guide that you can use to retrofit your existing I/O-intensive virtual machines to take advantage of this free performance boost here.

Conclusion

Hopefully your virtualized SQL Servers and other mission-critical systems are performing beautifully in your virtual environment. If not, review these tips to see what improvements you can make. Even if things are good right now, incorporate these tips to help squeeze more performance from your virtualized SQL Servers to help you scale into the future!

If you want to get started with performance analytics and triage of virtualized SQL Servers with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Tools for Proactive DBAs – Policy Based Management – Notes from the Field #012

[Note from Pinal]: This is a 12th episode of Notes from the Fields series. When taking a vacation the biggest worry any DBA has it that in their absence what will happen to their database. They are often worried that something will go wrong with their server or some users will change something without their permission and knowledge. This keeps on them so much worried that even though they take vacations they keep on looking at the phone or email continuously. Here is a simple trick which DBA can implement and take their vacation without worrying about their database.

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs, and Developer faces in their career – how to be proactive and manage database policies before they are violated. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


In this week’s tip from the field were going to cover Policy Based Management. Policy Based Management (PBM) allows you to import and build policies that could be enforced or checked to validate that your SQL Server Farm is compliant with best practices. Today, were going to show you how to import best practice policies. In a future tip, we will show you how you can leverage Central Management Server and Policy Based Management together to validate best practice settings against your SQL Server farm.

In order to use Policy Based Management you will need to enable it. In this case PBM is enabled, but this would how you would enable it incase it’s not already enabled on your instance.

For those of you who are familiar with SQL Server Best Practices Analyzer you will noticed that several of these checks have been included as policies that can be imported. We are going to take a look at how you can import some of these policies.

Policy Based Management can be accessed via SSMS under the management node for an instance as shown below. Right click on policies and select Import Policy.

Once you click on import you will see the following window. When you click on the ellipse next to “Files to import” you will see the folder by default is pointed to the folder that includes the default best practices shown below.

You will notice that there are several best practices policies. Today, we are going to import just the Database Auto Close policy.

Once we import the following policy we will see it inside the policies tree in SSMS.

You will notice that there is a red arrow on the Database Auto Close policy we just imported. This is because the policy evaluation isn’t scheduled. You can manually evaluate it or schedule a SQL Agent job to run on a schedule to evaluate the policy.

In this tip, were going to execute manually. This is done by right clicking on the policy and selecting evaluate. You will see that the “Database Auto Close” policy was evaluated for every user database on the instance.

If you enjoyed this tip from the field, checkout several other posts on Policy Based Management.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)