Category: Uncategorized

Wake up, Seattle!

Good morning from the blogger table on day 2 of PASS Summit!  Yesterday was an amazing, wonderful, jam-packed day for sure!  Did you meet tons of new people?  Did you meet your #DataHero?  I met so many amazing, wonderful people!  Honestly, that is my most favorite part of any convention and my #SQLFamily make PASS Summit the best ever!

KeyNote

As I sit here waiting for the keynote to begin, I am getting the opportunity to chat with other bloggers and all the awesome attendees who come say hello.  It is quite amazing being here with my data heroes like Brent Ozar, Kellyn Pot’vin-Gorman, Tim Mitchell, Kendra Little and Kevin Kline (among so many others!).  Take a look at their blogs, they are sincerely amazing people with amazing amounts of knowledge!

I Will Survive!

Wendy Pastrick starts us off today with a great reminder that COMMUNITY is what makes us awesome! She broke into song when she had to break down the finances.  She was amazing and received a well-deserved round of applause.  Seriously, if you have never heard her sing, you should do #SQLKaraoke with her!

First Timers, Diversity, Awesomeness

Next up Tim Ford lets us know that 40% of our attendees are First Timers!  How amazing and wonderful!  16 years ago he was a shy first timer, I cannot even imagine Tim ever being shy!  Tim also spoke to PASS Summit diversity.  I can attest to this being the most diverse tech group I have ever seen!  As a woman in tech, I feel more welcome and appreciated than I could ever describe.  And as I look around, I see such a diverse group of beautiful, wonderful people in the audience.

25 years of SQL Server

What an amazing treat!  We were introduced to some great stories from the past with SQL Server heavy hitters and past and current VPs: Ron Soukup, (1989-1995), Paul Flessner, (1995-2005), Ted Kummert, (2005-2014), Rohan Kumar (2016-present).  Surely 25 years of hard work and leadership!

Raghu Ramakrishnan

Raghu Ramakrishnan, CTO for Data Microsoft, shared “The cloud changes how we think about building a database, data is elastic, on demand.”   I had not stopped to think about how the cloud has changed the landscape of data.  We often think of data as mere numbers and letters, something static that gathers and sits until we are ready to use it.  However, data is something much larger, it is a living, breathing, ever-growing, changing life force of information that needs to be nurtured.  This gives me more of a personal insight in the way I need to address my projects.  Mind blown!

SQL Hyperscale

Project Name: Socrates (Socrates was a data guy!) “The unexamined database is not worth writing”

If you missed the quote Raghu posted, I found it.  I laughed a lot, apparently not much has changed!

“The children now love luxury; they have bad manners, contempt for authority; they show disrespect for elders and love chatter in place of exercise. Children are now tyrants, not the servants of their households. They no longer rise when elders enter the room. They contradict their parents, chatter before company, gobble up dainties at the table, cross their legs, and tyrannize their teachers.”

Truly, this keynote is full of so much great information, I am lost in it all.  I will go back and review the keynote as many times as possible to better take in what all was covered.  Such a testament to the hard work put in by the teams at Microsoft.

 

 

 

 

 

 

The most simple answer is “family.”

Good morning from the  #PASSSummit keynote blogger table!

Today, PASS President,  Grant Fritchey opened the first day of #PASSSummit #V20 with a rousing “Good Morning!” to all in attendance for the keynote.  With this being the 20th celebration of Pass Summit, a lot of focus has been placed on memories and stories from past years.  As we look back, we look forward.  I love hearing how attendees have grown and changed with each passing Summit.  Last night I met a number of first timers and I hope to hear their stories in the coming years.  The common theme I hear in the stories is one of community.  In today’s (sometimes very harsh) social climate, the stories of community and friendship are a sweet song that play upon my heart.

As this wonderful week at PASS Summit progresses, I hope that each of you have the chance to make new friends, reconnect with old, and learn many new ways to enrich your life and career. To my new #FirstTimers friends, remember to take these three things with you:

