Demystifying AWS/EC2 Access Credentials

As of now, I have begun my pursuit of a career in the cloud technology or to the least be in the right frame of mind to cover database hosted in the cloud. For this purpose, I specifically chose to study AWS and all of its features and no sooner had I begun working my way up to understand the core concepts and get hands-on experience I was awestruck with the dizzying amount of credentials, keys, ids, username, certificates, passwords, and codes which are used to access and control various accounts and service features and functionality. And this led me to think I need a single document that will let me have a clear cut understanding of what all those credentials meant and where does each one suffice to help me access what in the AWS/EC2 infrastructure and/or topology.

  • AWS Email Address and Password is used to login to your AWS account on the AWS website. Through this website, you can access and change information about your account including billing information. You can view the account activity. You can control many of the AWS services through the AWS console. Both of these values may be changed as needed.
  • MFA Authentication Code If you have ordered and activated a multi-factor authentication device, then parts of the AWS site will be protected not only by the email address and password described above but also by an authentication code. This is a 6 digit code displayed on your device which changes every 30 seconds or so. The AWS website will prompt you for this code after you successfully enter your email address and password.
  • AWS Account Number is a 12 digit number separated by dashes in the form 1234-5678-9012. You can find your account number under your name on the top right of most pages on the AWS website (when you are logged in). This number is not secret and may be available to other users in certain circumstances. I don’t know of any situation where you would use the number in this format with dashes, but it is needed to create the next identifier
  • AWS User ID is a 12 digit number with no dashes. In fact, it is simply the previously mentioned AWS Account Number with the dashes removed (e.g., 12345678912). Your User ID is needed by some API and command line tools.
  • AWS Access Key ID and  Secret Access Key are the first of two pairs of credentials which can be used to access and control basic AWS services through the API including EC2, S3, SimpleDB, CloudFront, SQS, EMR, RDS, etc. Some interfaces use this pair, and some use the next pair below. Pay close attention to the names requested. The Access Key ID is 20 alpha-numeric characters like 022QF06E7MXBSH9DHM02 and is not secret; it is available to others in some situations. The Secret Access Key is 40 alpha-numeric-slash-plus characters likekWcrlUX5JEDGM/LtmEENI/aVmYvHNif5zB+d9+ct and must be kept very secret. You can change your Access Key ID and Secret Access Key if necessary. In fact, Amazon recommends regular rotation of these keys by generating a new pair, switching applications to use the new pair, and deactivating the old pair. If you forget either of these, they are both available from AWS.
  • X.509 Certificate and  Private Key are the second pairs of credentials that can be used to access the AWS API. The EC2 command line tools generally need these as might certain 3rd party services. These are also used to perform various tasks for AWS like encrypting and signing new AMIs when you build them. These are the largest credentials, taking the form of short text files with long names like cert-OHA6ZEBHMCGZ66CODVHEKKVOCYWISYCS.pem and pk-OHA6ZEBHMCGZ66CODVHEKKVOCYWISYCS.pem respectively. Amazon keeps a copy of the Certificate so they can confirm your requests, but they do not store your Private Key, so don’t lose it after you generate it. Two of these pairs can be associated with your account at any one time so they can be rotated as often as you rotate the Access Key ID and Secret Access Key.
  • Linux username When you ssh to a new EC2 instance you need to connect as a user that already exists on that system. For almost all public AMIs, this is the root user, but on Ubuntu AMIs published by Canonical, you need to connect using the ubuntu user. Once you gain access to the system, you can create your own users.
  • Public ssh key and Private ssh key are often referred to as a key pair in EC2. The ssh keys are used to make sure that only you can access your EC2 instances. When you run an instance, you specify the name of the key pair and the corresponding public key is provided to that instance. When you ssh to the above username on the instance, you specify the private key so the instance can authenticate you and let you in. You can have multiple ssh key pairs associated with a single AWS account; they are created through the API or with tools like the ec2-add-keypair command. The private key must be protected as anybody with this key can log in to your instances. You generally never see or deal with the public key as EC2 keeps this copy and provides it to the instances. You download the private key and save it when it is generated; Amazon does not keep a record of it.
  • SSH host key is a private file generated by the host on first boot which is used to protect your ssh connection to the instance so it cannot be intercepted and read by other people, and each EC2 instance which you run will have its own ssh host key. In order to make sure that your connection is secure, you need to verify that the SSH host key fingerprint, which is provided to you on your first ssh attempt, matches the fingerprint listed in the console output of the EC2 instance.

