In this post, I am going to discuss what the most valuable (or highest paid) employees do and how they think.   I will briefly list strategies they use to make them worth more to their customers and thus to their employers.

The most valuable employees tend to think and act differently than the average.  Interestingly, these individuals are not necessarily the most intelligent and/or highly educated. Yet, they tend to deliver the highest value and hence typically command the highest pay and job security in the companies where they work.

While they command the highest compensation and job security, their main motive is rarely making more money.  They seem to intuitively understand that money is just a result and they have a way of figuring out the approaches that result in adding more value to their customers and employers.

The following are some of their attributes:

• They focus on the processes and tasks that allow them to be most valuable and somewhat indispensable for what they do.
• They tend to be self-made and self-motivated.
• They do not need much supervision or direction.
• They actually lead by example with their attitude and disposition.
• They are proactive.
• They think ahead and prevent problems rather that react to them.
• They are organized and focused.  They set goals and objectives for themselves and others.
• They understand that knowledge makes them better and more valuable and thus they constantly learn and improve themselves.
• They communicate before, during, and after with all involved.
• They see feedback as “information to improve” and not as “criticism.”  Hence, they yearn it and constantly ask for more:

What can I do better?

What can I improve?

• They don’t blame others and take full responsibility for their actions, decisions, mistakes, etc.
• They typically begin with the “end” in mind:

What’s the goal, they ask?

What is the purpose?

• They pay attention and focus on the “vital few” or on those areas that yield the biggest “bang for the buck.”
• They tend to also network and share knowledge and become great team players.
• They are self-assured and confident and yet remain quite humble.
• They place their customer (internal or external) as number one.
• They exhibit high levels of passion for what they do.
• They align their talents and passion (things they love to do) with the needs of their clients and they under commit and over deliver by managing expectations.

Here are some questions to ponder:

• Are you one of these individuals?  Would you want to be?
• What do you need to think and believe in order to become one of them?
• What changes would you need to make in the way you think and feel in order to become like them?
• What good reasons can you come up to motivate you to take action?
• How would you feel if you became one of them?
• What if this could ultimately result in 20, 30 percent (or more) money in your pocket?
• What could you accomplish with that extra money now or in the future?
• What would that mean to you?

Remember money is the result of your attitude, motivation, strategies, knowledge, skills and actions.  Thoughts lead to feelings.  Feelings lead to action.  Action leads to results.   Think the right way, feel the right way, act the right way and you should get what you want and maybe even more!

The BEST is Yet to Come!

Epi Torres, CEO

The model we use to frame our key capabilities is simple and straightforward.  It includes five components that we believe are critical to the performance of Remote DBA Experts’ Key Service Delivery Capabilities:

• Information
• Processes
• Skills
• Sub-Capabilities: methods, tools, systems, etc.
• Other: control parameters, attitudes, etc.

Let me illustrate the model using our Problem Prevention Capability as an example.  Let’s start with Information and work our way down the list.

Information: What information is necessary in order to predict and prevent problems from occurring?

The goal is to obtain and have available the right and most accurate information at the right time in order to prevent the most problems possible.  Thus, we ask these two questions for each of the segments we service:

• What are the most common problems in database environments we encounter?
• What information do we need to have in order to prevent them?

Once we gather this information, we can segment the responses by the level of maturity required to obtain and act upon such information.  We have chosen four information categories to organize the information at each level:

• Goals
• Strategies
• Plans
• Data

We focus our attention on information associated with two principal areas that are our key value drivers:

• Database availability
• Database performance

We then apply the same approach to the rest of the model components that remain:  Processes, Skills, etc.  Then, we ask a set of questions about each component and identify key categories to determine the maturity level at which each belong.  This provides a rich set of management items to focus our attention and investment on.  The capability maturity optimization process requires hard work and constant attention, but it produces incredible results and the payback is solid at many levels.

This is the last post of this series.  I hope you have followed along and gotten some value out of it!  Thanks for reading along.

The BEST is Yet to Come!

Epi Torres, CEO

Many Database Administrators are aghast when they discover that SQL Server 2005 and above have a default trace that runs 24 hours a day, 7 days a week. Most of us are taught early on in our careers that extended tracing is something that should only occur on rare occasions. Before you rush out and disable the default trace across all of your SQL Servers, let’s take a moment to find out what it’s actually capturing for us.

Is the Default Trace Running?

Before we begin, we need to determine a few things. First off, is the default trace running? You can use the following query to determine if the default trace is currently running on your system.

– Is the default trace running
SELECT *
FROM sys.configurations
WHERE configuration_id = 1568

