|
|
Well I missed class #3 due to SQLPass. I came in cold to class number four and I had to give two demonstrations of techniques used for public speaking. The first one was called the “LIONS” technique. It’s an acronym that stands for a checklist of things to go over before you speak to make sure you communicate your ideas clearly. As a guy who doesn’t get out from behind the keyboard too often, I’ve learned that this technique works very well to help me explain a thought or an idea. Here’s what it stands for: 
|
L
|
Language – are you using the correct language for your audience? |
|
I
|
Illustrations – are there any illustrations you can use to show your audience? |
|
O
|
Organize – are your points laid out in a logic order? |
|
N
|
Narrow your subject – when speaking with a time limit, often you must cut down your subject to key points |
|
S
|
Summarize – at the end, summarize your talk into two to three main points. |
Class #2 was pretty interesting. By the end of the class I was able to remember everyone’s name (27 attendees, 1 instructor and 2 other coaches). I also got to listen to everyone speak on a defining moment in their life. It was very interesting and inspiring.
One of the exercises we did was a technique to help memory called pegging. Here’s a full break down of a slightly different system (same ideas) from buildyourmemory.com. The idea is that if you’ve got a list of things to remember to link the ideas to a picture. There are permanent peg words and permanent peg images, these images are slightly modified to fit of what you are trying to remember.
| Item Number |
Permanent Peg Word |
Permanent Peg Image |
How to Change the image: |
| One |
Run |
 |
Instead of Jockeys in the saddles of these horses, envision the object you are trying to remember |
| Two |
Zoo |
 |
Change the flower in the monkey’s hand to the second item you are trying to remember, then envision that monkey throwing that item at you. |
| Three |
Tree |
 |
Picture the third item you are trying to remember as the cause of this tree being bent over. |
| Four |
Door |
 |
Picture this door stuck and what’s blocking this door from moving is the item you are trying to remember. |
| Five |
Hive |
 |
Instead of bees in this hive, picture the fifth item you are trying to remember as the bees, they may even be coming after you. |
| Six |
Sick |
 |
Change the medicine that is in this syringe to the sixth item you are trying to remember. |
| Seven |
Heaven |
 |
These are stairs to heaven. Picture the seventh item rolling, falling, etc. down these stairs. |
| Eight |
Gate |
 |
What’s making this railroad gate come down? The eighth item you are trying to remember is weighing it down. |
| Nine |
Wine |
 |
Instead of wine in this bottle, it’s the ninth item you are trying to remember. Imagine how surprised you are when you think you are pouring wine and out pops the item. |
Proof it works: The class listed nine items on the board, another one of the coaches memorized the list in about a minute. He turned his back to the board. People shouted out random numbers and he got 7 out of 9 items – pretty impressive for a minute of memorizing the list.
I’m going to miss class #3 as I’ll be at SQLPass – that should get me back on blogging about SQL topics.
I began my coaching of the Dale Carnegie Effective Communications and Human Relations class this week. In the first class, we did a few cool things. The first I’ll talk about is a tool that is basically “topics for small talk”.
In your mind, picture a great big mahogany name plate. On that name plate is a Victorian house with large pillars. In the front yard, there are adults, kids, dogs, cats and a bird. Coming out of the chimney is large orange work glove that is grabbing on to a 757 plane. The 757 has two things that are different than normal – it has a hockey stick and violin for propellers and it also has a blinking red light on the tail.
That’s it – it’s a checklist of things to talk about when meeting somebody new. Ask some questions about these topics, and you’ll learn a lot:
- Their name (name plate).
- Where they live (the large victorian house) – this can lead to where they are from, etc.
- Their family – and friends – and pets (the people and animals in the yard).
- Their work (the orange work glove).
- Their travels (the plane).
- Their hobbies/athletic interests (the hockey stick/violin propellars).
- Anything obvious that sticks out or things to remember (the red flashing lights).
It sounds a little basic, but it really helps. As my wife and I were in the car for a couple of hours a couple of years ago, I started to ask questions about these topics and I could believe how much I learned about her. Simply by asking open ended questions on these topics, I learned a lot about her that day – which I thought I knew before we got married! It made the hours in the car go by quickly!
The other crazy thing we did was learned everyone’s name in class. Sounds simple, but this is a three hour class and there are 28 students and the instructor in the class. So there are 58 names (First and Last names) that we learned in about 30 minutes. We all came up with a mental image for our name based on some guidelines. Then we went around the room and shared (and reviewed frequently). By the end of the 30 minutes, I got 50 of 58 names correct – that’s 86%. I missed both the first and last name on two people and then missed four others first or last names on others. It’s pretty amazing – I’m horrible at names!
Here’s mine:
 It’s Andy Lohn – “and he mows the lawn.” So when you meet me, just picture this and it’ll help you remember my name.
