Voice of the DBA

Tag: syndicated

Speaking in 2017
I’m done. No more trips (and no more time) to hit any events this year. As I wrote recently, I’m doing less in 2018, so this review might be quite short next year.

In any case, I thought I’d look back at how speaking went in 2017. I have a speaking CV where I track all my events, so I can get the numbers easily from there. By the numbers, 2017 was:
- 18 total events
- 34 total talks
- 15 unique talks
- 5 SQL Saturdays (not a lot)
- 5 virtual events (3 from Redgate)
- 4 new events for me
- 4 countries in which I delivered talks (1 new)
Overall a long year for me. Those 18 events resulted in a lot of trips and about 80 nights in hotels. I had some work engagements as well, but most trips involved speaking somewhere.

I am saddened that I didn’t present at any user groups this year, which is something that hasn’t happened in awhile. Hopefully I’ll do better next year at getting to a few user groups.

One thing here is that I discounted some of the Redgate DevOps webinars that we do monthly. I probably did 5 or 6 of those, but I’m not really presenting in most of these, mostly hosting, so I left those out.

Oh, and 1 Habitat for Humanity talk to add to this list
December 28, 2017
Restoring a Copy Only Backup–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

There was a question posted recently at SQLServerCentral about whether a copy only backup could be restore with a transaction log backup from a database. I was positive this could, but decided I needed to repro and test for someone as there wasn’t a good BOL reference.

The Tests

Here’s what I did. First, I created a table in a database. I often do this and drop in messages to allow me to track the progress of backups and restores. This post follows my progress.

The Backups

Here’s my basic script:
```
CREATE TABLE logger(msg VARCHAR(200), msgdate DATETIME DEFAULT GETDATE())

INSERT logger (msg) SELECT 'pre full backup'
```
Next, I made a backup and added a message.
```
INSERT logger (msg) SELECT 'pre-log backup 1'
BACKUP LOG nba TO disk = 'nba_1.trn'
INSERT logger (msg) SELECT 'log backup 1 complete'
```
Once this is done, I’m in a state that I expect. A normal full backup, a normal log backup, and some data to help me track where I am.

Now let’s make a copy only backup.
```
INSERT logger (msg) SELECT 'pre copy-only backup '
BACKUP DATABASE nba TO DISK  = 'nba_copy.bak' WITH COPY_ONLY
INSERT logger (msg) SELECT 'copy-only backup complete'
```
This now means I have an open log sequence in the first log backup (post full backup) and a few log records since then. Some of these are inside the copy only backup.

Now let’s add more data and make a new, regular, log backup.
```
INSERT logger (msg) SELECT 'pre-log backup 2'
BACKUP LOG nba TO disk = 'nba_2.trn'
INSERT logger (msg) SELECT 'log backup 2 complete'
```
It’s at this point that I have this sequence:
- Full backup
- Log backup
- Copy-Only Full backup
- Log backup
The Restore

What I want to test is can I restore the Copy-Only backup and a log backup? I think I can, so let’s do that. First, restore from the copy-only backup, using the MOVE option.
```
USE [master]
RESTORE DATABASE [NBA2] 
FROM  DISK = N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup\nba_copy.bak' 
WITH  FILE = 1,  
      MOVE N'NBA' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2.mdf',  
      MOVE N'NBA_log' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_log.ldf',  
      MOVE N'nba_mo_file1' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_mo',  
      MOVE N'nba_mo_file2' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_mo2'
,  NOUNLOAD,  STATS = 5
, NORECOVERY
```
Tip: Always use NORECOVERY

Now let’s try to restore the log.
```
RESTORE LOG NBA2 FROM DISK = 'nba_2.trn' WITH NORECOVERY

RESTORE DATABASE nba2 WITH RECOVERY
```
This works:

That should prove things. Let’s check the logger table.

That’s what we expect. The final message after log backup2 wasn’t captured in our backup files.

Copy Only Backups

What is a copy only backup? If we check the Copy-Only Backups page, we find that this is a regular backup in and of-itself, but it has the restriction that it cannot be used with differential backups. This also doesn’t change the differential bitmap, so that any differentials that are made ignore this backup and go back to include data changed since the last “normal” full backup.

SQLNewBlogger