With this much information, it can be a daunting task to remember all of this and anyone can be mistaken for the other. As such, it is always advised to make a file or a folder to save all of the above information associated with each AWS account especially for the ones that Amazon does not store for us. And once jotted down best to encrypt that information and secure it with a passphrase.

Let me know if I have missed any of the credentials and how you manage all of the above information.

Posted in AWS, Uncategorized | Tagged , , , | Leave a comment

SQL Server 2016 : A New Beginning

It’s been a long time since my last blog post, but evidently from today’s blog post headline you can only imagine where I was investing my time. Yes, SQL Server 2016, that is exactly where I had been dwelling all along. The RC3 was released recently around mid April meaning that we are ever so close to the RTM release as this was the final release candidate. With this, today I want to talk about top 10 features in SQL Server 2016 that has me excited from a developer as well as a DBA prospective. Let’s take a look at what they are:

  1. Always Encrypted
    With the Always Encrypted feature enabled the SQL Server data will always be encrypted within SQL Server. Access to encrypted data will only be available to the applications calling SQL Server. Always Encrypted enables client application owners to control who gets access to see their applications confidential data. It does this by allowing the client application to be the one that has the encryption key. That encryption key is never passed to the SQL Server. This feature will now allow us to sleep at night knowing that our confidential data stored in a cloud managed database is always encrypted and out of the eyes of our cloud provider.
  2. Dynamic Data Masking
    If you are interested in securing your confidential data so that some people can see it, while other people get an obscured version of confidential data then you might be interested in this feature. With dynamic data masking we can obscure confidential columns of data in a table to SQL Server for users that are not authorized to see all of the data. With this feature we can identify how the data will be obscured by defining masking rules, so unauthorized logins can only read data that they are allowed, whereas authorized logins can see all of the data.
  3. JSON Support
    With SQL Server 2016 we can now interchange JSON data between applications and the SQL Server Database Engine. By adding this support Microsoft has provided SQL Server the ability to parse JSON formatted data so it can be stored in a relation format. Additionally, with JSON support we can take relational data, and turn it into JSON formatted data. Microsoft has also added some new functions to provide support for querying JSON data stored in SQL Server.
  4. Multiple TempDB Database Files Configuration
    It has been a best practice for a while to have more than one tempDB data file if we are running on a multi-core machine. In the past, up until SQL Server 2014, we always had to manually add the additional tempdb data files after we installed SQL Server. With SQL Server 2016 we can now configure the number of tempDB files we need while we are installing SQL Server.
  5. PolyBase
    This feature allows us to query distributed data sets. With the introduction of PolyBase we will be able to use Transact SQL statements to query Hadoop, or SQL Azure blob storage. By using PolyBase we can now write adhoc queries to join relational data from SQL Server with semi-structured data stored in Hadoop, or SQL Azure blob storage. This allows us to get data from Hadoop without knowing the internals of Hadoop. Additionally, we can leverage SQL Server’s on the fly column store indexing to optimize our queries against semi-structured data.
  6. Query Store
    Now, this is one of the most, if not the most talked about feature in the new SQL Server 2016. Currently in version of SQL Server prior to 2016, we can see existing execution plans by using dynamic management views (DMVs). But, the DMVs only allow us to see the plans that are actively in the plan cache. We can’t see any history for plans once they are rolled out of the plan cache. With the Query Store feature, SQL Server now saves historical execution plans. Not only that, but it also saves the query statistics that go along with those historical plans. This is a great addition and will allow us to now track execution plans performance for our queries over time.
  7. Row Level Security
    With Row level security the SQL Database Engine will be able to restrict access to row data, based on a SQL Server login. Restricting rows will be done by filter predicates defined in inline table value function. Security policies will ensure the filer predicates get executed for every SELECT or DELETE operation. Implementing row level security at the database layer means application developers will no longer need to maintain code to restrict data from some logins, while allowing other logins to access all the data.
  8. R comes to SQL Server
    With Microsoft’s purchase of the Revolution Analytics, they are now able to incorporate R to support advance analysis against big data right inside of SQL Server. By incorporating R processing into SQL Server, data scientists will be able to take their existing R code and run it right inside the SQL Server database engine. This will eliminate the need to export our SQL Server data in order to perform R processing against it.
  9. Stretch Database
    This feature provides us a method to stretch the storage of our on-Premise database to Azure SQL Database. The interesting part of this feature is that having the stretch database feature allows us to have have our most frequently accessed data stored On-Premise, while our less accessed data is off-site in an Azure SQL database. When we enable a database to stretch, the older data starts moving over to the Azure SQL database behind the scenes. When we need to run a query that might access active and historical information in a stretched database, the database engine seamlessly queries both the On-Premise database as well as Azure SQL database and returns the results to us as if they had come from a single source. This feature will surely make it easy for DBA’s to archive information to a cheaper storage media without having to change any actual application code
  10. Temporal Table
    Finally, a Temporal table is a table that holds old version of rows within a base table. By having temporal tables SQL Server can automatically manage moving old row version to the temporal table very time a row in the base table is updated. The temporal table is physically a different table than the base table, but is linked to the base table. If you have been building or plan to build your own method to managing row versioning then you might want to check out this new feature supported by SQL Server 2016.

