Interview with
enStage on 19 May 2014
1.
Explain about your roles and responsibilities?
Daily
Routine Maintenance Tasks
Maintenance tasks
requiring close and regular attention are commonly
checked each day. DBAs who take on these tasks daily
ensure system reliability,
availability, performance, and security. Some of the
daily routine maintenance
tasks include the following:
■ Check that all required
SQL Server services are running.
■ Check Daily Backup logs
for success, warnings, or failures.
■ Check the Windows Event
logs for errors.
■ Check the SQL Server logs
for security concerns such as invalid
logins.
■ Conduct full or
differential backups.
■ Conduct Transaction Log
backups on databases configured with the
Full or Bulk-Logged recovery model.
■ Verify that SQL Server
jobs did not fail.
■ Check that adequate disk
space exists for all database files and transaction
logs.
■ At least monitor processor, memory, or disk counters for
bottlenecks.
Weekly Routine
Maintenance Tasks
Maintenance procedures that require slightly
less attention than daily checking
are categorized in a weekly routine. The
following list details these
weekly tasks:
■ Conduct full or differential backups.
■ Review Maintenance Plan reports.
■ Check database integrity.
■ Shrink the database if needed.
■ Compact clustered and nonclustered tables
and views by reorganizing
indexes.
■ Reorganize data on the data and index
pages by rebuilding indexes.
■ Update statistics on all user and system
tables.
■ Delete historical data created by
backups, restores, SQL Server agent,
and maintenance plan operations.
■ Manually grow database or transaction log
files if needed. Adjust automatic
growth values if needed.
■ Remove files left over from executing
maintenance plans.
Monthly
or Quarterly Maintenance Tasks
Some maintenance task are managed more
infrequently, such as on a monthly
or quarterly basis. Do not interpret these
tasks as unimportant because they
don’t require daily maintenance. These
tasks also require maintenance to
ensure the health of their environment, but
on a less regular basis because they
are more self-sufficient and
self-sustaining. Although the following tasks may
appear mundane or simple, they should not
be overlooked during maintenance.
■ Conduct a restore of the backups in a
test environment.
■ Archive historical data if needed.
■ Analyze collected performance statistics
and compare them to baselines.
■ Review and update maintenance
documentation.
■ Review and install SQL Server patches and
service packs (if available).
■ Test failover if running a cluster,
database mirroring, or log shipping.
■ Validate that the backup and restore
process adheres to the Service
Level Agreement defined.
■ Update SQL Server build guides.
■ Update SQL Server disaster recovery
documentation.
■ Update maintenance plan checklists.
■ Change Administrator passwords.
■ Change SQL Server service account
passwords.
Summary
The maintenance plan feature alone should
be one of the key selling points
for SQL Server 2008. The ability to use an
uncomplicated wizard to automate
administrative tasks that SQL Server will
perform against a single database
or multiple databases has decreased the
amount of manual work DBAs
must do and ensures that tasks do not get
overlooked. To take advantage of
running tasks concurrently, or using
precedence constraints to run tasks
sequentially, you should create plans
manually. This is the best way to
develop maintenance plans for those looking
for a lot of flexibility on
advanced workflow.
SQL Server 2008 continues to allow
organizations to extend their use of
maintenance plans. The following are just
some of the features SQL Server
2008 has brought to the table. SQL Server
2008 offers support for multiserver
maintenance plans, SQL Server 2008 does not
require SSIS to be
installed, and supports the potential for
remote logging.
In the end, the most important thing to
take away from this chapter is the
importance of having a maintenance plan in
place early and ensuring that
maintenance is scheduled accordingly to
preserve the health of each database.
Best
Practices
Some important best practices from the
chapter include the following:
■ DBAs should fully understand all
maintenance activities required and
implemented within the SQL Server
environment.
■ Use the Maintenance Plan Wizard to
automate and schedule routine
maintenance operations.
■ When creating maintenance plans with the
wizard, leverage the
features included in SQL Server 2008 and
create independent schedules
for subtasks.
■ Maintenance tasks should be scripted,
automated, and fully documented.
■ Maintenance tasks should be conducted
during nonpeak times or after
hours, such as on weekends and after midnight.
■ When you configure the order of the
maintenance tasks, backups
should be executed first, and then other
tasks that change the database.
■ Do not include the Shrink Task when
creating Maintenance Plans.
Manually shrink the database if needed
during nonpeak hours.
■ Maintenance tasks should be grouped into
daily, weekly, and monthly
schedules.
■ Schedule and conduct routine maintenance
tasks on a daily, weekly,
and monthly basis.
■ For a large enterprise environment
running many SQL Servers, take
advantage of subplans and the multiserver
maintenance plan.
2.
Why Index and what are the differences between
clustered and Non clustered Indexes? Which helps for good performance?
3.
I’ve ten million records and want to fetch one
record from that, which index is helpful?
4.
Did you ever fix a problem or issue which
occurred before 8 hours? How do you check the issues happened in the past?
5.
What Server Role and DB role required to restore
a database?
6.
Your database size is 500GB, what will be the
Backup size of the database?
7.
What do` you do when customers are complaining
about slow application response? Where do you check for problem and how do you
fix?
8.
What are scenarios to fail the replication?
9.
What are the jobs created when you configure
replication?
10.
How many records you have in your table at max?
11.
What is the maximum db size in your environment?
12.
Why do you want to change?
13.
What is the max size of the table in your
environment?
14.
What is archived database?
15.
What is AWE and its purpose?
16.
How to install hot fix and pre and post setup
actions?
Abstract
This paper recommends a series of best
practices for deploying Microsoft service packs, hotfixes and security patches.
The information contained in this document has been derived from Microsoft
Technical Account Managers, Microsoft Product Support Services (PSS) engineers,
and well-known Microsoft subscription products like Microsoft TechNet and
Microsoft Developer's Network (MSDN).
On This Page
Introduction
Generic Best Practices
Service Pack Best Practices
Hotfix Best Practices
Security Patches Best Practices
Conclusion
Appendix A - Definitions
Generic Best Practices
Service Pack Best Practices
Hotfix Best Practices
Security Patches Best Practices
Conclusion
Appendix A - Definitions
Introduction
Service packs, hotfixes and security
patches are updates to products to resolve a known issue or workaround.
Moreover, service packs update systems to the
most current code base. Being on the current code base is important because
that's where Microsoft focuses on fixing problems. For example, any work done
on Windows 2000 is targeted at the next service pack and hotfixes are built
against the existing available base.
Individual hotfixes and security patches on
the other hand should be adopted on a case-by-case, "as-needed"
basis. The majority of security updates released are for client side (often
browser) issues. They may or may not be relevant to a server installation.
Evaluate the update, if it's needed, then apply it. If not, assess the risk of
applying or not.
For a full description of service packs,
hotfixes and Security Updates, please refer to Appendix A ?Definitions.
The basic rules are:
"The risk of implementing the
service pack, hotfix and security patch should ALWAYS be LESS than the risk of
not implementing it."
And,
"You should never be worse off
by implementing a service pack, hotfix and security patch. If you are unsure,
then take steps to ensure that there is no doubt when moving them to production
systems."
The following guidelines outline the
recommended processes to follow before implementing service packs, hotfixes and
security patches. You can follow them as a step-by-step guide to having a
successful implementation of any Microsoft recommended update.
Generic Best Practices
These apply to all updates regardless of
whether they are service packs, hotfixes or security patches. The generic items
listed below are mandatory steps that need to be performed across all updates.
In addition, there are specific best practices for each type of update, and
these are listed under each update.
·
Use a change control process.
A good change control procedure has an
identified owner, a path for customer input, an audit trail for any changes, a
clear announcement and review period, testing procedures, and a well-understood
back-out plan. Change control will manage the process from start to finish. If
your current procedure is lacking any of the above, please reconsider carefully
before using it for deployment of updates.
·
Read all related documentation.
Before applying any service pack, hotfix or
security patch, all relevant documentation should be read and peer reviewed.
The peer review process is critical as it mitigates the risk of a single person
missing critical and relevant points when evaluating the update.
Reading all associated documentation is the
first step in assessing whether:
o The
update is relevant, and will resolve an existing issue.
o Its
adoption won't cause other issues resulting in a compromise of the production
system.
o There
are dependencies relating to the update, (i.e. certain features being enabled
or disabled for the update to be effective.)
o Potential
issues will arise from the sequencing of the update, as specific instructions
may state or recommend a sequence of events or updates to occur before the
service pack, hotfix or security patch is applied.
Documentation released with the updates is
usually in the form of web pages, attached Word documents and README.TXT files.
These should be printed off and attached to change control procedures as
supporting documentation.
As well as the documentation released with
the updates, a search on the Premier Web site
(https://servicedesk.one.microsoft.com/WRPublic/EN/Consent.asp) for Premier
customers or a search on the public Microsoft Support site (http://support.microsoft.com) needs to
be done for any additional post-release information on the update. TechNet also
has the "List of Bugs Fixed in <product name> Service Pack
<n>" articles. This is a critical document that must be referenced.
·
Apply updates on a needs only basis.
One of the common misconceptions about
Microsoft updates is that they are mandatory and/or urgent.
All updates, regardless of their type
(whether they are service packs, hotfixes or security patches), are to be
applied on an "as-needed" basis. They need to be evaluated
individually and treated as important optional updates.
Especially with security patches, the
expectation is that it must be an urgent issue and must be deployed quickly.
Without trying to detract from the urgency, security patches are very much a
relative update; for example, customers using solely Windows NT4 can ignore a
patch for a security vulnerability in Windows 2000. However, if the issue is
relevant and does plug a security hole, then it should be evaluated urgently.
Only when it addresses or fixes an issue
being experienced by the customer should it be considered. Of course, it still
needs to be evaluated before being installed.
·
Testing.
The prior points really assist in giving
you a feel (before installing) for the potential impact, however, testing
allows for the "test driving" and eventual signing off of the update.
Service packs and hotfixes must be tested
on a representative non-production environment prior to being deployed to
production. This will help to gauge the impact of such changes.
·
Plan to uninstall.
Where possible, service packs, hotfixes and
security patches must be installed such that they can be uninstalled, if
required.
Historically, service packs have allowed
for uninstalling, so verify there is enough free hard disk space to create the
uninstall folder.
·
Consistency across Domain Controllers.
Service packs, hotfixes and security patch
levels must be consistent on all Domain Controllers (DCs). Inconsistent update
levels across DCs can lead to DC-to-DC synchronisation and replication related
problems. It is extremely difficult to trap errors caused by DCs being out of
sync, so it's critical that consistency is maintained.
Where it is practical, Member Servers
should also be updated with the same service packs and hotfixes as the Domain
Controllers.
·
Have a working Backup and schedule production
downtime.
Server outages should be scheduled and a
complete set of backup tapes and emergency repair disks should available, in
case a restoration is required.
Make sure that you have a working backup of
your system. The only supported method of restoring your server to a previous
working installation is from a backup. For more information on backup and
recovery procedures, please refer to:
o "
Backup " in the Microsoft Windows 2000 Server Resource
Kit Server Operations Guide.
o "
Repair, Recovery, and Restore " in the Microsoft
Windows 2000 Server Resource Kit Server Operations Guide.
·
Always have a back-out plan.
A back-out plan will allow the system and
enterprise to return to their original state, prior to the failed
implementation. It is important that these procedures are clear, and that
contingency management has tested them, because in the worst case a faulty
implementation can make it necessary to activate contingency options.
Enterprises may need to exercise their
back-out plan in the event of the update not having an uninstall process or the
uninstall process failing. The back-out plan can be as simple as restoring from
tape, or may involve many lengthy manual procedures.
·
Forewarn helpdesk and key user groups.
You need to notify helpdesk staff and
support agencies (such as Microsoft Product Support Service - PSS) of the
pending changes so they may be ready for arising issues or outages.
In order to minimize the user impact, it is
also a good idea to prepare key user group of proposed updates, this will
assist in managing user expectations.
·
Don't get more than 2 service packs behind.
Schedule periodic service pack upgrades as
part of your operations maintenance and try never to be more than two service
packs behind.
·
Target non-critical servers first.
If all tests in the lab environment are
successful, start deploying on non-critical servers first, if possible, and
then move to the primary servers once the service pack has been in production
for 10-14 days.
Service Pack Best Practices
There are great Microsoft TechNet articles
that reference Service Pack Best Practices. All you need to know can be found
in the documents list below:
Hotfix Best Practices
·
Service Pack (SP) level consistency.
Don't deploy a hotfix until you have all
current service packs installed. A hotfix is related to a service pack and
should be deployed with this in mind. If a hotfix is for post-Windows 2000 SP2,
for example, then you need to ensure that the server has SP2 installed.
·
Latest SP instead of multiple hotfixes.
If multiple hotfixes are to be applied and
these are in the latest released service pack, apply the latest service pack
instead of applying several hofixes, unless issues relating to the latest
service pack may cause the server to break.
Security Patches Best Practices
·
Apply admin patches to install build areas.
It is crucial that not only clients are
retrospectively updated with security patches, but the client built areas are
updated for any new clients. Admin patches are required and differ from the
client patch. They need to be applied to client build areas. The admin patches
are usually located in a different location to the client-side patches. For
example, the admin Office Security patches are available from the Office
Resource Kit Toolbox at: http://www.microsoft.com/office/ork/2000/appndx/toolbox.htm.
·
Apply only on exact match.
Apply these fixes only if you encounter
exactly the issue the fix solves or if the circumstances relate to your
environment.
·
Subscribe to email notification.
Subscribe to the notification alias to
receive proactive emails on the latest security patches.
Conclusion
It is critical that when service packs,
hotfixes, and security patches are required to be installed, that these best practices
be followed. They will guide you through the successful deployment of an
update, and will assist your enterprise's recovery should the update fail.
Appendix A - Definitions
Service packs
Service packs correct known problems and
provide tools, drivers, and updates that extend product functionality,
including enhancements developed after the product released. They get you up to
our current code base. Being on the current code base is important because
that's where we fix the code.
Service packs keep the product current, and
extend and update your computer's functionality. Service packs include updates,
system administration tools, drivers, and additional components. All are
conveniently bundled for easy downloading.
Service packs are product specific, so
there are separate ones for each product. Being product specific does not
however, mean that they are SKU (Stock-Keeping Unit) specific. For example,
Windows NT 4.0 Server and Workstation will use the same service pack.
Service packs are cumulative - each new
service pack contains all the fixes in previous service packs, as well as any
new fixes. You do not need to install a previous service pack before you
install the latest one. For example, Service Pack 6a contains all the fixes in
SPs 1, 2, 3, 4, 5 and 6.
Hotfixes or QFE's
QFE (Quick Fix Engineering) is a group
within Microsoft that produces "hotfixes" - code patches for products
that are provided to individual customers when they experience critical
problems for which no feasible workaround is available.
Hotfixes are not intended for general
installation, since they do not undergo extensive beta testing when they are
created. Microsoft targets hotfix support toward enterprise-level customers and
designs it to provide an extra level of security for mission-critical software
systems.
Groups of "hotfixes" are
periodically incorporated into service packs that undergo more rigorous testing
and are then made generally available to other customers.
They are not regression tested. Hotfixes
are very specific - you should apply one only if you experience the exact
problem they address and are using the current software version with the latest
service pack.
General criteria to meet in order for an
issue to be evaluated for a potential bug fix are:
·
Excessive loss of work or revenue to the
customer
·
No reasonable, Customer accepted,
workaround exists
·
Priority given to Premier accounts
Microsoft offers full n-1 QFE support. This
means that we will support the current shipping version of a product and its
predecessor. "Version" is defined as a new release with some added
functionality and does not include "A" level releases, which are considered
strictly maintenance releases. For example, if version 3.5 is superseded by
version 3.5A, bug fixes will not be done on 3.5 since 3.5A is a maintenance
release. On the other hand, if version 4.21 is superseded by version 6.0 (a
functionality release), bug fixes will continue to be done against 4.21 until
another version ships. If there is not an update to a product, (ex: LAN
Manager) then we will continue to do bug fixes until such point when volume has
significantly slowed. We will apprise our Customers at least 6 months in
advance of us discontinuing QFE support.
Security Patches
Security patches eliminate security
vulnerabilities. Attackers wanting break into systems can exploit these
vulnerabilities. These are analogous to hotfixes but are deemed mandatory if
the circumstances match and need to be deployed quickly.
The majority of security updates released
are for client side (often browser) issues. They may or may not be relevant to
a server installation. You need to obtain both the admin patch and the client
patch as the client patch will retroactively update your client base and the
admin patch will update your client build area on the server.
Great articles on security patches are
available at:
As mentioned above, the admin
patches are located in a different location to the client-side patches. For
example, the admin Office Security patches are available from the Office
Resource Kit Toolbox at http://www.microsoft.com/office/ork/2000/appndx/toolbox.htm.
You also need to be aware that Microsoft
makes available four primary areas to obtain client software security
patches for its products. This means that if users have access to the Internet,
they may have updated their PCs using one of the four mechanisms.
·
Windows Update http://windowsupdate.microsoft.com/.
It uses ActiveX technology to scan the PC to see what has been installed and
presents a list of suggested components that need upgrading based on the most
up-to-date and accurate versions.
·
Recent security bulletins can be found at http://www.microsoft.com/technet/security/current.aspx.
This is the best place to search for purely security-related patches,
especially since it was upgraded to allow searching by product or date.
·
You can also visit product-specific security
patch download pages. They are available for Internet
Explorer (IE) and Office Updates.
The IE download page is a simple, chronological list of security patches that
makes it easy to click down the list and get what you need. Unlike WU, however,
there is no facility for identifying which patches have already been installed.
·
Finally, search the Microsoft
Download Center (MDC) for security-related patches using the keyword
"security_patch." MDC allows searches by product name, product
category, or operating system. Unfortunately, you cannot search by both keyword
and these other categories simultaneously, so it is not as effective a search
mechanism as the security bulletins page. Use this one if you are only
browsing, or if the other methods above failed to produce adequate results.
17.
What are errors come while installing hot fixes?
18.
Hot Fix, Patch and Service Pack differences?
No comments:
Post a Comment