Neal Waterstreet

Cutting the cable cord…

by Leave a Comment

I finally decided to cancel my cable service and go with streaming services. We looked at what we really watch and found that it can easily be replaced. Football is going to be the only problem and I am still looking for a solution for that.

Now that I the service was going away, I needed to figure out what to replace it with. For hardware, I looked into a few options for a DIY HTPC. There are a lot of options there and the cost can be very low. The downside to me was that the DVR features don’t appear all that great. I got used to being able to record on a channel while watching another with my old service. It looks like this requires multiple cards which could add to the cost. I also thought about getting a Tivo, but I didn’t want the monthly contract since that was one of the reasons I did this in the first place.

Instead of a HTPC, I purchased a Roku and OTA antenna. Roku adds new channels regularly and is a much simpler alternative to a HTPC. The model I bought has both USB and Plex support. The OTA antenna was very inexpensive and was easily installed in the attic. I just used the cable wire to connect it to the TV. The channels look much better than I was used to. The antenna receives about 25 channels, but that is OK since we will only watch 5 or 6. If not having a DVR becomes a problem I may look into it, there are some other services out there. Another option is to convert an old PC to a HTPC/DVR, but I think I have all I need at this point.

I made the call yesterday and had the service disconnected. I increased my internet speed to the next level up to help with streaming. It was easier to cancel than I expected and just need to drop the equipment in the mail. I’ve already received an email offering the same service for a drastically reduced rate, but it is too late.

DAX vs MDX Smackdown!

by 2 Comments

Last Monday Damu Venkatesan and I presented DAX vs MDX Smackdown! to the Atlanta Microsoft BI group. I thought it went well, but could definitely use some tweaking. In fact, it may be worth it to overhaul the presentation and focus on DAX. Here are a few of my thoughts about the presentation:

  1. More needs to be added to establish the use of DAX. I didn’t do a good job with this and assumed everyone was familiar with the Tabular model.
  2. I showed all of the queries in SSMS, which confused some people. The resolution here is to build a PowerPivot report and show the result from there. Then I can take it into SSMS and show the end result in PowerPivot and finally Power View.
  3. I need to spend more time telling the story of each query. Some of this was due to technical issues, but I still need to improve here. Damu did a very good job with stories about the MDX queries.
  4. Make sure the entire environment is installed on my VM. I had multiple SSAS instances, but didn’t have Excel or SharePoint installed. This would have helped me to elaborate on several questions.

On the technical side there were issues too. My Lenovo had issues with the projector and I could only see on screen and not the laptop. This was also at a very low resolution, so all of my work setting up SSMS for readability was lost. I need to find a backup plan here and find out what other people are doing.

I’d also like to thank Teo Lachev for his help before, during and after the presentation.

All in all it went well. It is always easier to pick out what went wrong instead of what went well. I am looking forward to doing it again soon.

WITH MARK is your friend

by Leave a Comment

Using the WITH MARK clause in a transaction can save time and tears. Instead of restoring backups to a point in time, WITH MARK can be used to name transactions and can then be used to roll forward to the transaction itself.

Make sure the AdventureWorks database recovery model is set to FULL
[code language=”sql”]
USE master
GO
ALTER DATABASE AdventureWorks SET RECOVERY FULL WITH NO_WAIT
GO
[/code]
We need to take a FULL backup first, just in case it was not using the FULL recovery model.
[code language=”sql”]
BACKUP DATABASE AdventureWorks TO DISK = N’D:\Backups\AdventureWorks_now.bak’
GO
[/code]

sshot-1

We can run some update statements against the Person.Contact table. We can start off by changing the EmailPromotion to 0 for ContactID 2.
[code language=”sql”]
USE AdventureWorks
GO
BEGIN TRANSACTION EmailPromotion WITH MARK
UPDATE AdventureWorks.Person.Contact SET EmailPromotion = 0 WHERE ContactID = 2
COMMIT TRANSACTION
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]
sshot-2

 

Update all of the phone numbers to the same number by leaving out a WHERE clause. This is a favorite mistake of mine.
[code language=”sql”]
BEGIN TRANSACTION UpdatePhone WITH MARK
UPDATE AdventureWorks.Person.Contact SET Phone = ‘123-456-7890’
–WHERE ContactID = 4
COMMIT TRANSACTION
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]
OOPS.

sshot-3

Finally change the last name from ‘Ackerman’ to ‘Smith’.
[code language=”sql”]
BEGIN TRANSACTION LastName WITH MARK
UPDATE AdventureWorks.Person.Contact SET LastName = ‘Smith’ WHERE ContactID = 5
COMMIT TRANSACTION
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]

sshot-4

Go ahead and back up the transaction log.
[code language=”sql”]
USE master
GO
BACKUP LOG AdventureWorks TO DISK = N’D:\Backups\AdventureWorks_now.trn’
GO
[/code]
First, we’ll use STOPBEFOREMARK to restore to the point prior to the EmailPromotion transaction.
[code language=”sql”]
USE master
GO
RESTORE DATABASE AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.bak’
WITH NORECOVERY, REPLACE
GO
RESTORE LOG [AdventureWorks] FROM DISK = N’D:\Backups\AdventureWorks_now.trn’
WITH STOPBEFOREMARK= ‘EmailPromotion’
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]

sshot-5

Now we can use STOPBEFOREMARK to restore to the point prior to the UPDATEPHONE transaction.
[code language=”sql”]
USE master
GO
RESTORE DATABASE AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.bak’
WITH NORECOVERY, REPLACE
GO
RESTORE LOG AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.trn’
WITH STOPBEFOREMARK= ‘UpdatePhone’
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]

sshot-6

Now we can use STOPBEFOREMARK to restore to the point prior to the LASTNAME transaction.
[code language=”sql”]
USE master
GO
RESTORE DATABASE AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.bak’
WITH NORECOVERY, REPLACE
GO
RESTORE LOG AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.trn’
WITH STOPBEFOREMARK= ‘LastName’
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]

sshot-7

STOPATMARK is similar to STOPBEFOREMARK, but it includes the marked transaction instead to stopping prior to it. Here we will restore back to LastName with STOPATMARK.
[code language=”sql”]
USE master
GO
RESTORE DATABASE AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.bak’
WITH NORECOVERY, REPLACE
GO
RESTORE LOG AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.trn’
WITH STOPATMARK= ‘LastName’
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]

sshot-4

But wait, there is more! You can also use WITH MARK to restore to a LSN instead of a name.
[code language=”sql”]
SELECT TOP 3 database_name, mark_name, description, lsn, mark_time
FROM msdb.dbo.logmarkhistory
ORDER BY mark_time DESC
GO
[/code]

sshot-8
[code language=”sql”]
USE master
GO
RESTORE DATABASE AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.bak’
WITH NORECOVERY, REPLACE GO RESTORE LOG AdventureWorks
FROM DISK = N’D:\Backups\AdventureWorks_now.trn’ WITH STOPBEFOREMARK= ‘lsn:45000000323400003’
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]

sshot-7