SQL Server 2016 has many new features and I have talked about only the top 10 among them that I felt would have the most impact on their users. Some of the new features are enhancements to existing features, while others are entirely new features. And while this write-up only discusses some of those features in theory, I will definitely followup to this blog post with more details regarding the new features with examples and code samples as well.

With that, let me know, which new feature are you most interested in SQL Server 2016? Are there any new feature that you think SQL Server missed out on? or would be better off without? Let me know your thoughts on the new SQL Server 2016.

Posted in ms sql, Uncategorized | Tagged , , , , | Leave a comment

Wait Statistics……What to worry for?

A while back, I once blogged about how to troubleshoot a SQL Server, and one of the first things that people complain regarding servers is that the server is slow.  Now there are a lot of tools and mechanisms within SQL Server for gathering metrics that can help us point towards the issue but the best place to start is by looking at the wait statistics.

There are a lot of ways to obtain information regarding the wait statistics, but the most useful and quick way is to run a query against the sys.dm_os_wait_stats DMV. Again, you can build your own query to pull the information that can help you out find the issues in the server, but if you are new to this, I’d suggest using a query developed by Paul Randal which I have been using since quite a while and it certainly gives a clear picture in the most convenient manner and is quite fast as well. The query is provided below and you can even check the link to the query here

WITH[Waits]AS
    (SELECT
        [wait_type],
        [wait_time_ms]/1000.0 AS[WaitS],
        ([wait_time_ms]-[signal_wait_time_ms])/1000.0 AS[ResourceS],
        [signal_wait_time_ms]/1000.0 AS[SignalS],
        [waiting_tasks_count]AS[WaitCount],
        100.0 *[wait_time_ms]/SUM([wait_time_ms])OVER()AS[Percentage],
        ROW_NUMBER()OVER(ORDERBY[wait_time_ms]DESC)AS[RowNum]
    FROMsys.dm_os_wait_stats
    WHERE[wait_type]NOTIN(
        N'BROKER_EVENTHANDLER',N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',N'CHECKPOINT_QUEUE',
        N'CHKPT',N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT',N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_SHUTDOWN_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH',N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP',N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',N'WAIT_FOR_RESULTS',
        N'WAITFOR',N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',N'XE_TIMER_EVENT')
    AND[waiting_tasks_count]>0
 )
SELECT
    MAX([W1].[wait_type])AS[WaitType],
    CAST(MAX([W1].[WaitS])ASDECIMAL(16,2))AS[Wait_S],
    CAST(MAX([W1].[ResourceS])ASDECIMAL(16,2))AS[Resource_S],
    CAST(MAX([W1].[SignalS])ASDECIMAL(16,2))AS[Signal_S],
    MAX([W1].[WaitCount])AS[WaitCount],
    CAST(MAX([W1].[Percentage])ASDECIMAL(5,2))AS[Percentage],
    CAST((MAX([W1].[WaitS])/MAX([W1].[WaitCount]))ASDECIMAL(16,4))AS[AvgWait_S],
    CAST((MAX([W1].[ResourceS])/MAX([W1].[WaitCount]))ASDECIMAL(16,4))AS[AvgRes_S],
    CAST((MAX([W1].[SignalS])/MAX([W1].[WaitCount]))ASDECIMAL(16,4))AS[AvgSig_S]
FROM[Waits]AS[W1]
INNERJOIN[Waits]AS[W2]
    ON[W2].[RowNum]<=[W1].[RowNum]
GROUPBY[W1].[RowNum]
HAVINGSUM([W2].[Percentage])-MAX([W1].[Percentage])<95;-- percentage threshold
GO
Now, the information returned from this query can be mind boggling if you do not know where to look at and what to look at. This is where today’s blog post comes in help. During my professional career as a SQL Server DBA, I have come across many wait types that causes a server to go slow. But, we should not be worrying about all of those wait types. In-fact, several DBA’s across the globe including me worry about only a handful of those wait types based on their occurrence, severity, etc. Let us look at what those wait types are:
  • ASYNC_NETWORK_IO: The predominant cause of this wait type is row by row processing of results in a client, instead of caching the results client-side and asking SQL Server to send more. A common myth is that this wait type is usually caused by network problems which is rarely the case.
  • CXPACKET: This wait type always occurs when there is parallelism, as the parent thread in a parallel operation waits until all the child thread have completed. However, when parallel threads are given unbalanced workload, the threads that finish early also fall victim to this wait type and adding to the wait type and thus becoming most prevalent. So this wait type could be good with lesser statistics, as the workload has lots of good parallelism, or it could be bad if there’s unwanted parallelism or problems causing skewed distribution of work among parallel threads.
  • LCK_M_X: This wait type commonly occurs when there is lock escalation being seen. It could also be caused by using a restrictive isolation level like REPEATABLE_READ or SERIALIZABLE that requires S and IS locks to be held until the end of a transaction.
  • PAGEIOLATCH_SH: This wait type occurs when a thread is waiting for a data file page to be read into memory. Common causes of this are when the workload doesn’t fit in memory and the buffer pool has to keep evicting pages and reading others in from disk, or when query plans are using table scans instead of index seeks, or when the buffer pool is under memory pressure which reduces the amount of space available for data.
  • WRITELOG: This wait type is common to see in the first few top waits on servers as the transaction log is often one of the chief bottlenecks on a busy server. This could be caused by the I/O subsystem not being able to keep up with the rate of log flushing combined with lots of tiny transactions forcing frequent flushes of minimal-sized log blocks.
  • PAGELATCH_EX: The main reasons for this wait type are TEMPDB allocation bitmap contention and an insert hotspot. Basically meaning, when lots of concurrent threads are creating and dropping temp table combines with a small number of TEMPDB files and not having TF118 enabled, or from lots of concurrent threads inserting small rows into a clustered index with an identity value, leading to contention on the index lead-level pages.
  • LCK_M_IX: This wait type occurs when a thread is waiting for a table or page IX lock so that a row insert or update can occur. It could be from lock escalation to a table X or S lock causing all other threads to wait to be able to insert/update.
  • SOS_SCHEDULER_YIELD: The most common cause of this wait type is that the workload is memory resident and there is no contention for resources, so threads are able to repeatedly exhaust their scheduling quanta, registering SOS_SCHEDULER_YIELD when they voluntarily yield the processor.

Now, even among these wait types mentioned above, which ones are a critical if they seem to be the most prevalent as per the results of the query provided earlier. Personally for me, it would be the LCK_M_IX wait type as most of the other waits are likely to be very small in duration, and although they’d make the overall system slower, the locking waits can easily cause individual queries to time out and fail, leading to a very poor user experience.

It goes without saying that this in no way means that you can neglect the other wait types as every individual system design and requirement can make any of the above wait types to be of concern in a particular server. And so, now that you know how to find why the server is slow by running the query, you even have the answer to what exactly could be the issue in the server.

There are lots of other wait types but are not included in today’s blog. If there is any particular wait type that you would want me to write about or discuss feel free to post a comment. Also, I would love to hear from you if you agree or disagree to the comprehensive wait types that I have discussed today. I would be more that happy to reply in my upcoming blog post.

Posted in ms sql, Performance Tuning, Troubleshooting, Uncategorized | Tagged , , , , , , , , | 1 Comment

Q&A Backup_001

Ever Since I wrote the blog post regarding how to make SQL Server backups go faster and better, I have been bombarded with several colleagues and professionals regarding backup issues and questions that they had in mind regarding SQL Server backups. Many of those questions were repetitive, and since there were a huge number of questions, I decided to post my answers to the most common and significant SQL Server issues in my blog. Oh, and if you want to check out my post regarding better and faster SQL Server backups, just follow the link given below:

Better…..Faster….SQL Server Backups

Now, for today I shall address one of the most common questions regarding SQL Server backups:

Can I restore a backup onto a different version of SQL Server? And do I have to worry about any issues in doing so?

Well, I can explain this in many ways, but for the most part I’ll say, Yes. We can restore to a different version of SQL Server, but we can only restore upwards. Meaning, we can restore from 2000 to 2005, 2005 to 2008R2, 2008R2 to 2012, and so on, but we can never restore in the reverse order. Each version of SQL server makes modifications to the binaries of the database and its storage, even the metadata. Sad thing is, Microsoft doesn’t go back in time and re-write the previous versions to support these changes. And if we really need to restore in the reverse order, we have to try scripting out the schema and data.

In order to know which version of SQL Server a backup originated, we need to look at the header information of the backup file using a command as shown below:

RESTORE HEADERONLY FROM DISK = ‘d:\SQLBKUP\backup_001.bak’;

This command would return information that includes details of the major, minor and build version of the SQL Server instance for that particular backup file and this allows us to validate that we are attempting to restore an appropriate version.

Now for the second part of the question. When restoring a database to a newer version of SQL Server, it is possible to hit incompatibilities within the database. The safest approach is to run the Microsoft’s Upgrade Advisor on the database we wish to migrate, in order to ensure that it’s ready. and then take a backup and restore it to the new instance.

After the restore, the database will be running in the compatibility mode that it had prior to the upgrade. This means that it will be set to support the functionality of the version of SQL Server from which we migrated. In order to take full advantage of the new version of SQL Server, we will need to modify the compatibility level of our database. We can do this through the GUI, or by running the T-SQL as show below:

ALTER DATABASE Backup001 SET COMPATIBILITY_LEVEL = 110;

The different numbers of compatibility_level represent different version of SQL Server, 90 for 2005, 100 for 2008 and 2008R2, 110 for 2012, and so on.

Before ending I’d like to add that not all upgrades are possible. Microsoft only allows us to jump forward a maximum of two versions. For example, we cannot restore a SQL Server 2000 database backup to a 2012 instance. We would have to first restore it to 2008/2008R2, update the compatibility level, perform backup and then restore that backup to 2012.

That’s it for this time. I shall certainly tackle a new issue that is quite common to SQL Server backups. Until then, I’d like to hear from you all too. So, feel free to comment.

Posted in backup, ms sql, Performance Tuning, Uncategorized | Tagged , , , , , , | Leave a comment

A Guide To SQL Server Maintenance Plans

An integral part of a SQL Server DBA or any DBA as a matter of fact is to keep the servers up and running with performance as it was when the whole database system was afresh. Now, we all know that’s a tough thing to do, because as time passes by the performance of the database system degrades and there can be multiple factors adding onto it. That is why it’s often said that the real job of a DBA begins when he/she has to maintain the performance of the database system. On this note, there are various methods one can use to maintain the database system across many technologies, but when we talk about SQL Server, we prefer the term maintenance plan. A maintenance plan in SQL Server is a group of tasks that are done sequentially in order to maintain the performance of the overall database system and improve them at times. The individual tasks in a SQL Server maintenance plan can be summarized as below:

  • Check Database Integrity
  • Shrink Database
  • Rebuild Index
  • Reorganize Index
  • Update Statistics
  • Execute SQL Server Agent Job
  • History Cleanup
  • Back Up Database – Full
  • Back Up Database – Differential
  • Back Up Database – Transaction Log
  • Maintenance Cleanup Task

Now, all these individual tasks can be executed via a maintenance plan created in SQL Server, but the key thing to decide on, is the order of the tasks as well as the frequency. Here is a general guideline as to which task should be done in what order:

  1. A task such as cleanup history can be performed at any point in the plan but, for other tasks, there is a certain logical order.
  2. It makes sense to start the maintenance plan with the check database integrity task, because there is no point in running the rest of the plan if the integrity of our database is in question.
  3. The backup database – full should come before backup database – transaction log, as we can’t perform a transaction log backup before we perform a full database backup.
  4. If a rebuild index task or the reorganize index and update statistics is performed during the same maintenance window as the backup database – full task, then always perform the index maintenance task first. Why? This way, if its needed to perform a restore of the backup, it will have it’s indexes de-fragmented and will be ready for production
  5. The Maintenance cleanup task, if selected should be performed only after backup database – full has been completed. This way, we can ensure that a good backup has been made before deleting any older backups.
  6. If we choose separate schedules for each task, the scheduling of these tasks will determine the actual order in which they occur, and so the key thing to remember is that the logical task order we select is not absolute, and that it can be overridden by the schedules we set for each one.

If we were to write a code using the above guidelines it could look like as shown below:

if (Check Database Integrity succeeds)
{
    Rebuild Index OR (Reorganize Index and Update Statistics)

    Back Up Database (Full) or Back Up Database (Differential)
    if (Back up Database (Full) succeeds)
    {
        Backup Database (Transaction Log)
        Maintenance Cleanup Task
    }

    Shrink Database (never)
}

Run these anytime:
    Execute SQL Server Agent Job
    History Cleanup

But what about the frequency of the tasks to be executed? Well, frankly, this will vary depending on individual needs, but here are some common guidelines for how often to schedule tasks:

  • Check Database Integrity – DAILY (or at least WEEKLY)
  • Rebuild Index  WEEKLY (DAILY is better). 
  • Reorganize Index and Update Statistics WEEKLY (DAILY is better)
  • Back Up Database (Full) WEEKLY
  • Back Up Database (Differential) EVERYDAY EXCEPT FULL BACKUP DAY
  • Backup Database (Transaction Log) – HOURLY
  • Maintenance Cleanup Task – WEEKLY
  • Shrink Database DO NOT SCHEDULE – ON DEMAND ONLY
  • Execute SQL Server Agent Job – DEPENDS
  • History Cleanup – WEEKLY (or other)

Now before using these general guidelines on any system, here are a couple of more things to keep in mind:

  • Do not update statistics using maintenance plans. Allow SQL Server to update statistics as needed. If you notice performance degradation, identify large tables with numerous indexes and update statistics manually only on those indexes.
  • Do not use maintenance plans for removing unused space in production databases. Doing so can generate substantial undue overhead on the server. Try removing unused space from production databases occasionally – perhaps once a month during scheduled maintenance windows.
  • Use maintenance plans for checking database integrity on non-production servers.
  • Do not use maintenance plans for running integrity checks in production databases. Instead, run DBCC CHECKDB and other integrity checks separately from database or transaction log backups. Attempting repairs found by DBCC CHECKDB puts the database in single-user mode, which is unacceptable during production hours.
  • Use maintenance plans for rebuilding index in non-production environments
  • Do no use maintenance plans for index maintenance in production databases. There are better ways to maintain indexes and the one I along with many industry leading experts use is a solution from Ola Hallengren. This is an all in one solution including backups, integrity check, index and statistics maintenance, and works across SQL Server 2005 to 2014.

To summarize, a database maintenance plan is critical for the best performance, but my preference is to not use the database maintenance plan wizard unless you really know what you are doing. It just doesn’t give you enough advice. And, if you just select the defaults, you will end up with a maintenance plan that is no optimal.

A better approach would be to create your own maintenance plan. If you write the code yourself then you can strategically target only the objects that have the warning sings and/or are out of date and you can set when to rebuild versus when to reorganize. But then again, all this coding has already been done by Ola Hallengren and you only need to configure the execution of the code as per your database environment and pick the schedule that is the most optimal for you using the guidelines that I provided earlier.

I hope this post serves you well in creating your own customized maintenance plan to increase the throughput of your SQL Server database. Please feel free to drop in comments regarding your approach towards database maintenance plan in SQL Servers.

Posted in backup, Best Practices, configuration, ms sql, Performance Tuning, Troubleshooting | Tagged , , , , , , , , , | Leave a comment

Transaction Tracking Continued

In my last blog, I wrote about the use of fn_dblog that helped us in querying the log to find the exact information with respect to a transaction that we needed to track down. Let us look at the scenario that we are going to tackle today:

Someone dropped a table and we want to find out when it happened and  maybe who did it. The default trace has wrapped meaning it has been overwritten so we can’t get the DDL trace from there anymore.

If the transaction log for the DROP hasn’t yet been cleared from the active portion of the log when we’d be able to use fn_dblog to search through the log fro the information we need as per my last blog. We might even be able to look in the inactive portion of the log by using trace flag 2536, which instructs the log reader to ignore the log truncation point and dump all possible log records from the log.

But what do we do if the pertinent log records just don’t exist in the log anymore? They’re only in our log backups. We could tediously find our way through restoring the of backups a few seconds at a time until we find the point at which the DROP took place, and then restore to just before that point so we can get the data back.

Or we could save a whole ton of time and use fn_dump_dblog which allows us to dump and search log records from a log backup file, without having the restore the database.

Note: The function fn_dump_dblog creates a new hidden SQLOS scheduler and up to three threads, which will not go away and will not be reused until a server restart. It’s a bug which has been fixed in SQL Server 2012 SP2 and above and SQL Server 2014 going forward. The fix has not been backported any earlier and so it is advised not to be used in prior versions

Ok then let’s see an example – We are going to create a table, populate it, back it up, then drop it.

USE[master];
GO
CREATEDATABASE[FNDBLogTest];
GO
USE[FNDBLogTest];
GO
SETNOCOUNTON;
GO
-- Create tables to play with
CREATETABLE[ProdTable](
    [c1]INTIDENTITY,
    [c2]DATETIMEDEFAULTGETDATE(),
    [c3]CHAR(25)DEFAULT'a');
CREATETABLE[ProdTable2](
    [c1]INTIDENTITY,
    [c2]DATETIMEDEFAULTGETDATE(),
    [c3]CHAR(25)DEFAULT'a');
GO
INSERTINTO[ProdTable]DEFAULTVALUES;
GO1000
-- Take initial backups
BACKUPDATABASE[FNDBLogTest]TODISK=N'D:\SQL2012\FNDBLogTest_Full.bak'WITHINIT;
GO
BACKUPLOG[FNDBLogTest]TODISK=N'D:\SQL2012\FNDBLogTest_Log1.bak'WITHINIT;
GO
INSERTINTO[ProdTable2]DEFAULTVALUES;
GO1000
Now we’ll drop the table and add some more log records:
DROPTABLE[ProdTable];
GO
INSERTINTO[ProdTable2]DEFAULTVALUES;
GO1000
Now how can we find the point at which the table was dropped?
SELECT
    [Current LSN],
    [Operation],
    [Context],
    [Transaction ID],
    [Description]
FROM
    fn_dblog(NULL,NULL),
    (SELECT
        [Transaction ID]AS[tid]
    FROM
        fn_dblog(NULL,NULL)
    WHERE
        [Transaction Name]LIKE'%DROPOBJ%')[fd]
WHERE
    [Transaction ID]=[fd].[tid];
GO
snap1

That’s awesome

Now we’ll take another log backup, which clears the log, and contains the log we just looked at.

BACKUPLOG[FNDBLogTest]TODISK=N'D:\SQL2012\FNDBLogTest_Log2.bak'WITHINIT;
GO
Who did the DROP?

If we want to figure out who ran the DROP command, look at the Transaction SID field for the DROP transaction’s LOP_BEGIN_XACT log records and the pass that value into the SUSER_SNAME() function. Simple!

Restoring using STOPBEFOREMARK

The LSN for the LOP_BEGIN_XACT log record is where we need to restore to just before. To do that we can just plug in the LSN to the STOPBEFOREMARK option for RESTORE. The option is documented but the format is not. The LSN needs to be specified with ‘0x‘ in front of it, and then the format is exactly as returned by fn_dblog.
The restore sequence to restore to just before the DROP is therefore:
RESTOREDATABASE[FNDBLogTest2]
    FROMDISK=N'D:\SQL2012\FNDBLogTest_Full.bak'
WITH
    MOVEN'FNDBLogTest'TON'C:\SQL2012\FNDBLogTest2.mdf',
    MOVEN'FNDBLogTest_log'TON'C:\SQL2012\FNDBLogTest2_log.ldf',
    REPLACE,NORECOVERY;
GO
RESTORELOG[FNDBLogTest2]
    FROMDISK=N'D:\SQL2012\FNDBLogTest_Log1.bak'
WITH
    NORECOVERY;
GO
RESTORELOG[FNDBLogTest2]
FROM
    DISK=N'D:\SQL2012\FNDBLogTest_Log2.bak'
WITH
    STOPBEFOREMARK ='lsn:0x0000009d:0000021e:0001',
    NORECOVERY;
GO
RESTOREDATABASE[FNDBLogTest2]WITHRECOVERY;
GO
And the table is there again, right before the point it was dropped. We can see where we used the constructed LSN string in the final log restore.
Using fn_dump_dblog
So what if the log records are no longer in the log? We can use the fn_dump_dblog function. For instance, here is how we can use it to look in the FNDBLogTest_Log2.bak backup that we created earlier:
SELECT
    COUNT(*)
FROM
    fn_dump_dblog(
        NULL,NULL,N'DISK',1,N'D:\SQL2012\FNDBLogTest_Log2.bak',
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT);
GO
We have to specify all the DEFAULT parameters, all 63 of them or it just won’t work. The other parameters are:
  • Starting LSN (usually just NULL)
  • Ending LSN (again, usually just NULL)
  • Type of file (DISK or TAPE)
  • Backup number within the backup file (for multi-backup media sets)
  • File name

So we could do the same query as we did above:

SELECT
    [Current LSN],
    [Operation],
    [Context],
    [Transaction ID],
    [Description]
FROM
    fn_dump_dblog(
        NULL,NULL,N'DISK',1,N'D:\SQL2012\FNDBLogTest_Log2.bak',
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT),
    (SELECT
        [Transaction ID]AS[tid]
    FROM
        fn_dump_dblog(
            NULL,NULL,N'DISK',1,N'D:\SQL2012\FNDBLogTest_Log2.bak',
            DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
            DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
            DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
            DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
            DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
            DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
            DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
            DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
            DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT)
    WHERE
        [Transaction Name]LIKE'%DROPOBJ%')[fd]
WHERE[Transaction ID]=[fd].[tid];
GO
Which works perfectly, but takes much longer to run.
So maybe you are wondering what all the other parameters to fn_dump_dblog are for? They are for specifying the media families of a media set that has more than one media family.
Here’s an example using a log backup striped across two files:
BACKUPLOG[FNDBLogTest]TO
    DISK=N'D:\SQL2012\FNDBLogTest_Log3_1.bak',
    DISK=N'D:\SQL2012\FNDBLogTest_Log3_2.bak'
WITHINIT;
GO
If we try to use fn_dump_dblog and only specify a single file, we get an error:

 

SELECT
    COUNT(*)
FROM
    fn_dump_dblog(
        NULL,NULL,N'DISK',1,N'D:\SQL2012\FNDBLogTest_Log3_1.bak',
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT);
GO
Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.
So we have to specify both media families:
SELECT
    COUNT(*)
FROM
    fn_dump_dblog(
        NULL,NULL,N'DISK',1,N'D:\SQL2012\FNDBLogTest_Log3_1.bak',
        N'D:\SQL2012\FNDBLogTest_Log3_2.bak',DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
        DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT);
GO
So there you have it – some more powerful tools to add to your disaster recovery arsenal. Although do note that we do need log data whether it be active/inactive logs or log backups.
Do write your thoughts regarding this two part series blog that I have written regarding transaction tracking as well as point in time restore of logs.
Posted in Best Practices, ms sql, Troubleshooting, Uncategorized | Tagged , , , , , , , | Leave a comment

Finding a Transaction in the log for a particular user

As a DBA, this is a frequently asked question from the developers “When was a certain insert/update/delete performed on a specific table?” Or, It is simply required to know the time of the particular transaction so that it can be rolled back or restored to a state prior to the transaction. Now there are 2 ways to do this, first is rather simple, if the relevant log is still available, meaning that the log has not been flushed. The other is a bit time taking scenario when the available logs do not hold such information. Today I will deal with the former scenario and will talk about the latter in upcoming blogpost.

For the first scenario, this can be done using the fn_dblog, and needs two pieces of information, the user and the rough time that the transaction occurred. The user can’t be used to search in the log directly, but every LOP_BEGIN_XACT  log record contains the SID of who ran the transaction. The SID can be obtained from the username using the SUSER_SID function.

For instance, on my test SQL Server instance:

SELECT SUSER_SID (‘SQL2012\SKARKI’) AS [SID];

GO

SID
-----------------------------------------------------------------
0x0105000000000005150000008E888D4129BB677CAA278B76E8030000
Now that we have our SID, we can use it as a filter for fn_dblog, like so:
SELECT
    [Current LSN],
    [Operation],
    [Transaction ID],
    [Begin Time],
    LEFT([Description],40) ;AS[Description]
FROM
    fn_dblog(NULL,NULL)
WHERE
    [Transaction SID]=SUSER_SID('SQL2012\SKARKI');
GO
snap1
Obviously the transactions above are a sample. You can imagine the case of lots of transactions spread out over a few hours or even over a day and to narrow it down to the transaction you want, you could look through the list manually for the rough time or specify a time range on the SELECT using predicates on the Begin Time column in the fn_dblog output as shown below:
SELECT
    [Current LSN],
    [Operation],
    [Transaction ID],
    [Begin Time],
    LEFT([Description],40)AS[Description]
FROM
    fn_dblog(NULL,NULL)
WHERE
    [Transaction SID]=SUSER_SID('SQL2012\SKARKI')
AND([Begin Time]>'2015/06/03 11:18:15'AND[Begin Time]<'2015/06/03 11:18:25');
GO
snap2
And if you knew what the operation was, you could narrow it down by the Description too. Now that you know the exact time frame for the transaction that occurred, you can easily restore the tables/objects to the point in time before the changes occurred. I shall deal about how to restore the objects to the point in time from available backups in the next blog post and I will also talk about another function that will help us track the time frame of particular transactions that occurred in the database when we don’t have the needed logs with us.
Feel free to comment if you think this post was helpful or if you think this post needs some updates or changes from your end.
Posted in ms sql, Troubleshooting, Uncategorized | Tagged , , , , , , , | Leave a comment