You can see from the output of this query that the default trace is indeed running and the necessary trace files will be available for us to access and investigate.

Uh, Oh! The Default Trace is Disabled!

If the Default Trace has been turned off by a wary Database Administrator, you can turn it back on using the following command. You must have enabled the “show advanced options.”

– Turn on advanced options
sp_configure ‘show advanced options’, 1 ;
GO
RECONFIGURE ;
GO
— Turn on the default trace
sp_configure ‘default trace enabled’, 1 ;
GO
RECONFIGURE ;
GO

Where are the Trace Files Located?

Now we know the default trace is running, we need to determine where the trace files are being stored on the system. The query listed below will return this information.

– Get the path to the current trace rollover file
SELECT *
FROM ::FN_TRACE_GETINFO(0)

The results of this query have provided the path to the current rollover trace file. We can see that the file is located inside the “Log” directory in the SQL Server installation path.

How Can I View the Trace File Information?

Now that we know where the rollover trace files are located, we can begin to investigate the information they contain. We could just double-click on the file listed above and open it in SQL Server Profiler. This would load the trace and show us its contents. I’d like to show you a more robust method of viewing this information.

Use the file path from above in the query that follows to inspect the contents of the trace as if it were a table in SQL Server.

SELECT loginname,
spid,
applicationname,
DatabaseId,
cat.name AS [CategoryName],
starttime,
e.name AS EventName
FROM ::FN_TRACE_GETTABLE(‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_12.trc’,
0)
INNER JOIN sys.trace_events e ON eventclass = trace_event_id
INNER JOIN sys.trace_categories cat ON e.category_id = cat.category_id
WHERE cat.category_id = 2

In this query I’m only returning events that occurred in the “Database” category. The “Database” category includes the events “Data File Auto Grow”, “Data File Auto Shrink“, “Log File Auto Grow”, “Log File Auto Shrink” and “Database Mirroring Status Change.”

You’ll see from the results above that there is a SQL Agent Job that causes a “Data File Auto Grow” every time it executes. This is probably something that warrants additional investigation.

In this case, I discovered a SQL Server database that was using a data file “Autogrowth” increment of 1 MB. This database was also receiving a substantial data load via a SQL Agent job every 1 second.

Conclusion

The SQL Server default trace captures 32 unique events across 6 categories. The example illustrated above is just a small sampling of the information that is available. I recommend you take some time to experiment with the other events and categories the next time you’re checking the health of your SQL Server environment. You never know what you might discover.

Scott Caldwell, SQL Server DBA

In my last two posts, I described key capabilities and capability maturity.  In this post, I will delve into the Capability Maturity Optimization process.  Optimization, in a nutshell, is the process of continuous improvement.  It means setting goals, objectives, and metrics for assessing performance and taking action to optimize the capability based on how it performs.  It is close to impossible to start or sustain a capability operating at level five of its maturity levels. Thus, the Capability Optimization Process becomes very important.

In order to optimize, you need to know where you are and have some sense of where you need or want to go.  In other words, you need to know what level the capability is operating at currently and what level you need or want it to operate.  This assumes that a maturity road map exists to describe the aspects of the capability that need to be actualized at each level.

As I had mentioned in a prior related post, we chose five key aspects for the Remote DBA Experts’ maturity road map:

• Information
• Processes
• Skills
• Sub-Capabilities: Methods, tools, systems, etc.
• Other: Control parameters, attitudes, etc.

Let me use the Information aspect to illustrate the Optimization Process a little better.  The idea is that at level one there is certain information required for the capability to perform at that level and, as the capability matures, more information will be necessary.  At level five, all information that makes the capability perform most efficiently and effectively  shall be available to those who need it within the capability.  Because business is dynamic, the information needed for each capability will change and thus requires on-going optimization even after reaching level five performance.  This will also be the case for Processes, Skills, and the rest of the key capability aspects in the road map or model you use.

Furthermore, parts of the key aspects will also need attention.  For example, you may not need additional information but the source or method of information delivery may need optimization.  That is why it is an ongoing effort to improve or even maintain your level of performance.  Limited resources necessitate careful assessment of the things that make the biggest difference.  That way efforts and resources can be applied at the highest leverage points in the system and provide the biggest bang for the buck!

What do you need to optimize?

The BEST is Yet to Come!

Epi Torres, CEO

In order to resolve this issue use the following steps:

Ad hoc access to OLE DB provider ‘Microsoft.ACE.OLEDB.12.0′ has been denied. You must access this provider through a linked server.

