On-Demand Enterprise: Database Automation for the DBA Shortage: “As the amount of data continues to grow, the need for experienced database administrators (DBAs) is exploding. The U.S. Department of Labor and Statistics reports that employment of DBAs is expected to grow significantly and outpace the growth of all other occupations through 2016. The DBA has a very specific, technical set of skills that makes the position one of the most difficult to fill in the datacenter. With only a handful of skilled database administrators, many companies will struggle to meet this demand.”
|
||||
 |
Microsoft Press – SQL Server 2008 Free E-Book Offer: “Introducing SQL Server 2008 by Peter DeBetta Learn about major new features in SQL Server 2008 including security, administration, and performance.” EXECUTE AS vs. SETUSERIn SQL Server 2005, the context in which a string, command, or module is executed can be explicitly set by using the EXECUTE AS statement. EXECUTE AS replaces the SETUSER statement. TRUSTWORTHY Database Property SQL Server Security : The TRUSTWORHY bit database property in SQL Server 2005 References and links · TRUSTWORTHY Database property · Security Considerations for Databases and Database Applications · Understanding Context Switching · Extending Database Impersonation using Execute As SQL Server Security : The TRUSTWORHY bit database property in SQL Server 2005 The TRUSTWORHY bit database property in SQL Server 2005In SQL Server 2005 we introduced a new database property named TRUSTWORTHY bit (TW bit for short) at the database level in order to work as a safeguard to reduce the default surface area regarding some powerful new features: EXECUTE AS USER and CLR assemblies. These new features are really powerful, but without the TW bit they would be ultimately under the control of the DBO and can potentially be misused to escalate privileges in the system MSDTC – Give it a dedicated group or use Cluster Group? – Database ForumThe only reason to give it its own group is performance. 1. SQL SERVER SETUP
Setup a SQL 2005 Standalone Machine
I would strongly recommend that you start by reading about the pre-requisites of installing SQL 2005. http://msdn2.microsoft.com/en-us/library/ms143719.aspx
If you want to install SQL 2005 side-by-side with SQL 2000 or 7.0, you need to be aware of some special considerations. Refer to this article. http://msdn2.microsoft.com/en-us/library/ms143694.aspx
After ensuring the pre-requisites are met, you should follow the steps mentioned in this article to install a SQL 2005 standalone instance on your machine. http://msdn2.microsoft.com/en-us/library/ms143219.aspx
If you prefer to install SQL 2005 from command-prompt, you can do that too by following the steps highlighted in this article. http://msdn2.microsoft.com/en-us/library/ms144259.aspx
Upgrade to a SQL 2005 Standalone instance from SQL 2000 and 7.0 The first step is to check if the upgrade from you existing SQL 2000 edition is supported. You can review the Version and Edition Upgrade Paths Matrix to learn more about it.http://msdn2.microsoft.com/en-us/library/ms143393(SQL.90).aspx
After you have confirmed that a direct upgrade path exists for your edition of SQL Server, you should do a sanity check by running the upgrade advisor. Some old features in SQL 2005 have either been deprecated or removed. To find out if you are using any feature that has been deprecated or removed in SQL 2005, run Upgrade Advisor tool against your SQL 2000 databases. This tool will give you a list of things to correct pre, during and post upgrade process in your database. I would strongly encourage you to run this tool in advance of your upgrade planning. http://msdn2.microsoft.com/en-us/library/ms144256(SQL.90).aspx
After having confirmed that there are no upgrades blocking issues for your database, you should read more about some of the known issues with upgrade of each component of SQL Server and how to avoid them by visiting this link: http://msdn2.microsoft.com/en-us/library/ms143710(SQL.90).aspx
Once the ground work is complete, you can upgrade your existing standalone instance in-place by following the steps highlighted in this article. http://msdn2.microsoft.com/en-us/library/ms144267.aspx
Make sure that you follow the steps in the above upgrade article in the section titled Next Steps. This section talks about updating the statistics after upgrade and also about using DBCC UPDATEUSAGE. These steps are important from performance point of view.
Post Setup and Upgrade Activities Once the setup is complete, it is important to verify that the SQL Server is configured and setup right. You can verify that by using the SQL Server setup logs. However, these logs are verbose and you need to know what you are looking for. To learn how to sniff the right information from these logs, refer to this aticle. http://msdn2.microsoft.com/en-us/library/ms143702.aspx
Setup a SQL 2005 Cluster from ground-up
Since the minimum hardware and software requirements for SQL 2005 have changed, I would suggest that you start by reviewing this article. You can confirm that your hardware and software meets the minimum requirements for sure. http://msdn2.microsoft.com/en-us/library/ms143719.aspx
There are special considerations for SQL 2005 Clusters that you should be aware of. Review this link to ensure that you have configured all the pre-requisites before running the SQL 2005 setup. http://msdn2.microsoft.com/en-us/library/ms189910.aspx
Also, SQL 2005 cluster setup prompts you for domain accounts. You need to have these domain accounts setup in advance. Refer to this article which talks about what to do and some of the best practices for setting up domain accounts. http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B915846
Once the pre-requisites are met, follow the instructions in this article to setup a SQL 2005 failover cluster. http://msdn2.microsoft.com/en-us/library/ms179530(SQL.90).aspx
Upgrade from SQL 2000 and 7.0
The first step is to check if the upgrade from you existing SQL 2000 edition is supported. You can review the Version and Edition Upgrade Paths Matrix to learn more about it.http://msdn2.microsoft.com/en-us/library/ms143393(SQL.90).aspx
After you have confirmed that a direct upgrade path exists for your edition of SQL Server, you should do a sanity check by running the upgrade advisor. Some old features in SQL 2005 have either been deprecated or removed. To find out if you are using any feature that has been deprecated or removed in SQL 2005, run Upgrade Advisor tool against your SQL 2000 databases. This tool will give you a list of things to correct pre, during and post upgrade process in your database. I would strongly encourage you to run this tool in advance of your upgrade planning. http://msdn2.microsoft.com/en-us/library/ms144256(SQL.90).aspx
After having confirmed that there are no upgrade blocking issues for your database, you can upgrade your existing cluster in-place by following the steps highlighted in this article. http://msdn2.microsoft.com/en-us/library/ms191295(SQL.90).aspx
Once the setup is complete, it is important to verify that the SQL Server is configured and setup right. You can verify that by using the SQL Server setup logs. However, these logs are verbose and you need to know what you are looking for. To learn how to sniff the right information from these logs, refer to this aticle. http://msdn2.microsoft.com/en-us/library/ms143702.aspx
Maintenance of a SQL 2005 Cluster Here is a list of a few How-To articles that should come in handy to learn about the best practices of doing the mundane tasks. http://msdn2.microsoft.com/en-us/library/ms190990(SQL.90).aspx
Also, on a cluster, a lot of stuff is different from a standalone box. Here is a list of clustered SQL do’s and don’ts – a must read. http://support.microsoft.com/?id=254321
2. SQL SERVER GENERAL PERFORMANCE
SQL 2005 Whitepapers
Statistics used by SQL 2005 As we know, existence and the subsequent updates of statistics is a very important factor governing SQL Server query performance. However, the implementation details are different in SQL 2005. Understand the nuances by referring to this article. http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
Caching in SQL Server 2005 and how it impacts performance SQL Server, just like any other RDBMS, tries to minimize disk I/O. It achieves this balance by caching the data in memory. However, since memory is a limited resource, it is imperative that you implement the best practices to maximize your use of it. This is an excellent white paper that talks about the internals of SQL Server caching, with a focus on compilations and re-compilations of batches in SQL 2005. http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Performance Analysis and Troubleshooting SQL Server 2005 This is an excellent whitepaper that provides scripts and queries to monitor, identify performance bottlenecks and suggests improvements. I would strongly recommend this – a must read. http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
SQL 2005 Webcasts I thought I will also provide some links to useful webcasts. I love the format of the webcasts and the ease of replaying stuff at will.
Performance Diagnosis in SQL 2005 All production databases need to be high performing. However, often times, we don’t have solid performance benchmark numbers which we should aim and maintain in the long run. The first step, then is to monitor SQL Server performance during the good and bad times. Based on the data collected during bad times and comparing them to the one collected during good times, we can see what is causing this slow performance. This 300 level webcast talks about performance monitoring and troubleshooting best practices.
Troubleshooting Performance Problems in Microsoft SQL Server 2005 Once you have monitored the performance over a period of time, you can use the techniques in this webcast to troubleshoot the performance bottlenecks. This webcast provides some really good practical advise on troubleshooting performance.
SQL 2005 Blogs The folks who wrote the code in SQL Server have blogs of their own. These blogs provide in-depth knowledge about SQL Server internals – like the query optimizer, the SQL OS etc. – stuff that is not there either in Books Online or at MSDN. I would strongly encourage you to read these blogs, if you really want to learn about the esoteric internals of SQL Server.
Slava Oks Blog on SQL OS and Memory
Blog on Query Optimizer written by the folks who wrote the query optimizer http://blogs.msdn.com/queryoptteam/
SQL Tips and Tricks Blog – handy tips and tricks to do mundane tasks in SQL 2005 http://blogs.msdn.com/sqltips/default.aspx
3. SQL SERVER BLOCKING Here is the list of resources which you can use to learn more about SQL Server blocking concepts and how to troubleshoot them.
So what is blocking? Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency. On SQL Server, blocking occurs when one SPID holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource.
Identifying blocking Microsoft has published a script that you can use to monitor blocking on your server. This script creates a SP, which continuously pings syspro http://support.microsoft.com/kb/271509/EN-US/
Understanding the nuances of blocking This KB talks at length about the different scenarios in which you can experience blocking. You will learn how to identify blocking in SQL 2000, which tools to use and how to resolve blocking using some of the best practices. http://support.microsoft.com/kb/224453/
Another scenario in which you can experience blocking is lock escalation in SQL 2000 and 2005. You can identify blocking caused by lock escalation by following the steps highlighted in this KB. http://support.microsoft.com/?id=323630
Based on the output of the blocker script in KB 271509, you can see the different waittypes for each process. To help you decipher the meaning of each waittype, refer to this KB. http://support.microsoft.com/?id=822101
Special Considerations for SQL 2005 In SQL 2005, it is recommended that you use the DMVs instead of the regular system tables. In SQL 2000, we used to query sysprocesses, but it is only provided for backward compatibility in SQL 2005 (http://msdn2.microsoft.com/en-us/library/ms179881.aspx). Instead, you can use sys.dm_os_wait_stats (http://msdn2.microsoft.com/en-us/library/ms179984.aspx) You can still use the blocker script in KB 271509 against SQL 2005. However, a better way to identify and resolve blocking in SQL 2005 is described in this whitepaper under the section titled Blocking. http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EYBAG In the appendix, you will find a simple query which you can run recursively to get the output of the DMVs. You can store this output in a table or a flatfile and use the same strategies to resolve blocking in SQL 2005.
I got this emailed to me as well – again, some good points. 22 WAYS TO STRIKE OUT WHEN INTERVIEWING
A recent survey was conducted among 150 companies to discover why a candidate who is capable of handling a job is not hired. The responses all relate to the crucial interview period. Twenty-two inevitable ways to “strike out” are listed below. If you really want the job, be careful to avoid them all.
1. POOR PERSONAL APPEARANCE A company will be more likely to hire someone who is well groomed and will represent their company in a professional manner.
2. LACK OF INTEREST AND ENTHUSIASM A passive and indifferent attitude is instantly recognized as “I don’t care if I get this job.” Sincere interest and enthusiasm about the position is essential.
3. TOO MUCH EMPHASIS ON MONEY Your interviewing goal is to sell yourself to the interviewer and get an offer of employment. Salary discussion is secondary.
4. CONDEMNATION OF PAST EMPLOYERS Present facts only surrounding the past positions. Interviewers will be sensitive to comments about past employers. Too much information may come across as gossip.
5. TALKING TOO MUCH Answer questions as asked without being abrupt; expound only to the point that the interviewer has a clear understanding of what you mean. Unnecessary conversation wastes time.
6. WEAK HANDSHAKE The interviewer’s first impressions are lasting impressions. A firm handshake shows confidence in yourself and you abilities.
7. REFUSAL TO TRAVEL AND/OR RELOCATE Always be open for discussion concerning travel and relocation. The employer may be talking future plans, not present.
8. BEING LATE FOR THE INTERVIEW Tardiness is a sign of irresponsibility. This demonstrates a lack of interest and what the employer can expect in the future.
9. FAILURE TO SEEM INTERESTED ABOUT THE POSITION An interviewer will be impressed by an eager and inquisitive mind. Don’t hesitate to ask questions concerning the company or the position you are interviewing for.
10. NO DEFINITE CAREER OBJECTIVES Don’t be caught off guard! Try to anticipate questions you will be asked and have answers prepared in advance. Uncertainty and disorganization show you do not know what your goals are.
11. OVERBEARING, CONCEITED, KNOW-IT-ALL ATTITUDE An upfront confident discussion of talents and achievements is more likely to work in your favor, than incessant bragging about yourself.
12. INABILITY TO EXPRESS YOURSELF CLEARLY Don’t let your mouth get ahead of your mind. Take a moment to think and construct your answers to questions before rushing into a vague and senseless reply. Interviewers will carefully watch and listen to your communication skills.
13. LACK OF CONFIDENCE AND POISE Everyone will be somewhat nervous during the interview; this is to be expected. However, preparation to handle the interview will come across as confidence in your ability to handle the job.
14. TOO MUCH CONCERN ABOUT RAPID PROMOTION IS A TURN-OFF Few people are able to automatically jump to the top of the ladder. Let the interviewer know that you are willing to learn the business from the ground up.
15. LACK OF LONG TERM COMMITMENT An employer will not waste his time with you unless you convince him that the position in question is THE ONE that you want to make as your career. A “merely shopping around” attitude on your part displays lack of interest.
16. LACK OF INTEREST IN THE COMPANY OR PRODUCTS It will be obvious to the interviewer that you will not be able to effectively contribute to the company or industry if you have no real interest in its products and/or services.
17. INTOLERANT PREJUDICES They tend to narrow your perspective and could easily keep you from performing to your fullest potential in the position.
18. INABILITY TO TAKE CRITICISM Take it without flinching and in a constructive manner. Agree that you need to work on whatever is being criticized and promise to improve in that area.
19. SECOND-GUESSING THE INTERVIEWER Let the questions be asked before you answer. Do not volunteer irrelevant information. Over-aggressiveness does not gain favor with the interviewer.
20. LOW MORAL STANDARDS Personal ethics parallel business ethics. If your personal morals are questionable, your business ethics will be viewed in a similar manner. Although you only work eight hours a day you represent your employer 24 hours a day.
21. DISPLAYS OF LAZINESS No one wants an employee who is afraid of hard work. Show a desire to earn your salary.
22. LACK OF EYE CONTACT Failure to look at the interviewer when conversing will cause the interviewer to doubt you sincerity. Direct eye contact will assist in supporting your statements.
If your behavior reflects any of the above “strike out” areas you can count I got this emailed to me and it’s got some good points….
TWENTY GOOD ANSWERS TO HELP YOU MASTER EVEN THE MOST GRUELING EMPLOYMENT INTERVIEW
Most people make two devastating mistakes when they are being questioned in an interview. First, they fail to listen to the question. They proceed to annoy the interviewer either by answering a question that wasn’t asked, or by giving out a lot of superfluous information.
Second, and more important, they attempt to answer questions with virtually no preparation. The glibbest person on earth, even the most skilled debater, cannot answer questions off the cuff without damaging the chances for success.
What follows are twenty questions that various surveys have indicated are asked most often, regardless of the job classification. Study them carefully, develop strong responses, and your candidacy will receive prime consideration.
Because you have done your homework on the company, you know exactly why you want to work there. Just organize your reasons into several short hard-hitting sentences like “You make the best product on the market today. Your management is farsighted enough to reinvest the company’s profits so that you will soon be the leader in this category”.
The interviewer does not want a lengthy regurgitation of your resume. They do not want a barrage of facts and figures. They are interested in testing your poise and confidence. So give a short, generalized summary like “I have the qualifications to do the job that needs to be done and my track record proves it”.
Give a truthful-but-brief answer like “The challenge” or “The future”, “The environment” or “The competitiveness”. This response will force the interviewer to ask you to explain, giving you yet another opportunity to demonstrate your profound knowledge of the company.
By all means, “Yes!”. Ambitious, hungry people are always preferred over those who would settle for a safe routine. If you sense that this answer may threaten your interviewer’s security, you might want to add, “when I am judged qualified” or “should an opening develop in several years”.
Keep your answer job related. If you exaggerate contributions to major projects you will be accused of suffering from “coffee-machine syndrome”, the afflictions of the junior clerk who claimed success for an Apollo space mission based on his relationships with certain scientists, established at the coffee machine. You might begin your reply with “Although I feel my biggest achievements are still ahead of me, I am proud of my involvement with…..I made my contribution as part of that team and learned a lot in the process. We did it with hard work, concentration and an eye for the bottom line”. 6. What kinds of decisions are most difficult for you?
Be human and admit that not everything comes easily. But be careful about what you do admit. “I find it difficult to decide which of two good employees must be let go”. Or “ It is difficult for me to tell a client when he’s running his business badly.”
7. How do you feel about your progress to date?
Never apologize for yourself. “I think I’ve done well, but I need new challenges and opportunities”. This is a good time to drop hero stories. “No one in my company has advanced as fast as I have. I think you’ll agree that I’ve accomplished quite a bit in the last five years”.
A reasonable response would be, “As long as I continue to learn and grow in my field”
This is best answered with some degree of self-effacement. “I would be lying if I told you I was perfect, but I have always tackled assignments with all my energy and talents”.
To answer this question, make sure you know exactly what can or cannot be accomplished by the ideal candidate in your shoes. Too many job-hunters butcher this question because they have not done their homework and have no idea where their career will lead them. If you see yourself at another company or in another department of the company you are interviewing with, the tread lightly. You can’t afford to tell your interviewer that you think you’ll be more successful than they will.
Deliver a short, fact-filled summary of your two or three most important qualifications. “I have a background in accounting. I’ve demonstrated proven selling skills. I’m capable of handling several projects simultaneously”.
This is one of the first questions interviewers ask. Be sure you are ready to answer it satisfactorily. If you are currently in a dead-end position, locked out of advancement opportunities, explain this. The interviewer will understand. If your job has become routine, and void of learning ex
You must demonstrate good use of your time, that you believe in planning your day beforehand, and that when it is over, you review your own performance to make sure you are reaching the desired goals. No one wants a part-time employee, so you should sell your energy level. For example, your answer might end with: “At the end of the day when I’m ready to go home, I make a rule always to type one more letter (make on more call, etc.) and clear my desk for the next day”.
This question is crucial. In fact, an unsatisfactory answer to this one is among the top reasons why candidates fail to get the jobs they want. Convince the interviewer that your job-hopping days is over. If you feel it was a mistake leaving previous jobs so soon, say so, and at the same time remind the interviewer that your performance was never in question. Honesty is appreciated. If something in your personal or business life has recently changed and would affect your future stability, come right out with the facts.
Isolate high points from your background and build in a couple of the key value profiles from different categories. You will want to demonstrate pride; reliability and the ability to stick with a difficult task yet change course rapidly when required. You can rearrange the previous answer here. Your answer in part might be: “I believe in planning and proper management of my time. And yet I can still work under pressure”.
Have your hero stories ready and be willing to prove that you have made significant contributions in one or more of these basic areas. Keep your explanations short and try to include specific dollar amounts.
This is a current favorite, which can frighten the wits out of an unsuspecting candidate. One of the following responses should cover your situation: “I have been willing to sacrifice short-term earnings in order to gain valuable experience”, or “I have received (been promised) company stock (or other benefits) in lieu of a salary increase” or “I was reluctant to gain a reputation as a job-hopper, preferring instead to build my career on solid, long-term achievement”. These work.
Similar to the “hired/fired” question, the interviewer is trying to determine the depth f your experience. Do not exaggerate!
It is best to keep this answer very general, permitting the interviewer to probe more deeply. Offer a short list of positive character traits that describe YOU> “I like to work hard”. “I get along with all kinds of people” and I know how to listen>” or “I pay close attention to details, I know how to watch costs and I can keep difficult customers smiling.”
Summarize four or five key areas of experience, which you know you can bring to your new job. Demonstrate how each one will help the interviewer’s company solve their problems. For example, “My experience in new product introductions will be very helpful to your entire marketing effort”, or “My industrial design background will strengthen your sales-force capability in dealing with large clients”.
THE KEYS TO INTERVIEW SUCCESS
There are 20 universally admired key personality traits; they are your passports to success at any interview. Use them for reference as you customize your answers to these tough questions.
Personal Profile:
The interviewer searches for personal profile keys to determine what type of person you really are. The presence of these keys in your answers tells the company representative how you feel about yourself, your chosen career, and what you would be like to work with. Few of them will arise from direct questions-your-future employer will search for them in your answers to specific job-performance probe. The following words and phrases are those you will project as part of your successful, healthy personal profile.
Professional Profile:
All companies seek employees who respect their profession and employer. Projecting these professional traits will identify you as loyal, reliable, and trustworthy.
Achievement Profile:
Projecting your business profile is important on those occasions when you cannot demonstrate ways you have made money, saved money, or saved time for previous employers. These keys demonstrate you are always on the lookout for opportunities to contribute, and that you keep your boss informed when an opportunity arises.
As the requirements of the job are unfolded for you at the interview, meet them point-by-point with your qualifications. If your experience is limited, stress the appropriate key profile traits (such as energy, determination, and motivation), your relevant interests, and your desire to learn. If you are weak in just one particular area, keep your mouth shut-perhaps that dimension will not arise. If the area is probed, be prepared to handle and overcome the negative by stressing skills that compensate and/or demonstrate that you will experience a fast learning curve. Do not show discouragement if the interview appears to be going poorly. You have nothing to gain by showing defeat, and it could merely be a stress interview tactic to test your self-confidence.
If for any reason you get flustered or lost, keep a straight face and posture; gain time to marshal your thoughts by asking “Could you help me with that?” or, “Would you run that by me again?” or, “That’s a good question; I want to be sure I understand. Could you please explain it again?”
|
|||
|
Copyright © 2013 SQL Feather and Quill - All Rights Reserved |
||||