1. You are never alone
2. We are stronger together
3. Lift as you rise

Your Models are Only As Good as Your Data

Along with family and networking, we learn A LOT at PASS Summit. There many fantastic announcements by Rohan Kumar, Corporate Vice President of Azure Data, Microsoft regarding innovations and the release of SQLServer 2019.  As my interests mainly lie in the Analytics realm, today was mind blowing!  I see that my recent decision to go forth and learn #DataScience was a great decision.  SQLServer 2019 promises to be an all-in-one tool for the busy data professional.  I am very excited to get in and start playing with all the new analytics and ML features.

#WasabiMustardSauce

If you missed Patrick LeBlanc’s first time on the “big stage”, you missed the intro of some amazing PowerBI things, and the introduction of a new standard on the heat scale….hot as wasabi mustard sauce!  Great job, Patrick!

Have a great day at Summit, everyone and make some new friends, meet your data heroes, and make memories that will last a lifetime!

As I sit here in the Denver airport watching people go by, I am struck by how different and wonderful each of us are.  I have this rare moment in time where I am just sitting and waiting.  Waiting for my time to board my plane to head home from #SQLSatDenver.  Instead of spending my time playing a game on my phone or catching up on the news (that I have been blissfully avoiding this weekend), I chose to just sit and do nothing.

Learning that No is okay

I learned something really important this weekend from Jes Borland’s session “Busy is a Four Letter Word.”  I learned I can say NO.  hard no, soft no, easy no, not really a no but a no…..I have all the power to say NO.  Funny how it took flying so far away from my home (read that as total comfort zone) to be assured that I can say NO.  I can even say NO to MYSELF.

This morning I have practiced that.  I told myself to not check the news, not spend my time waiting by playing mind-numbing games.  I told myself to embrace the stillness of my life at the moment.  There is no dishes or laundry to clean, there is no pet to feed, there is no child or spouse to attend to…. this time is all mine.  So, I sat, and I watched.

I watched as frustrated men in wrinkled business suits drank dark beers.  I watched as an older lady, traveling alone, enjoyed her “well-shaken” martini.  I watched as a sweet married couple shared loving glances and giggles over coffee.  I watched as an angry couple fought over “petty but still frustrating” things that had happened in their past week.  I watched as the staff of the United Club busied themselves with picking up after people who were just too busy to find the trash can.  I noticed that not many people stopped to smile or say hello to the staff.  How many people do they see each day that do not acknowledge them?

Acknowledge being Blessed

This made me think “how many times a day do I not acknowledge myself?”  Back to saying no… Jes, thank you a million times over for this session.  You have changed my life and the way I look at myself.  I have been too busy in my life to take care of me.  Always busy saying yes to everyone.  Always busy doing everything; thinking I need to be Wonder Woman.  You know what?  Even Wonder Woman took time to go to a party!  Ok, granted she went to the party hunting evil, but still, she went!

I am blessed to be surrounded with amazing, wonderful, loving friends, family, and my SQL community.  Thank you to Melody Zacharias who reminded me this weekend that Confidence is the Fuel for Action and is key in success, and I am stronger and smarter than I know. Thank you to Wendy Pastrick for her session Moving Past Imposter Syndrome where she acknowledged that sometimes there are outside forces that bring us down and it really isn’t just all in our heads.  Thank you to the countless numbers of SQL Presenters who travel to spend their Saturdays sharing with the community they so love.  I am so very blessed to have been welcomed into this amazing community full of people who truly care about what they do, and the people they are with.

Revelations

SQL Saturdays are a great place to network and learn; however, they are so much more.  SQL Saturdays give us a chance to reconnect with what has made us fall in love with the data industry.  They rekindle our passion for the newness and excitement of it all.  They give us a chance to refresh and renew.  They give an opportunity to say NO to the every day and YES to fun, learning, and adventure.  We see old friends and make new ones.  We get to give in-person hugs and hi-fives to our twitter compadres.  We come together for so many reasons, all with one goal.  What an amazing group to be a part of, grow with, and love.

