We are excited to announce that Remote DBA Experts has entered an agreement with Amazon Web Services (AWS) and is a new member of the AWS Solution Providers Program.  AWS provides flexible and dependable cloud platform services to a wide variety of customers.  The Amazon Solution Providers Program consists of Integrated Software Vendors and Systems Integrators that are utilizing cloud computing services with AWS nationally and globally.

The AWS Solution Providers Program allows organizations in the program to enhance and increase their cloud computing offerings. By developing a relationship with AWS, Remote DBA Experts can now help current customers set up applications on the cloud more quickly and successfully as well as reduce delivery times and reach a wider customer base.  As we continue to grow, our involvement in the program is sure to be beneficial to our organization.

In addition to being a member of the AWS Solution Providers program, Remote DBA Experts is now listed in the AWS Solution Providers Directory. To see Remote DBA Experts’ listing, please click here.

Remote DBA Experts is featured in the summer edition of Pittsburgh Technology Council’s publication, TEQ Magazine. The interactive version of TEQ magazine is now live. Check out our article within here.

On Saturday July 23rd, the WVPASS and Greater Wheeling Chapter of AITP joined forces to host the 2nd Annual SQL Saturday in Wheeling, WV. SQLSaturday is a one-day free training event for SQL Server professionals and those wanting to learn about SQL Server brought to you by the local SQL Server community and PASS (Professional Association for SQL Server).

The following is a quick run down of our stats for the event. We had 108 people signup, 85 attendees, 11 speakers covering 18 sessions on Saturday. Many thanks goes out to the speakers, volunteers, sponsors and attendees for making this a successful SQL Saturday.

The Good