I’m looking forward to this week – it should be fun.
As mentioned in a previous post, I’m going to be coaching a Dale Carnegie Effective Communications class. The other night I attended a training session for current and future coaches. The topic that we covered and went in-depth on  was how to give feedback. An effective way that we learned is based on a three part formula. Being a computer and math guy, I thought it’s pretty easy to fit things into a formula, so I made a game of it.
The Formula
The formula for a response/compliment is as follows:
- Tell the person the a strength they possess
- Show evidence of that strength
- Tell them how that strength will help them in the future
The Challenge
In the last two weeks, I’ve attempted to give at least one compliment a day to a co-worker. It’s not worked everyday, but it has helped me and the team I’m working with quite a bit. People seem to be more confident around me and proud of what they’ve done. I’ve made a stronger connection with a few the people as well.
The Example
Today, for example, I was troubleshooting an issue with a less experienced DBA. He was driving the workstation we were working on and jumping around like he knew the next move. I simply stopped and said, “Wow, you’ve really picked up on the environment around here. You’re two moves ahead of where I’d be and jumping to shortcuts I didn’t know existed. This will really help you in the future to be more efficient.” This was a simple example, but he was all smiles the rest of the day and even showed me a couple of quicker ways to do things.
The Payoff
As computer geeks, we sometimes sacrifice our social skills. Try giving out a few compliments and you’ll be amazed at the response and help you’ll get from others. You may even pick up a few new short cuts like I did.
When someone states that adding a column to an existing table is easy and you need a list of risks, feel free to copy and paste this, add your own, etc. In fact, contact me via email, comments, twitter – @SQLQuill with any additions. Adding a column to an existing table – in theory and best practices world is easy – but in reality it is not always as easy as ALTER TABLE ADD COLUMN. The risk is that any procedure, view or application code that uses a “Select *” or an “Insert <TableName> Values <…>” (without column names explicitly stated) may not function, or may not function correctly, with an additional column added to the table. Also note there could be temp tables, table variables and table value parameters built off the table that an additional column affects as well. Best practices and my database development guidelines says not to use the “Select *” or the “Insert <TableName> Values <…>”.
So apparently I like scripts that create random data. I’ve written a couple of posts that create random numbers and random characters. This week I was working on a clean up job that would delete data that was over 50 days old. To test my clean up routine, I had to combine the random number script with the DATEADD function – here’s the basis for it:
Select Dateadd(D, (-1)* ROUND((RAND() * (100)),0), Getdate())
Breaking it down, the ROUND((RAND() * (100)),0) section randomly chooses an integer from 0 to 100, then multiplies that by –1 as we want to go back in time. Finally the DATEADD function adds the negative random integer number of days to today’s date.
Here’s an example that populates a test table with 1000 rows of data:
SET NOCOUNT ON
Go
Declare @RandomDates Table (DateTest datetime)
Declare @Counter int
Set @Counter = 0
While @Counter < 1000
Begin
Insert @RandomDates (DateTest)
Select Dateadd(D, (-1)* ROUND((RAND() * (100)),0), Getdate()) --Random Date 0 to 100 days old
Set @Counter = @Counter + 1
End
Select Datediff(d,DateTest, getdate()) as DaysOld,
Count(Datediff(d,DateTest, getdate())) as Count
from @RandomDates
Group by Datediff(d,DateTest, getdate())
Order by DaysOld Desc
A few years ago I found this procedure on SQLServerCentral – I believe (but I can’t find it today) – it creates a random password with options for upper case, lower case, special characters and numbers. I liked this procedure as I’m kind of a security guy and I use it for all my random password needs. Note, this was written and tested for SQL Server version 7.0 and 2000 – a good thing lasts. Anyway, recently I’ve been doing some testing and in need of creating test data, so, not only did I create this script to populate a table, I’ve now been tweaking the password generator procedure.
Here’s the basic’s of it.
Select CHAR(ROUND(97 + (RAND() * (25)),0)) as 'Lowercase',
CHAR(ROUND(65 + (RAND() * (25)),0)) as 'Uppercase',
CHAR(ROUND(48 + (RAND() * (9)),0)) as 'Number',
CHAR(ROUND(33 + (RAND() * (13)),0)) as 'SpecialCharacter'
For Lower Case letters, here’s how it works:
- The RAND() function randomly picks a float from 0 to 1
- That random float is multiplied by 25, so it could be any number 0 through 25 – note 26 integers
- Add 97 to that, so the number now could be 97 through 122.
- The ROUND() function rounds that number to the nearest integer as it’s passing in a 0 for length — ROUND(<<Num>>, 0), so we’ve got integers 97 though 122.
- Finally, those integers are passed into the CHAR function, which converts an integer to a character.
The same idea’s apply to the upper case – except integers ranging from 65 to 90, etc.
Here’s a chart for the integer ASCII code to a characters
| Integers |
Characters |
| 97 – 122 |
Lower case |
| 65 – 90 |
Upper case |
| 48 – 57 |
Numbers 0 – 9 |
| 33 – 46 |
Characters !”#$%&’()*+,-. |
*Note the Char(10) is a line feed and Char(13) is a carriage return
Here’s a script to see all the integer ASCII code to a characters
Declare @TTable table (Number int, Character varchar(5))
Declare @TData int
Set @TData = 33
While @TData < 256
Begin
Insert @TTable Select @TData, CHAR(@TData)
Set @TData = @TData + 1
End
Select * from @TTable
Here’s a good example that uses this idea to create a view of a fixed length column – kind of a brute force method, but sometimes when your in a bind, it might just be crazy enough to work.
Backups and Backup StrategiesMicrosoft’s SQL Server Backup Overview
http://msdn.microsoft.com/en-us/library/ms175477.aspx
What we use (all definitions taken from link above):
1. Full backups
A full backup contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.
2. Differential backups
A differential backup is based on the latest full backup of the data. This is known as the base of the differential, or the differential base. A differential base is a full backup of read/write data. A differential backup contains only the data that has changed since the differential base. Typically, differential backups that are taken fairly soon after the base backup are smaller and faster to create than the base of a full backup.
3. Transaction Logs
Under the full recovery model or bulk-logged recovery model, regular transaction log backups (or log backups) are required. Each log backup covers the part of the transaction log that was active when the backup was created, and it includes all log records that were not backed up in a previous log backup.
Notes:
Our naming conventions are based on: 3 characters Location Code 2 characters domain 2 characters site code 2 – DB for Database 2 Numbers for server # 3 – SQL for clustered server Differential backups overview….not dependant on other differential backups
| Sunday |
Monday |
Tuesday |
Wednesday |
Thursday |
Friday |
Saturday |
| Full Backup |
Differential Backup |
Differential Backup |
Differential Backup |
Differential Backup |
Differential Backup |
Differential Backup |
| Everything |
X |
X |
X |
X |
X |
X |
| |
|
Y |
Y |
Y |
Y |
Y |
| |
|
|
Z |
Z |
Z |
Z |
| |
|
|
|
W |
W |
W |
| |
|
|
|
|
Q |
Q |
| |
|
|
|
|
|
T |
To restore to w, all you need is Sundays full backup and Thursday’s differential backup
Logfile backups overview….dependant on other log files backups
| 2:00 AM |
5:00 AM |
8:00 AM |
11:00 AM |
2:00 PM |
5:00 PM |
8:00 PM |
To restore to w, you need is 2:00 AM full backup, 5:00 AM log backup, 8:00 AM log backup, 11:00 AM log backup and 2:00 PM log backup.
General DBA Responsibilities
- Availability/Recoverability
- Security
- Performance
- Develop/Deploy
RecoverabilityRecoverability means that, if a data entry error, program bug or hardware failure occurs, the DBA can bring the database backward in time to its state at an instant of logical consistency before the damage was done. Recoverability activities include making database backups and storing them in ways that minimize the risk that they will be damaged or lost, such as placing multiple copies on removable media and storing them outside the affected area of an anticipated disaster.SecurityPrincipals are entities that can request SQL Server resources. Like other components of the SQL Server authorization model, principals can be arranged in a hierarchy.Windows-level principals
- Windows Domain Login
- Windows Local Login
SQL Server-level principal
Database-level principals
- Database User
- Database Role
- Application Role
SQL 2005 Versions
- Express – Free and lite, sometimes distributed with applications, run on desktop – RAM, DB size, etc limitations
- Workgroup – Less features than standards not used too often
- Standard – four CPUs max, no partitioning – we use this on all but servers with partitioned tables
- Enterprise – All the features
Full Explanation here….http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspxIntroduction to General SQL Architecture SQL has 2 main servicesSQL Server – runs the serverSQL Agent – runs schedulerSystem Databases – Master, Model, MSDB, TempDBUser Databases – 2 filesData File – .mdf (default extension)Log File – .ldf (default extension)Overview of SQL Server 2005 for the Database AdministratorGood site that explains lots of the features and services SQL2005 can provide including…
- SQL Service Broker
- Reporting Services
- Notification Services
- High Availability and Scalability options
- Plus more…
|
|