In this community, I know that I am not alone.  SQL Saturdays remind me of this every time.  I am not alone in my concerns, fears, insecurities, or challenges.  I am not alone in my frustrations or pains.  I have friends, I have colleagues, I have a family beyond DNA. SQL Saturdays also remind me that I am worthy, that I have so much to contribute, that I have a voice and a story will help someone else.  I am worthy.  I am enough.

What I Take Away

No matter who you are, no matter where you are in your career, you need community events.  SQL Saturdays are amazing because they are all volunteer events.  What greater gift of love is there than the gift of your time?  Take that with you, my friends.  Your time is sacred and valuable and worthy.  Keep some for yourself, give some away, share your time with someone you don’t know.  Smile at the United Club staff.  Say thank you to your Uber driver.  Don’t be afraid to write a blog or present a session.  Bless someone else with your blessings.

Go forth and be awesome!  And by all means, remember that you can say NO.

 

Great question!

Before I answer this right off, ask yourself these questions:

1. “Do I want to lose access to all other databases on the instance?
2. What would happen if I lose the model my company demands for the specific way every new database must be created?
3. Would anyone notice if I had to restore after a disaster and no one had correct passwords to the database?”

That shiver that just ran up your spine is your answer.  Absolutely YES, the system databases (Master, MSDB, and Model) should have backups!

Master

It is recommended that the Master be backed up as often as necessary to protect the data: a weekly backup with additional backups after substantial updates is highly recommended.  If for some reason the Master becomes unusable, restoring from the backup is the best way to get up and running.  Go here for information on Restoring the Master. If you do not have a valid backup of the Master, rebuilding the Master is the only option.  You can click here to find more information about what it takes to Rebuild System Databases.

Model

Best practices recommend creating FULL backups of the Model Database, and doing so only when necessary for your business needs.  It is a small database that rarely sees changes; however, it is important to make sure it is backed up especially immediately after customizing its database options.

MSDB

Microsoft recommends to perform backups on the MSDB database whenever it is updated.

TempDB

What about TempDB?  Isn’t it a System Database as well?  Despite the importance of backups and recovery, the only database that cannot be backed up or restored is TempDB!  Why can it not be backed up or restored? TempDB is recreated each time the server is restarted, so any temporary objects like tables, indexes, etc, are cleared automatically.  As seen here, backup nor recovery are even an option!

“But, backups take so long to run!”

Feeling like this might be too much trouble?  As with any other backup, these too can be automated by using a SQL Agent job!   There is absolutely no reason NOT to back up your system databases.  If you feel otherwise, might I suggest you keep an updated resume close at hand.

 

*Originally posted at Procure SQL:
https://www.procuresql.com/blog/2018/09/13/backup-system-databases/

 

Corruption.  We know it is everywhere.  It is surely a hot-button issue in the news.  If you haven’t given much thought to database integrity, now is the time to sit up and pay attention.  Corruption can occur at any time.  Most of the time database corruption is caused by a hardware issue.  No matter the reason, being proactive on database integrity will ensure your spot as a hero DBA in the face of corruption.

“A single lie destroys a whole reputation of integrity” – Baltasar Gracian

Integrity is one of those words people throw around quite often these days.  The definition of ‘integrity’ is the quality of being honest and having strong moral principles.  How can data have strong moral principles?  Does ‘data Integrity’ mean something different?  Yes, data integrity refers to the accuracy and consistency of stored data. 

Have Backups, Will Travel

When was the last time an integrity check was run on your database?  If you are sitting there scratching your brain trying to find the answer to that question, you may have serious issues with your database and not know it.

“But, I have solid backup plans in place, so this means I am okay.  Right?”