1. Run the Following T-SQL

   USE [master]

   GO

   EXEC master . dbo. sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’AllowInProcess’ , 1

   GO

   EXEC master . dbo. sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’DynamicParameters’ , 1

   GO

2. Then  drill down into the registry and make the following change:
   

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\Providers\Microsoft.ACE.OLEDB.12.0

3. Switch disallowadhocAccess value to 0
   

   
   

4. Rerun the query with the user that was initially denied access and it works!!
   

   select *

   from openrowset(‘Microsoft.ACE.OLEDB.12.0′,

   ‘Excel 8.0;Database=\\test\all\spreadsheets\YouWant.xlsx’,

   Thanks,

   James Shropshire MCDBA, MCITP SQL Server
   

Capability Maturity is simply the level of development and performance of a particular key capability.  The classical approach to capability maturity involves assessing the level (1-5) at which a capability operates according to the standard set by the developer.  The most popular of which is the Software Development CMM standard developed many years ago by our Pittsburgh neighbors at Carnegie Mellon University’s Software Engineering Institute.  The five levels are defined as follows:

1. Initial
2. Managed
3. Defined
4. Predictable
5. Optimizing

A typical graphical representation of the maturity model looks something like this:

SOURCE: CMU SEI “Improving Workforce Capabilities with the People Capability Maturity Model”

The items under each level indicate the requirements for that level. For our approach to maturity at Remote DBA Experts, we developed six key service delivery Maturity Aspects as categories for each of the five levels:

• Key Information
• Key Processes
• Key Skills
• Key Sub Capabilities
• Key Tools
• Key Other

The concept is to identify the key aspects expected at each level of maturity as we move up the maturity continuum from level 1 to level 5.  For example, what key information, etc. is needed to operate at level one (or the minimum information needed to be in business).

Once all levels of maturity are defined, you move to assess your maturity.  In our case the assessment process involves the following three criteria:

• Approach: The approach taken to implement an aspect
• Implementation: How well implemented it is
• Results:  The results produced

This framework allows you to focus on the vital few aspects and actions that yield the best results to enable your key capabilities to reach the highest maturity level and remain at that optimized level.  That is they bottom line!

In my next post, I will delve into the Optimization subject.  Hope you’ll come back to read it.  THANKS!!!

The BEST is Yet to Come!

Epi Torres, CEO

Here is a way to create an SSIS that audits the transfer of data each step of the way. The main transformations used for this are:

• Row Count
  
  • The row count was used to validate the number of records
• Conditional Split
  • Was used to split out rows with a specific Header value. In this case “H”
• Aggregate
  • Was used to sum the specific values

Here are the results. I used data viewers to confirm the specific rows:

Here are the results of the aggregate:

As I mentioned in my last post, our key capabilities at Remote DBA Experts are those that support our ability to be most proactive, responsive and effective.  Let me now expand upon each of the key capabilities.

Proactiveness Capabilities

Proactiveness is first and foremost an attitude. Thus, it comes from people and it starts with them.  That means we must look for this attitude in our recruiting and hiring process.  We hire individuals who are technically capable and meet many other criteria (see our hiring criterion here).  That is where it all starts.  Furthermore, we provide the individual with the ability to be most proactive by enabling proactivity with key capabilities.  In the case of Proactiveness, our primary aim is internal and customer problem prevention.

In order to be most efficient and effective at problem prevention, we focus our attention and investment on the key capability components:

• Key Information
• Key Processes
• Key Skills
• Key Sub Capabilities
• Key Tools
• Other

The first step is to identify these key items and then to ensure they are in place and available to the team.  This is where we focus our optimization efforts.

Here at Remote DBA Experts, our monitoring capability is the primary tool that enables us to predict and prevent problems and we place significant effort to that end.  In fact, this year we invested a lot of money and attention to this capability.  We have also established a new internal organization that is completely focused on problem prevention and customer responsiveness.  It is working out to be one of our best ideas ever!

Responsiveness Capabilities

Like Proactiveness, Responsiveness is also an attitude we look for in the folks we recruit and hire. In this area we have also made significant efforts and investments recently to enhance this capability.  We implemented a new CRM that is the key to improving and maintaining our ability to respond to our customers both internal and external.

Responsiveness involves four key aspects:

• Acknowledging
• Timely Action
• Updating
• Closure

Our Responsiveness Capabilities are focused on enabling these aspects. This ensures that we acknowledge customer requests quickly, take timely action, keep the customer informed along the way, and close the matter to the satisfaction of the customer.

Effectiveness Capabilities

For Remote DBA Experts, Effectiveness means accomplishing two primary things for our customers: Ensure the highest database environment availability and performance possible. That is it!  That is what being effective means to our team.

Our Effectiveness Capabilities focus on two areas: Activities and Tasks.

Activities

Activities are what I call the set of “meta-activities” people must do effectively in order to accomplish value delivering tasks and achieve the key results customer expect.  Effectively completing them is part of the sequence.

• Effective Preparation
• Effective Learning
• Effective Communication
• Effective Documentation
• Effective Search
• Effective Analysis
• Effective Collaboration
• Effective Research

To execute these activities most effectively, you need to clearly define each.  You need to know and understand what Effectiveness means for every one of them.  You need to know which of them are more vital to the end result.  Spending too much time and/or effort on less vital activities will affect their proverbial strength as a link in the chain.

Tasks

Tasks are what customers hire us to do for them.  Each of these tasks breaks down into many more “sub-tasks” for the lack of a better term.  However, the three tasks listed below capture the essence of the services we deliver.  Their effective completion is the ultimate contributor to us being most effective as a whole.  Database availability and performance directly depend on how well we proactively monitor and maintain the databases under our stewardship.  Furthermore, effectively preventing and resolving problems enhances our effectiveness.

• Effective Database Monitoring
• Effective Problem Management (prevention and resolution)
• Effective Database Maintenance

I hope this post gives you a better sense of our Key capabilities and how we go about them.  In my next post, I will delve into the Capability Maturity subject.  Thanks for reading!

The BEST is Yet to Come!

Epi Torres, CEO

Recently I ran into an issue where I had a SQL Server instance that needed to have a database restored on it.  A pretty simple task, right?  Well, it turns out that I did not have rights on the system to do the restore.  My client did not have a system administrator password and did not have a user which we could use to grant us the necessary access.  But, I did have local system administrator privileges on the server.  As long as we have local admin rights on the server we can get the necessary privileges from SQL Server.

1. The first step to recovery is having the ability to bring the instance down.  We can do this a few different ways: either through Enterprise Manager (SQL Server Management Studio), services, or through Configuration Manager.  Once the instance is brought down we need to bring it up in single user mode using the ‘m’ flag.  I usually do this through the command prompt using ‘net start’ or you could put the trace flag in the startup parameter files; however you feel most comfortable.  Example: Bringing the default instance down and starting it back up in single user mode.



2. Use SQLCMD (2005-2008) or iSQL (2000) to connect to the instance.  Make sure that Object Explorer and applications are not connected to the instance.  You can also do this through SSMS or Query Analyzer. Example: Connect to the instance once in single user mode.



3. Add yourself as a user and grant yourself administrator privileges. Example: Adding a test user and giving it system administrator privileges.



4. Congratulations!  You have successfully given yourself administrator privileges, so now stop SQL Server and start it back up without the single user flag.

Tim Foley, Sr. SQL Server DBA

At Remote DBA Experts, we are constantly optimizing our key service capabilities.  We monitor the maturity and performance of all our key capabilities and ensure our efforts are ongoing to enable the best performance of those capabilities.  In the following series of posts, I will describe our model and approach to optimization.

To get started, I will define the following terms so we are on the same page during the rest of the posts in the series:

• Key Capabilities
• Capability Maturity
• Optimization
• Model

Key Capabilities

Capability is defined as the quality or ability of being capable.  A key capability is a mission-critical capability.  In our case, we have identified several key capabilities for our remote database administration services business.  Our mission is to deliver better and more service than in-house DBAs.  In order to accomplish our mission, we focus our effort on three main areas:

• Proactiveness
• Responsiveness
• Effectiveness

Hence, our Key Capabilities are all the things that enable us to be the most proactive, responsive and effective in delivering our remote DBA services.

Capability Maturity

Capability Maturity is simply the level of development and performance of the capability.  It involves three main aspects:

• Approach: The approach or strategy you take to make it happen
• Implementation: How well you implement it
• Results:  What results are produced by the capability

Optimization

This is the process of continuous improvement.  It means setting goals, objectives, and metrics for assessing performance and  taking action to optimize the capability based on how it performs.

The Model

The model we use to frame our key capabilities is simple and straightforward.  It includes five components we believe are critical to a capability’s performance:

• Information
• Processes
• Skills
• Sub-Capabilities: methods, tools, systems, etc.
• Other: control parameters, attitudes, etc.

In the following posts, I will delve into each of these and expand upon them with more detail and examples.  I hope you come back to read the series.

The BEST is Yet to Come!

Epi Torres, CEO