Understanding backup and recovery is critical for a data professional. I’d say this is the most important skill, and it’s always worth writing about. Spend a few minutes reviewing scenarios and creating some posts like this to show you understand how the system works.
December 27, 2017
AutoMap in SQL Compare

SQL Compare is a core product from Redgate and I’ve got a series on some of the interesting things I’ve found. Download a trial today if you haven’t tried it.

SQL Compare does a great job of trying to help you map columns in your database. For example, I was testing deployments recently to see how a process might work and I noticed the SQL Compare had automapped columns for me.

This is a feature in SQL Compare that tries to make your development effort a little quicker and easier. As you can see in the image below, there’s a note at the top of the comparison, and then an indicator for the actual object.

If I click the object, I see the code, but I don’t know where the auto mapping has taken place.

In this case, I know because I changed the Quantity column name, but there’s nothing special here about the mapping. SQL Compare just mapped “Quantity” to “Qty” by itself. If I check the project properties, I can see the mapping, and change it if necessary.

If you want to know how to customize this, I’ve got an article on the Redgate Hub on How to Customize Schema Comparisons using Auto Map in SQL Compare.

Automap is one of those useful, handy features of SQL Compare. It gets you moving in development quickly by taking care of some work, but you can easily change things and keep your project handy if you have the need.

.

December 14, 2017
T-SQL Tuesday #098–2018 Goals
A great topic for T-SQL Tuesday this time from @sqlmal. It’s Setting Learning Goals for 2018, and it’s a topic I both loathe and love. I hate making commitments that I can’t keep, and I constantly find that a chaotic life will get in the way of learning over time.

As an example, I tried reading the Powershell in a Month of Lunches book. I managed to get to Day 19. It’s not that I don’t like PoSh or have given up (I still blog on the topic). It’s that the setup was more complex later in the book (really need a domain) and I became busy at work.

Goals do have a way of focusing a person on a task, and they’re good in that sense. With a year of less travel coming, this was a timely topic for me and forced me to stop and think a bit about the way I want to drive learning in 2018.

Goals for 2018

What do I want to learn next year? Here’s a short (ish) list, in no order:
- CosmosDB
- Python
- Pester
- Extended Events
- Entity Framework
I could try to tackle all of these, but that certainly won’t help me do well. This is too large a list to become strong at all of them, though I could spend time improving my skills a little bit in all these areas.

Of these items, I could relate many of these to my job, which can make it hard to choose. In addition to these items, I’ve got other things that I know will be a part of my learning next year. Product changes, new integrations between our products and others, plus who knows what will come about.

Picking Two

If I have to say that I’ll spend 100 hours on something next year and try to get better, I’ll say that the two areas I’ll focus on are Extended Events and Python. While I may dabble in the others, I think that these two areas will be more important to my career as well as my job over time.

I need a much better understanding of how XE works apart from trace, and I need a lot more comfort in gathering and analyzing the data.

I also think that Python, with its addition to SQL Server ML and the popularity of the language in new packages and modules means that this is an area that provides some variety, excitement, and a relation to my job. Plus, it’s in SQL Data Generator, so I should have lots more examples there.

My goals for these two are to spend 50 hours on each over the year, in a combination of course work (articles, videos, etc.) and practical work (building things and trying to use the technology to solve problems).

Caveats

There’s always something that comes up, and while I don’t want to start making excuses, I recognize that there are a few things that might derail my plan and make me change.

First, GDPR. I have no idea how this will impact Redgate and my, but I might need to spend a bunch of time early in the year on this. We are getting calls, questions, and requests from clients, and as some of our new integrations and products come out, this might eat up time.

Second, travel. I haven’t planned on much, but I’ve really only set the first 4 months of the year. After that, we’ll see. Who knows what might come up or what demands arise.

Third, life at home. I never know what will happen here and how things might change, so that could change how I focus. Plus, I have a sabbatical coming, so we’ll see how that affects me.

Getting Ready

I’m doing a few things right now. First, I’m setting a monthly reminder to update my goal progress. I’ll try to write a post each month, for better or worse, on where I am.

Second, I’m setting an appointment for myself to sit down and plan some things out the last week of December. That’s a good time to spend an hour or two and try to come up with a plan. It won’t be perfect, but it will give me a direction on which to start. I can amend this during my monthly review if needed.

That’s it, thanks to Mala for a great topic, and we’ll see how it goes.
December 12, 2017