Just a quick 101 style video I put together to show the pieces of a SQL Server database for those that are starting to learn the product. Also a teaser for my teaching style of my DBA Fundamentals classes!
Enjoy!
SQL Server Database Consulting
Just a quick 101 style video I put together to show the pieces of a SQL Server database for those that are starting to learn the product. Also a teaser for my teaching style of my DBA Fundamentals classes!
Enjoy!
I will be teaching an all day (9-5) class on SQL Server Database Administration fundamentals at the Microsoft building in Irving/Las Colinas on September 15, 2017.
Update: This is now a free class. There will be a donation bucket, with 100% going to the Red Cross.
The target audience for this is System admins, Developers, Tier 1 support staff, IT managers…anyone that has to deal with SQL Server on a regular basis and finds themselves Googling everything.
Register (REQUIRED) on EventBrite to learn the following topics (subject to change due to time, class flow):
• What makes up a SQL Server
• How a database is structured
• How to install and what to look out for
• Finding what you need in Management Studio
• SQL Security Basics
• How to create and manage backups
• How to create a database, 3 different ways
• Basic performance troubleshooting
• Picking a Disaster Recovery option for your environment
Please see the FAQ section of the registration page, and feel free to contact me for anything not covered there.
Thanks!
Now that the official schedule has been published, I am pleased to announce that I will be doing a pre-con and a regular session at SQL Saturday Minnesota on October 6-7. 2017.
The Friday pre-conference will be “DBA Fundamentals for the Junior, Accidental and non-DBAs.” This will be a wide ranging day-long discussion on the underlying fundamental pieces of SQL Server Administration that are beneficial to both new DBAs, and those that have to work with or fill in for them. Please see the link for details, and feel free to contact me with any questions. There is a $110 (plus service fees) charge for this, which is split between me and the local SQL User group organizing the event, after expenses.
The Saturday regular session will be “Backups for new/non-DBAs…the Why, not the How”, where we will cover Disaster recovery scenarios and the part SQL Server plays in the recovery. We won’t spend much time on specifically defining backup types, etc. Currently this is scheduled for the 1-2pm time slot, but that may change.
Edit: my Saturday session is now 10:15am – 11:15am 🙂
I’m very excited to be coming to the MSP area! I even have a friend that lives in the area that I get to see for the first time in 3 years…plus all of the new friends I will make at the events!
Thanks,
Kevin3NF
Quick post today…I needed to find a quick way to get the size of a specific index without using T-SQL.
Tables are easy…right-click>>Properties>>Storage. Indexes don’t have a storage option. You can get the sum of all indexes on a table from the “Disk Usage by Top Tables” built-in report, but not one by one.
To do it in the SSMS GUI, expand the Database>>Tables>>Indexes:
Right-Click the index in question, select “Properties”, then “Fragmentation.”:
As we know, pages are 8KB, so now its just a simple math problem:
46236 pages x 8 KB /1024 to get to MB = 361 MB (rounded for conversation’s sake):
If you need more than one, but not all…you’re going to need to Google up some T-SQL to run. I found a variety of ways to do it, and didn’t like any of them.
Thanks for reading!
Follow Dallas DBAs on LinkedIn
If you haven’t been asked to Delete a database in your DBA career…its just a matter of time:
“We don’t need database XYZ anymore…delete it.” — Some well meaning manager
Stop right now, before blindly following an order like that.
First…there are some questions to ask:
As a DBA, any time anyone asks you to delete anything, from data to an entire database, you need to question it. Not doing so can be a CLM/RGE combo! (Career Limiting Move/Resumé Generating Event)
The request may be perfectly valid, but in my experience most people that ask for a database to be deleted use the term to mean “I don’t want to see that database in SSMS anymore” or “I don’t want it on Server A, put it on Server B”, with very little regard for impact to other teams or applications.
There are three main ways to take a database “out of service”:
Each has its own considerations and each is the proper choice in different circumstances. I am going to walk you through each one, from least risky to most, in that order.
Takes the database offline, but still visible in SSMS. Cannot be accessed by applications, backups, etc. Can be brought back online relatively quickly.
My list of databases before starting this post…we’ll be messing with the Rename2222 database (nothing in it…came from a rename script I was writing/testing):
Right-click the database in question, Tasks, Offline:
Dialog Box you get after clicking “Take Offline”:
Results in SSMS after Offline Operation:
Upside to Offline method: Database is still in place, visible in SSMS, Record in master database is still there, relatively simple.
Downside to Offline: May cause SQL Monitoring tools to throw alerts and create tickets, files are not in use by SQL Server, can be deleted.
Removes the database from SSMS, deletes its meta information in the master database, but leaves the physical files intact on the drive.
The detach menu:
The detach dialog:
After Detaching:
Note that the MDF/LDF files are still right where they were. SQL Server no longer has a handle on them:
You can re-attach fairly quickly should the need arise (i.e. user comes screaming at you 5 minutes after the detach):
Upside to Detach method: Files are still in place, re-attach is fairly straightforward, Monitoring tools will generally not error. Can save your job by not having deleted a database someone else needed.
Downside to Detach: Files not manually deleted take up disk space…set reminders to delete after some time period.
Drops the database, its meta data in Master, AND its associated physical files…you cannot reverse a Delete (DROP Database).
This is one of those “Stop right here” moments. Before you Delete a database, make very sure you are following protocols. Make sure you have a recent FULL backup:
If not, run one. The simplest possible command to backup a database:
-- backs up to default location Backup Database Rename2222 To Disk = 'Rename2222.bak'
Better:
-- backs up to specified location -- with date included in the file name: Backup Database Rename2222 To Disk = 'c:\MyBackups\Rename2222_20170531.bak'
Once you have a backup, you also should get a ticket, change control, or at the very minimum an email from the requester with approval for the delete. Depends on your process. Especially important if this is a production environment. This can save your job. If you Delete a database that a system depends on and cause an outage…whoever told you to do may claim they were not involved.
To delete is way too simple:
Remember…once you Delete a database…its gone. The ONLY ‘undo’ is to restore from Backup. You can call Microsoft, but they will tell you the same thing. Worst case, you can spend tens of thousands of dollars on a data recovery service, assuming you can take the server down and nothing overwrote the space the the files were in on the drives.
Almost everywhere I’ve worked, Detach with a 2-4 week follow up to delete the files was the best choice. But beware that some systems may only be useful for Quarterly or Annual reporting. In an ideal situation, you have a complete inventory of every database on every server from Production down to Dev and know exactly why they are there, as well as who the stakeholders are for each one. If you don’t have this for the O/S, SQL and DB level info, start now.
Thanks for reading!
Day 1 of the free DBA fundamentals was a huge success!! Thanks to all that came out.
Day 2 (June 7) registration goes live at noon on Saturday, May 27 (still free):
This is an in-person class…not online and not recorded. We are not that fancy yet…
We will be covering the following:
You do not need to bring a laptop, but if you have one with SQL Server and SSMS installed to follow along, please feel free to bring it.
You do need to be registered to attend…we completely filled our space last Wednesday, with no room for walk-ins. Please do not just show up and make me have you stand in the corner 🙁
There will be a waitlist when the tickets are gone.
If you came to Day 1 and sent me a request, you are already registered for Day 2.
Thanks, and see you there on June 7!
Kevin3NF (follow me on Twitter…its your homework!)