• We had a great mix of MVP’s, experienced speakers and first time speakers. One of the greatest accomplishments for this event is giving Matt Velic (Check out his #sqlsat80 photo’s), Abi Chapagai and Shannon Lowder their first opportunity to speak at a SQL Saturday. The speakers all did a fantastic job. We received several comments from attendees thanking us for having fantastic speakers.
• Registration was quick and smooth. Everyone was able to get in and get to the main room to socialize and enjoy a free breakfast. The volunteers did a fantastic job.
• We took a step outside of the box with lunch and got burrito’s from Salsa Café. With only a few complaints, this was a hit. Last year we did box lunches from Panera and it was expensive and we also got lukewarm reviews.
• Speakers received their evaluations shortly after their sessions. Personally, I have spoken at a few events and never received my feedback so I completely understand how important it can be towards growing as a speaker.
• WVNCC was a fantastic host facility. The staff was very friendly and able to get us everything we needed to be successful.
• Hosted the event on a $2,400.00 budget. There will be more on this in a future blog post.

Stuff to Work on Next Year

• Speakers dinner might have been a little too early. We held our dinner at 6pm and a few speakers wanted to attend but couldn’t make it in time. We also had plans of following our dinner with a voyage of the Italian Festival but a rain storm quickly sidelined that plan.
• SQLGolf was a great idea and fun until the heat kicked our butt around the fifth hole. Once we got to the eighteenth hole I forgot we were still playing golf.
• We need more onsite sponsors next year.
• Bacon was not included in breakfast.

John Sterrett, SQL Server DBA

I’ve been conducting quite a few SQL Server health assessments lately so I thought I would write a brief explanation of the importance of “Instant File Initialization” and how I check to see if it’s being used.

SQL Server File Initialization

The SQL Server storage engine utilizes an initialization process on data and log files. This process fills the files with zeros in order to overwrite any existing data that may be on disk. The initialization process occurs when you create a database, restore a database or file group, add files to an existing database or increase the size of an existing database file. These disk operations are blocking processes that will cause SQL Server to wait until they are complete.

What is Instant File Initialization?

Simply put, instant file Initialization removes the file initialization penalty by negating the need to fill the newly allocated space with zeros before it can be used. This is incredibly important and can reduce disk allocation times from minutes to milliseconds. This is incredibly important when you think about restoring a VLDB and how long you’ll have to wait just for the empty database files to be initialized.

There are a couple of caveats to mention. The first is that instant file initialization is only available on Windows Server 2003 or later. If you’re on Windows Server 2000 then you’re out of luck. The second is that this only works for database data files and is not available for transaction log allocations. The last thing to note is that this is not available if you’re using Transparent Data Encryption (TDE) in SQL Server.

Am I Using Instant File Initialization?

If you don’t know if you’re using this capability then I recommend you find out as soon as possible. Instant file initialization is only working for you if the SQL Server service account has been granted SE_MANAGE_VOLUME_NAME .

Figuring this out from the comfort of SSMS turns out to be pretty simple but it does require the use of xp_cmdshell in order for the query I’ve provided to work. If your security policies restrict the use of xp_cmdshell then you’ll have to figure this out the hard way or turn on xp_cmdshell before you execute and then turn it off after you’re done. If xp_cmdshell is enabled then execute the following script to see if you’re receiving the benefit:

EXEC xp_cmdshell ‘whoami /priv’;

GO

What have we just done? Well, we’ve just executed the Windows “whoami” command in the context of our database service account. We included the /priv parameter because otherwise this tool returns quite a bit of information and we only need to see the security privileges. It’s important to note that the output will return all the security privileges whether they are granted to the account or not. The key is to look to the right of the privilege to see whether is disabled or enabled. Have a look at the output below:

If you scan down to the bottom of the list you’ll find the SE_MANAGE_VOLUME_PRIVILEGE near the bottom. You’ll see that to the right the privilege it is listed as enabled. This is great news and we’re not paying the initialization penalty during data file allocations.

Wait, Instant File Initialization is not Enabled!

If you executed the query and came to the unfortunate conclusion that instant file initialization is not enabled, don’t worry because it’s very easy to implement.

First off, open up the “Local Policy Editor” on the database server and navigate to the “User Rights Assignment” node under the “Local Policies” node. When you select the “User Rights Assignment” node all of the policies will be displayed in the pane at the right.

You’ll need to scroll down to find policy. It’s named “Perform volume maintenance tasks.” You’ll notice that the users or groups that have been granted this privilege are listed to the right. Open the policy and add the SQL Server service account.

Unfortunately, once this is complete you’re still not finished. You’ll need to restart the SQL Server service for it to take effect.

Conclusion

I think it’s easy to see how this feature will benefit your environment in a number of situations. So, take the time today to check this out and save yourself all the waiting when time matters most.

Scott Caldwell, SQL Server DBA

SQL Server supports numerous commands, most of which are extremely well documented with detailed examples provided in “Books on Line.” However, there are quite a few that were left out of the official documentation and remain unsupported by Microsoft.

You’ll find references regarding these in blogs across the Internet and some are more useful than others. Obviously, these are unsupported commands and should only be used with great care.

TRACE FLAGS

There are quite a few undocumented trace flags in SQL Server.  However, I’m only going to touch on four today. They are trace flags 3004, 3014 3604 and 3605.

These can be enabled for the current session with the following commands:

– Enable the trace flags

DBCC TRACEON(3004);

DBCC TRACEON(3014);

DBCC TRACEON(3604);

DBCC TRACEON(3605);

GO

These trace flags are going to be used in conjunction with a database restore so we can get an idea about what SQL Server is doing during the process. We’ll be looking at output in the message output screen and output sent to the SQL Server error log.

Before we go much further, I want to briefly explain the purpose of these flags and why it’s important to use them together to get the information we’re looking for.

Trace Flag 3004

Most Database Administrators are aware of instant file initialization. In a nutshell, when instant file initialization is enabled the data files do not need to be zeroed out during creation. This can save an incredible amount of time during the restoration of VLDBs. As you can imagine, the zeroing out of a 1 TB data file can take a very long time.

Trace flag 3004 turns on information regarding instant file initialization. Enabling this trace flag will not make this information available to view. You will still need to turn on trace flag 3605 to send this information to the error log.

Trace Flag 3014

Trace flag 3014 provides detailed information regarding the steps performed during the backup and restore process. Normally, SQL Server only provides a limited amount of information in the error log regarding these processes. By enabling this trace flag you’ll be able to see some very detailed and interesting information.

Trace Flag 3604

Trace flag 3604 can be used under a variety of circumstances. If you’ve ever used DBCC IND or DBCC PAGE then you’ve probably already used trace flag 3604. It simply informs SQL Server to send some DBCC output information to the screen instead of the error log. In many cases, you have to use this trace flag to see any output at all.

Trace Flag 3605

Trace flag 3605 will send some DBCC output to the error log. This trace flag needs to be enabled to see the instant file initialization information made available by trace flag 3004.

Restore without Trace Flags

I created a full backup of the AdventureWorks database and then restored it with the following command:

– Restore the database

RESTORE DATABASE AdventureWorks

FROM DISK = ‘C:\TEMP\ADVENTUREWORKS.BAK’

GO

Without these trace flags the following information is returned to the message output screen:

You can also see very similar information in the error log. I returned the contents of the error log with the following command:

– Read the error log

EXEC xp_readerrorlog;

GO

Restore with Trace Flags

I’m going to perform the restore again. This time my four trace flags have been enabled and I’m hoping to see some additional information in both the message output window and the error log.

In order to make sure the error log is easy to read, I’ve cycled it with the following command:

– Cycle the error log

EXEC sp_cycle_errorlog;

GO

The next step is to execute the restore again.

– Restore the database

RESTORE DATABASE AdventureWorks

FROM DISK = ‘C:\TEMP\ADVENTUREWORKS.BAK’

GO

After the restore is finished, and with the log cleared and the trace flags enabled, we get the following information in the message output window:

It’s easy to see that this output is far more detailed when compared to what we received during our last restore. You notice that there are quite a few additional steps that were not reported when the trace flags were not enabled.

But wait, we also need to check the error log to see what additional information is available there too.

– Read the error log

EXEC xp_readerrorlog;

GO

The most interesting information in the error log is in regard to instant file initialization. You can see that SQL Server is zeroing out the data file during the container prepare process. This means we’re not benefiting from instant file initialization.

Conclusion

It’s important to understand that SQL Server purposely hides this level of detail from us on a daily basis. Arguably, this information isn’t really that necessary. However, if you feel as though you need to have a greater understanding about what’s going on inside the database engine then this is a good starting point on your journey.

Scott Caldwell, SQL Server DBA

You got the job because you’re a quick learner, have the ability to understand complex systems, and most importantly, you can troubleshoot.  However you became an Oracle Apps DBA, you’re one now, so the fun starts.  I started this blog to assist young Oracle Apps DBA professionals who might need a little more detail in their solutions and good discussion into basic, fundamental Oracle architecture and other Oracle App subjects.

I’ve been an Oracle Apps DBA for 6 months now and before that I was a RDBAE junior DBA where I troubleshot MSSQL, Oracle, and Oracle Apps issues.  At RDBAE, I have the great opportunity to seek advice from our Oracle Apps team with decades of experience. Also, they have given me a R12 environment that I can practice on whenever I have time.  Working at RDBAE, I get great opportunities to work on some cool projects.  My first big project was a 24-hour downtime in production to apply the ATG RUP7 patch along with other patching.

With that, I plan to write a discussion and troubleshooting post each week depending on my job load.  Every once in a while I’ll also post a quick blog on helpful commands. I hope you find the posts informative and feel free to post comments at any time.  I’ll post the blogs with the beginning titles:

OraAppYP: Discussion: <title_name>

OraAppYP: Troubleshot: <title_name>

OraAppYP: HelpfulCMD: <title_name>

Enjoy the posts!

Chris Page, Oracle Apps DBA

If you’re like me, you’ve been hearing a lot about Master Data Management lately.  Master Data Management is not an entirely new theory; it goes back to the Master File idea from the days of the mainframe.  The basic concept is to have a single ‘correct’ set of data that the entire company will use.

During this time of data marts, pivot tables made from excel spreadsheets, and data spread throughout the company, this concept has made a big comeback.  Mergers and acquisitions, as well as data being managed by separate departments, contribute to the lack of data consistency across the organization. For example, you would never have your current cable provider calling to inquire if you’d like to switch your cable to… your current cable provider.  This type of mishap usually occurs because there is a ‘sales lead’ database that is not reconciled with a ‘current customer’ database.  Master Data Management (MDM) requires the company to first define their data and agree upon what it all means.

The objective of MDM is to ensure that all of the non-transactional data in the company (what some might call a reporting database or a data warehouse) is consistent throughout the organization.  In other words, the idea is that the ‘right hand’ of the organization will always know what the ‘left hand’ is doing.  Once the data definition is agreed upon, the company must ensure that all data collection and data manipulation follow the data definition perfectly.  The data is then processed and stored in a single location, and all BI, reporting, CRM, ERP, and other functionality feed off of this single repository.

Virtual Master Data Management (VMDM) is considered the fourth generation of MDM solutions.  VMDM uses abstraction layers in the data model to create a metadata catalog.  The data is not actually consolidated into a single storage location, like it is in the more typical MDM solution.  Instead, the catalog contains an index of the information and is used to make sure that all data is consistent across the organization and that the ‘true’ source of each piece of information is known and documented.  The data is dynamically transformed when requested by a BI, CRM, or ERP solution.

There is an obvious time advantage in using VMDM as opposed to a typical MDM solution.  The project life cycle of MDM includes the definition and analysis phase, as well as the development phase, during which ETL processes are written to transform and load the data into the newly defined structures.  Since you are often storing the data both in its originating system, and also in the MDM solution, you can save on storage costs with VMDM.

Although MDM has many positive attributes, many companies have found it cumbersome to implement in its purest form.  The concept of VMDM allows for data governance with a significant time and cost reduction.

Katy Park, SQL Server Team Lead

The very thought of database corruption makes my skin crawl. Unfortunately, it’s something that all Database Administrators will eventually experience and need to correct. I’m not going to discuss how to recover from this lurking problem. Instead, I’m going to give you some simple strategies to ensure you are aware of the issue as soon as possible. Early detection can save you downtime, data loss and even your job.

The Usual Suspect

Generally speaking, the majority of database corruption is caused by hardware errors in the disk sub-system. Our database files are sitting on our SAN, or DAS, and are happily going about their lives without any problem until something goes awry. You may not even know there is a problem until it’s too late and a database is marked suspect.

The most important thing to can do, outside of monitoring for these problems, is to keep your storage system’s firmware and drivers up to date and consistent within the system. Sign-up for notifications with your vendor so you will receive these notices, and when you received them, read the little text file that explains what has changed and what will happen if you don’t update. I can’t tell you how often I’ve read through these and run across verbiage like, “previous version can cause data loss.”

These systems aren’t perfect. Stay on top of your firmware and driver updates or you may be turned down when you make that middle of the night support call and need their help.

Automate the Eights

I’ve got a bag of alerts that I like to implement on each SQL Server that I administer. Three of these alerts are for errors 823, 824 & 825.

Error 823

Sometimes referred to as a hard I/O error, this is alerting SQL Server, and you, that a read or write error has occurred. In the case of a read error, Windows has already attempted a retry four times before sending the message. This error can also be captured by alerting on errors with a severity of 24.

This error will be accompanied by the following entry in the SQL Server error log and Windows event log:

“The operating system returned error 823 to SQL Server during a <<MESSAGE>> at offset <<PHYSICAL OFFSET>> in file <<FILE NAME>>. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online”.

Error 824

Sometimes referred to as a logical consistency error, or soft I/O error, this indicates that Windows was able to read the page from disk but SQL Server has discovered that the page has a problem. This error can be caused when SQL Server determines that there was a checksum error or a torn bits error when it read the page from disk. This is dependent on the page verification option your database is using. This error can also be captured by alerting on errors with a severity of 24.

No matter which occurred, there’s a problem with the page and the following error message will be reported in the SQL Server error log and the Windows event log:

“SQL Server detected a logical consistency-based I/O error: <<ERROR TYPE DESCRIPTION>>. It occurred during a <<Read/Write>> of page <<PAGEID>> in database ID <<DBID>> at offset <<PHYSICAL OFFSET>> in file <<FILE NAME>>. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.”

Error 825

This error also falls in the hard I/O error category. When this error is received, it indicates that Windows requested a read and the read failed at least one time. Oftentimes, this is the first indication that there’s trouble brewing in the disk sub-system. This error can also be captured by alerting on errors with a severity of 10.

You’ll find the following error message in the SQL Server error log and Windows event log:

“A read of the file <<FILE NAME>> at offset <<PHYSICAL OFFSET>> succeeded after failing<<FAILURE COUNT>> time(s) with error: <<MESSAGE>>. Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.”

Conclusion

You’re going to receive one, or more, of these errors during your career as a Database Administrator. No one is immune. Bad things happen to hardware, and most of the time there’s not a lot you can do to prevent it. However, you can be prepared to respond.

If you receive one of these errors, don’t wait around before you investigate. You’re response is critical to the health of the data you’re sworn to protect. These types of problems don’t go away on their own. Don’t make the mistake of thinking that they will.

So, keep your storage system firmware and drivers up to date, implement these alerts and systematically plan your response. These actions will give you the best chance of a speedy recovery when database corruption strikes.

Scott Caldwell, SQL Server DBA

This post is for those of you who need a quick answer to this issue and you’ve already restarted the DTC service and the SQL Server service in that order. Before you start pulling your hair out and running to DTCPing or DTCTest, if it’s ok with all parties involved, try restarting the DTC service on each server involved in your BizTalk architecture. Naturally this will appear to be an issue solely with the server hosting SQL Server, but be sure to confirm the servers hosting the BizTalk Client, the BiZTalk application and the Biztalk database. In a recent case, I encountered all of the mentioned components on different physical servers (which is a good thing). The issue I encountered was resolved by restarting the DTC on the server where the BizTalk Client was installed. Hopefully this will save you some time. Now, how did I come to this revelation? Initially I attempted to use DTCPing. The tool is easy to install and setup. It provides output of any success and error message when attempting to communicate between server X and server y.  So I tested all the options from confirming no dtc issues so I thought. Then for good measure, I decided to test with DTCTest as well and I discovered the following issue when attempting to connect from server hosting the BizTalk client to the server hosting the database server. This was the error:

• I confirmed the ports were open
• Confirmed DNS
• Even confirmed the netcards

Since we were able to narrow the issue down to bad communication between 2 servers, one of which the DTC and SQL Services had already been restarted, that left us with one and only one potential candidate. So the DTC services were restarted and everything worked.

Checklist

• Confirm that DTC is running properly on servers involved and restart one by one if it’s possible.
• I’d run DTCTest first rather than DTCPing because in my case DTCPing confirmed connectivity. However, I was unable to enlist a transaction which is what I wanted to do at the end of the day.
• If you need additional output, fallback to DTCPing.

Attached are a few more screenshots should you encounter this issue.

Initial Error:

Accompanying Error:

Successful DTCTest display after restarting DTC on the server with the issue:

Thanks,

James Shropshire MCDBA, MCITP SQL Server

Recently I ran into an issue with the infamous “Cannot generate SSPI context.”  I have run into this issue a few times in the past and it is a problem that usually takes a while to find the solution and fix.  This problem does not deal with just SQL Server.  Usually the help of a system administrator is needed to fix the problem.  The most recent one had me stumped for a few days and trying everything under the sun to fix it.

Here was the setup of the problem:

1. User 1 on computer 1 is connecting to computer 2, which reads a file on computer 3.  It is a DTS package and we are using Windows authentication throughout the process.
2. We are receiving an O/S error 5 in the package, which is denying access to the SQL Server Windows account on a file we need to open.  We have made sure the proper permissions are granted on the file to be opened, read, and written to.
3. Trying to connect to computer 3 is giving me the “Cannot generate SSPI context” error when connecting from computer 1.
4. It was working just fine a few days ago.

First, let’s take a look at two of the types of authentication SQL Server can use.  The first is NTLM.  This protocol is a challenge/response where the client sends a user name to the server, it sends back a challenge which is encrypted by the user’s password and sent back to the server for authentication.  If the user is a domain user, then it is forwarded off to the domain controller for authentication and otherwise it is checked locally by the Security Account Manager.

The second type of authentication is Kerberos.  This method is a Third Party Trust Scheme.  It is a little more complicated as the following items work in tandem to authenticate a user: Key Distribution Center, Authentication Service, Ticket Granting Service, Ticket Grant Ticket,  and Server Principal Name.  The main advantage with Kerberos is that it gives us the ability to use the double hop authentication.  We want to use this authentication method because of the DTS package.

Now that we know which authentication we should be using, let’s tackle some of the other issues.  How can we know which authentication method we are using?  It is easy for SQL Server 2005 and 2008.  We simply connect to management studio, open up the object browser and open a query window to the server we want to test.  Then, we run the following:

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

One thing that I’ve found when running this query is that it should be opened from another server on the domain.  Do not remote desktop directly to the machine and run it on the server you want to test. I have found that to be unreliable.  Now that we see that the server is running under NTLM and not Kerberos, how can we change it?  That part is easy.

To ensure that we are using Kerberos, we need to make sure of a few things first:

1. Both the client and server must be running in the same domain or a trusted domain.
2. The Server Principal Name (SPN) must be registered in Active Directory.
3. The SQL Server instance must have the TCP/IP protocol enabled and be used with the client connection.  An easy way to make sure you are using TCP/IP is to connect to the server with management studio as such: tcp:servername or IP address.  This will ensure you are using TCP/IP.

If we are using Kerberos, then we just need to make sure that the account that is starting SQL Server on computer 2, where the DTS package is located, has the “Account is Trusted for Delegation” in active directory.  If we are not using Kerberos, then we need to look at the Server Principal Name (SPN) and possibly delete and manually recreate the entries.  Using the SetSPN.exe tool that is available on the Windows 2003 support tools, we can list the entries with the –l switch, delete entries with the –d switch, or add entries with the –a switch.  In my case, I found that the entries were already there, but since the user running the SQL Server service was recently changed, they needed to be deleted and recreated.  This is because entries are not supposed to be registered under two different containers.  This would also explain why Kerberos was not being used and why it was working just a few days ago.

In conclusion, the error occurred because of many reasons. First, we recently changed the service account causing errors in the SPN and not allowing Kerberos authentication to run.  The fall back authentication is NTLM, which does not allow for delegation of the authentication which did not allow the DTS package to open the file with the correct credentials.  Using the correct authentication along with “Account is Trusted for Delegation” in Active Directory solved the problem.  Hope this helps anyone else running into this same type of situation.

Tim Foley, Sr. SQL Server Database Administrator