While having a solid backup and recovery plan in place is an absolute must, you may just have solid backups of corrupt data.  Regular integrity checks will test the allocation and structural integrity of the objects in the database.  This can test a single database, multiple databases (does not determine the consistency of one database to another), and even database indexes.  Integrity checks are very important to the health of your database and can be automated.  It is suggested to run the integrity check as often as your full backups are run. 

As discussed in an earlier blog, Validating SQL Server Backups, your data validation needs to take place BEFORE the backups are taken.  A best practice is to run a DBCC CHECKDB on your data to check for potential corruption.  Running CHECKDB regularly against your production databases will detect corruption quickly.  Thus providing a better chance to recover valid data from a backup, or being able to repair the corruption. CHECKDB will check the logical and physical integrity of the database by running these three primary checks*:

• CHECKALLOC – checks the consistency of the database;
• CHECKTABLE – checks the pages and structures of the table or indexed view; and
• CHECKCATALOG – checks catalog consistency. 

Where to Look

Wondering if you have missing integrity checks or if they have ever been performed on your database?  The following T-SQL script will show when/if integrity checks were performed on your databases.  (Bonus) Running this script regularly will help track down missing integrity checks.

If you are looking for the last date the DBCC checks ran, the T-SQL script to use is as follows:


IF OBJECT_ID('tempdb..#DBCCs') IS NOT NULL
DROP TABLE #DBCCs;
CREATE TABLE #DBCCs
(
ID INT IDENTITY(1, 1)
PRIMARY KEY ,
ParentObject VARCHAR(255) ,
Object VARCHAR(255) ,
Field VARCHAR(255) ,
Value VARCHAR(255) ,
DbName NVARCHAR(128) NULL
)

/*Check for the last good DBCC CHECKDB date */
BEGIN
EXEC sp_MSforeachdb N'USE [?];
INSERT #DBCCs
(ParentObject,
Object,
Field,
Value)
EXEC (''DBCC DBInfo() With TableResults, NO_INFOMSGS'');
UPDATE #DBCCs SET DbName = N''?'' WHERE DbName IS NULL;';

WITH DB2
AS ( SELECT DISTINCT
Field ,
Value ,
DbName
FROM #DBCCs
WHERE Field = 'dbi_dbccLastKnownGood'
)
SELECT @@servername AS Instance ,
DB2.DbName AS DatabaseName ,
CONVERT(DATETIME, DB2.Value, 121) AS DateOfIntegrityCheck
FROM DB2
WHERE DB2.DbName NOT IN ( 'tempdb' )
END


The result will look similar to this.  However, let’s hope your results show a date closer to today’s date than my own!  If you see that your databases do not have integrity checks in place, check your backup and recovery plans and double check your agent jobs to see if perhaps the checks were scheduled but were turned off.

Recommendations

It is recommended that DBCC CHECKDB is run against all production databases on a regular schedule.  The best practice is to have this automated and scheduled as a SQL Agent job to run as a regular part of maintenance.  More specifically, to run the integrity check directly before purging any full backups.  Doing so will ensure that corruption is detected quickly, which will give you a much better chance to recover from your backup or being able to repair the corruption.

Remember, SQL Server is very forgiving and will back up a corrupt database!  Corrupt databases are recoverable, but they might have data pages that are totally worthless!

*Originally posted on 8/6/2018 at https://www.procuresql.com/blog/2018/08/06/does-your-database-have-any-integrity/

As was discussed in a previous blog, (The Most Important Role of a SQL Server DBA) Backups and Recovery are the cornerstone of all successful SQL Server DBA careers.  Exactly how much attention is paid to backups until something drastic happens?  NOT AS MUCH AS SHOULD BE!

What is a SQL Server Backup?

A backup is the process of putting data into backup devices that the system creates and maintains.  A backup device can be a disk, file, a tape, or even the cloud.  Easy enough!

There are four different methods of backup in SQL Server:

• Full Backup – A Full Backup is a copy of the entire database as it exists at the time the backup was completed. It is all-encompassing; every detail of your database (data, stored procedures, tables, functions, views, indexes, and so forth) will be backed up. This is why the Full Backup is the foundation of every restore sequence.  These Full backups can be quite large and put a strain on your storage capacity.  Despite the name, a full backup will only backup the data files; the transaction log must be backed up separately.  To ensure you can recover to the point of failure quickly, you will want to also utilize Differential and Transaction Log Backups (we will cover these next).  Without a Full Backup, your Differential and Transaction Log Backups are useless as you cannot restore a database without a Full Backup!
• Differential Backup – Differential Backups capture only changed data from the last Full Backup. Simple terms: this will backup only the changes since the last Full backup, not backup the entire database. In the event of an outage, the Differential Backups can greatly reduce the time to recover.  Using both Differential Backups and Full Backups will dramatically reduce required storage space, as the Full Backups can be very large and the Differentials are remarkably smaller.
  • How does a Differential Backup know what data has changed?  When data is changed on a page, it sets a flag.  This flag indicates which extent (collection of 8 database pages) the Differential Backup will need to backup.  If your Differential Backups are set to run daily, each day’s data changes are recorded and the flag will not be cleared until a Full Backup has been executed.  Say on Monday you have 3 flags set, and Thursday you have 4 more set, your differential backup on Thursday night will contain data changes represented by all 7 flags, so the flags from the Monday Differential are not cleared by the subsequent Differential Backups.  The flags would only be cleared by a Full Backup.
• Transaction Log Backup – A Transaction Log is a physical file that stores a record of all the transactions that have occurred on a specific database; much like a recorder, maintaining a record of all the modifications.  The Transaction Log Backup will capture the information and then releases the space in the log file so it is able to be used again.  In doing this, the Transaction Log Backup truncates the information in the Transaction Log, but the data is not deleted, it is stored in the backup!  Without Transaction Log Backups, the Transaction Logs will run and grow nonstop, chewing through valuable storage space.
  • Not only does backing up and truncating the Transaction Log manage the filesize, it also decreases the potential for catastrophic data loss.  Having all the Transaction Log Backups since your last Full Backup will allow you to perform point in time restores.  Transaction Log Backups are ideally set up to execute every few minutes to every hour, depending on your company’s threshold for data loss.
• Copy Only Backup – A Copy Only Backup doesn’t change the Differential.  It can be a full backup in that it is a record of the database as it exists when the backup is taken; however, it does not reset flags.  It is ideal for doing a full backup in the middle of the week without disrupting any other backups.  A Copy Only Backup can be used in a Dev space for troubleshooting, or preparing to move to a new environment.

Here is an example of a solid weekly backup plan which uses Full, Differential, and Transaction Log Backups:

Okay, great…backed up, now what?

Okay, so now you know what SQL Server backups are, a description of each backup type, an idea of how and what they back up, and have an idea of a good plan of action to create a solid backup plan.  So, why are backups so important?  Do you know how easy it is to accidentally update or delete data?   It is just one T-SQL statement away with the wrong filter, or no filter at all.  Having good up to date backups and being able to restore them is the difference between looking like a hero and being forced to find a new job.  Backups are your foundation for Recovery.

Please come back, this is the 2nd in a series of blog posts regarding SQL Server Backups and Recovery.  See you next time when we begin to discuss SQL Server Recovery Models.  Thank you for reading!

 

*Originally posted at Procure SQL:
https://www.procuresql.com/blog/2018/02/08/beginners-guide-sql-server-backups/

Hello and welcome!  You have entered the Tidwell zone!  My name is Angela and I love to chitter-chat about a lot of subjects.  It is my goal in life to share, laugh, support, educate, learn, and empower.  I invite you to come along this journey with me.  You might even see a few things from family and friends posted here as well. We are all in this together!