Microsoft Excel 2013 Inside Out – A Review

Posted on by
2

It seems like I’ve been using Excel for as long as I’ve been using computers, and I like to think of myself as a power user. I’m familiar with how to create pivot tables, how to write VBA functions, graphs, formulas, pretty much most things you can do in a spreadsheet. I often turn to Excel in my day to day tasks as a database administrator. It’s very handy to quickly compare how much a database grows over time.

To get myself up to speed with the 2013 version I recently picked up a copy of Microsoft Excel 2013 Inside Out, written by Mark Dodge and Craig Stinson and published by Microsoft Press. And I’m glad I did. This is an excellent book on Excel, whether you’re already familiar with it or brand new to spreadsheets.

This is a thick book, coming in at over 1100 pages. There are 32 chapters organized into 10 sections, from getting to know the Excel environment to formatting and editing worksheets to working with charts and graphs and finally automating Excel with macros and custom VBA code.

My favorite section is Creating Formulas and Performing Data Analysis. It starts out by explaining the basics of functions; how to write them, operator precedence, the difference between relative and absolute cell references, etc. Where it really takes of is where it describes each built in function. They’re separated by purpose for easy reference; mathematical,text, logical, information, and lookup. For example you’ll see the difference between SUM, SUMIF, and SUMIFS. All the statistical and lineal regression functions are explained. The book includes sample Excel files where you can see how the functions work with real data. 

Another favorite is Chapter 24, An Introduction to PowerPivot.Microsoft PowerPivot for Excel 2013 is an add-in that allows users to build their own data models for analysis for self-service BI. This chapter is only meant to be an introduction, but it walks you through how to set up your data models and work with the data, and there’s even a section on how to query with DAX (Data eXpression Language).

Don’t skip the first few chapters, even if you’re an experienced Excel user. You might miss some good tips. For example I wasn’t aware before that you could have a workbook open every time you open Excel by saving it to C:\Users\<your name>\AppData\Roaming\Microsoft\Excel\XLStart. Maybe it was just my imagination but when I did this my workbook seemed to open quicker, before all the Excel add-ins. Another helpful section is the discussion of what was removed in 2013 and, even better, what was added in 2007 and 2010, good information to know if you’re upgrading from older versions.

Also don’t neglect the back of the book. Appendix A maps the old Excel 2003 menus to their new location on the ribbon, a great help if you haven’t worked with the ribbon before. And Appendix B is a list of Excel keyboard shortcuts. There are definitely some time savers here.

I recommend Microsoft Excel 2013 Inside Out for any Excel user, either power user or newcomer. I know I’ll be referencing this book often.

Learning to cluster – initial observations

Posted on by
2

I wrote a quick post a week ago, about finally setting up a failover cluster in my Hyper-V lab. I want to learn about clustering, especially as it relates to SQL, as that is a big hole in my DBA education. These are just some of my initial observations, and they mostly relate to how I set up the cluster. They probably won’t matter to anyone but me. Remember, I’m just learning to cluster. 

Before I set up my cluster I had already created the 2 servers that would be my primary and secondary nodes. I also set up three virtual disks on my iSCSI Target server; a 25 GB disk labeled “SQL Data”that will be used for my data files, a 10 GB disk labeled “Log” for the log files, and a 1 GB disk labeled “Quorum” that I would use for the quorum disk. When I built the cluster I added both nodes at the same time. The clustering wizard automatically set up the proper disk for the quorum and set it to Node and Disk Majority. So far I haven’t made any changes to the cluster defaults.

I installed SQL on both nodes with no problems. I gave the SQL cluster a static IP. SO far I’ve just played with failing over by shutting down the primary virtual server. What surprised me was that after failing over the secondary node took the IP address I gave to the SQL cluster. I was expecting it to get the IP address I gave to the cluster itself.

I just bought Allan Hirt’s book Pro SQL Server 2008 Clustering. I’ll write more about that as I go along. There’s tons of whitepapers and blogs to help me out.

Opening command prompt in Windows Server 2012 Core

Posted on by
1

Just a quick post to reference later, since I always seem to forget the easiest tasks.

I’ve installed Windows Server 2012 Core before and one thing I almost always do is to close the command prompt window. Then I have to scramble to remember how to get it back. The Windows key on my keyboard doesn’t help. It’s easy to do, though.

  1. Use Ctrl – Alt – Delete and choose Task Manager from the list of options.
  2. Click File > Run New Task from the menu bar
  3. Type cmd in therun box and click OK.

And that’s all there is to it. Maybe now I can remember.

My SQL Lab now has clustering

Posted on by
3

“Hello, and welcome to our DBAs Anonymous meeting. Today we have a new visitor. Would you like to introduce yourself”

“Hello, my name is John. I’m a DBA and I don’t know how to cluster servers.”

“GASP!!! Get out!!!”

OK, so maybe it’s not quite like that. But sometimes that’s how I picture it in my mind. Until a few weeks ago I have never installed Microsoft SQL on a failover cluster. In my defense I’ve never worked in an environment that used clustering. I always felt that I was missing a big part of my SQL education. So I finally decided to do something about it.

I’ve been running Hyper-V on my laptop for a while now and I’ve already set up my virtual network. I installed and configured a domain controller running Windows 2008 R2 and two other servers that run SQL 2008 R2 and SharePoint 2010, also a few workstations that I use mostly for demos and small projects. Until recently I didn’t know how to create virtual disks that I could use for a test cluster. I did some research and did know that I could download the iSQSL Software Target, which I did on another server attached to my virtual network.

Recently I found two blogs that showed exactly how to configure the disks and set up the cluster. Prashant Kumar shows how to do this in Windows 2008 R2 on his blog SQLActions. And Ayman El-Ghazali does the same with Windows 2012 Core on his blog The SQL Pro. Between these two resources I was able to configure my lab with a cluster. I’ve built and destroyed a few dozen over the last two weeks, along with different SQL installations. So far I’m having a blast! I know, I know, wait until it’s production. But still…

I’ve just got a few additional tips to anyone else who stumbles across this post and wants to try the same.

First, if you use the differencing disk feature of Hyper-V, make sure you install all OS features, upgrades and patches to your base disk before you create your new virtual machines. This will save you hours, depending on how up to date you want your servers to be. In my case I installed PowerShell because I know I’ll be using it everywhere. On my node servers I put in other roles and features, but those won’t necessarily go on all my future servers.

Second, take advantage of snapshots in Hyper-V, again it will save you time when you want to redo an action or just start from scratch. In my case I took snapshots when I created my virtual machines, when I attached the storage, when I created the cluster, and when I first installed SQL. Now, for example, when I want to reinstall SQL with different options, I can easily get back to a fresh environment where I know there won’t be any conflicts from past installs.

I can show my method step by step if anyone is interested, but there’s enough information in the two blogs I mentioned earlier to get you started. Give me a few more weeks and I hope to be able to discuss SQL clustering here as well.

Microsoft SQL Server 2012 Step by Step – A Review

Posted on by
1

The latest tech book I’ve been reading is Microsoft SQL Server 2012 Step by Step, written by Patrick LeBlanc and published by Microsoft Press. This is an excellent book for anyone new to SQL. If your familiar with older version of SQL you may still find information about some of the new features, but you should remember that it’s written with beginners in mind.

The book is split into nine sections that cover everything from installing and configuring SQL 2012 to high availability and disaster recovery. In between you’ll read about the basics of database design and T-SQL. There’s a very good chapter covering backup and recover of databases. 

Like most other Step by Step books I’ve read this book teaches by example. First there’s a discussion about the  topic of each chapter, followed by an example that walks you through the concepts being taught. What I especially like is that you will see how to perform tasks like creating a table through the Management Studio but you’ll also learn how to perform the same tasks using T-SQL.

This is a great book for anyone who wants to get up to speed with SQL 2012 quickly.

SSDT – Arranging windows to customize your workflow

Posted on by
1

One of my favorite features in the new SQL Server Data Tools is the query window. Specifically, what I like is the ability to rearrange the panes to better see query results and different sections of a long query or procedure. If you use Visual Studio you’re probably already familiar with rearranging code windows.But if you’re just used to using SSMS for queries this might be new to you.

If you just run a query the way you do in SSMS you can see results the same way. This screenshot is using SSDT for Visual Studio 2010 but you can do the same things in Visual Studio 2012.  

 image

But because you’re in Visual Studio you can rearrange the windows. You can show the results on the side, nice when you return a lot of records. Just click the little switch button to show vertically or horizontally.

ssdt1

But even better is the ability to split a large block of code into two sections. Ever find yourself jumping around a long procedure, repeatedly going from the middle back to the top to see where variables get their values? Well, in SSDT you can split the code window and scroll the different sections. Drag the splitter to resize them to whatever size you’re comfortable with.

ssdt2

image

Finally, like in SSMS you can separate the different tabs to display side by side You can also move a tab to a second monitor.

SQL Data Tools for Business Intelligence released

Posted on by
1

Last week saw the long awaited release of SQL Server Data Tools for Business Intelligence. This version of SSDT  has the templates for creating Intelligence Services packages, Reporting Services reports, and Analysis Services projects both cubes and tabular, inside of Visual Studio 2012. That’s great news as a lot of developers, myself included, have been waiting for this functionality.

However the bad news is that it doesn’t include the Database Projects templates released last year. You’ll still need to install them separately. But they will work together.

So if you want just the BI templates for Visual Studio 2012 you only have to install the BI version of SSDT. If you also want the database projects you will need to install both the BI templates and the database templates. And if you want to use the test plans for your new database projects and create SSRS reports or SSIS packages you’ll need a full edition of VS 2012, either Premium or Ultimate, plus the database templates plus the BI templates.

You can get the database project templates for either VS2010 or VS2012 from the SSDT Team blog. The BI templates for VS2012 can be found as a Microsoft download. And you can read more about the added functionality on the SSRS Team blog, the SSIS Team blog, or the SSAS Team blog.

How do you perform SQL security audits?

Posted on by
2

Do you need to track permissions on the SQL server databases that you are responsible for? Are you sure you know everyone who has sysadmin rights, or who can delete records from the production servers? How often should you perform a security audit?

In my case I don’t have to check permissions on a regular basis. But I still want to keep track of who can do what. So I run the following scripts to check both server and database level permissions as well as check the default trace for any new permissions or users I may not be aware of. They also help me easily compare permissions across servers.

I run these scripts on a mix of SQL 2005 and SQL 2008 servers. I haven’t had a chance to run on SQL 2008 R2 or SQL 2012 yet so I’m not guaranteeing that they’ll work there. Also they may return a lot of records, depending on the number of users in your databases. But they can be modified to filter by user. As always, though, please understand the scripts before running them on a production server.

One final note. I used a script I found on the internet a while back as a basis for the ones I’m using now. Unfortunately I don’t remember where I found them so I can’t properly give credit where it’s due.

This script is run against the server and has 2 parts. First it returns a list of server roles and who belongs to which role. The second part returns individual permissions for each account.

-- Server role members
SELECT
sp1.[name]
, sp1.type_desc
, sp2.[name]
, sp2.type_desc
, sp2.create_date
, sp2.modify_date
FROM sys.server_role_members AS srm
INNER JOIN sys.server_principals AS sp1
ON srm.role_principal_id = sp1.principal_id
INNER JOIN sys.server_principals AS sp2
ON srm.member_principal_id = sp2.principal_id
ORDER BY sp1.[name], sp2.[name]

-- Server users
SELECT
sp1.[name]
, sp1.type_desc
, sp1.create_date
, sp1.modify_date
, sp.state_desc AS 'Permission'
, sp.permission_name AS 'Action'
, sp.class_desc
, sc.class_desc
, sp2.[name]
FROM sys.server_permissions AS sp
INNER JOIN sys.server_principals AS sp1
ON sp.grantee_principal_id = sp1.principal_id
INNER JOIN sys.server_principals AS sp2
ON sp.grantor_principal_id = sp2.principal_id
LEFT JOIN sys.securable_classes AS sc
ON sp.major_id = sc.class
ORDER BY sp1.[name], sp1.type_desc

My next script does the same thing on a database level. Just change the name of the database to see who belongs to which database role or other individual rights.

USE <database_name, sysname, master>;

-- Database role members
SELECT
pr1.[name]
, pr1.type_desc
, pr2.[name]
, pr2.type_desc
, pr2.create_date
, pr2.modify_date
FROM sys.database_role_members AS rm
INNER JOIN sys.database_principals AS pr1
ON rm.role_principal_id = pr1.principal_id
INNER JOIN sys.database_principals AS pr2
ON rm.member_principal_id = pr2.principal_id
ORDER BY pr1.[name], pr2.[name]

-- Database users
SELECT
pr.[name]
, pr.type_desc
, pr.create_date
, pr.modify_date
, dp.state_desc AS 'Permission'
, dp.permission_name AS 'Action'
, CASE dp.class
WHEN 0 THEN 'Database::' + DB_NAME()
WHEN 1 THEN ISNULL(s.[name] + '.', '') + OBJECT_NAME(dp.major_id)
WHEN 3 THEN 'Schema::' + SCHEMA_NAME(dp.major_id)
END AS 'Securable'
, pr2.[name]
FROM sys.database_permissions AS dp
INNER JOIN sys.database_principals AS pr
ON dp.grantee_principal_id = pr.principal_id
INNER JOIN sys.database_principals AS pr2
ON dp.grantor_principal_id = pr2.principal_id
LEFT JOIN sys.objects AS o
ON dp.major_id = o.[object_id]
LEFT JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE dp.class IN (0, 1, 3, 4)
AND dp.minor_id = 0
ORDER BY pr.[name], pr.type_desc

Finally there’s a way to monitor who is adding users, or changing permissions. Those events are captured by the default trace that runs when the SQL service starts up. My script only checks the latest trace file, but it can be modified to look at older .trc files. If you’ve disabled the default trace then this won’t work.

USE master;

SET NOCOUNT ON;

DECLARE @enable int;
 SELECT TOP 1 @enable = CONVERT(int,value_in_use)
 FROM sys.configurations
 WHERE name = 'default trace enabled';

IF @enable = 1
 BEGIN
 DECLARE @d1 datetime;
 DECLARE @diff int;
 DECLARE @curr_tracefilename varchar(500);
 DECLARE @base_tracefilename varchar(500);
 DECLARE @indx int ;

SELECT @curr_tracefilename = path
 FROM sys.traces
 WHERE is_default = 1;

SET @curr_tracefilename = reverse(@curr_tracefilename);
 SELECT @indx = PATINDEX('%\%', @curr_tracefilename);
 SET @curr_tracefilename = reverse(@curr_tracefilename);
 SET @base_tracefilename = LEFT(@curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc';

SELECT
 t.LoginName
 , t.HostName
 , t.ApplicationName
 , t.StartTime
 , t.DatabaseName
 , t.TargetUserName
 , t.RoleName
 , e.[name] AS EventDesc
 FROM ::fn_trace_gettable(@base_tracefilename, default) AS t
 INNER JOIN sys.trace_events AS e ON t.EventClass = e.trace_event_id
 WHERE t.EventClass BETWEEN 104 AND 114
 END

I’d love to hear how you handle security on your servers. I’m always looking for ways to improve my processes.

MS Press TK for 70-463 – A Review

Posted on by
Reply

The last Microsoft certification exam I took was 70-448: SQL Server 2008 Business Intelligence. Because that’s the freshest in my mind, and because I’m focusing more on the BI side of SQL lately, I’ve decided that my next exam would be the new 70-463 Exam. This post is my review of the Microsoft Press Training Kit for 70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012, written by Grega Jerkic, Matija Lah, and Dejan Sarka.

You’ll notice a difference between 70-463 and the older Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft® SQL Server® 201270-448 exam. When the new exams came out for SQL 2012 Business Intelligence got its own track. There’s now a MCSE just for BI that requires the core tests (70-461, 70-462, and 70-463) plus 70-466 that covers Analysis and Reporting Services, and 70-467 on designing a Business Intelligence solution. 70-463 covers designing a data warehouse and using SSIS for ETL. You can read up on the exam requirements for 70-463 here. Information on all the new SQL 2012 Business Intelligence exams can be found here. 

If you’ve ever used a Microsoft Press training kit to study for an exam you’ll be familiar with the layout. There are five sections that match the exam objectives; Design and Implement a Data Warehouse, Extract and Transform Data, Load Data, Configure and Deploy SSIS Solutions, and Build Data Quality Solutions. Each section of the book contains chapters covering topics pertaining to the objective. The chapters are comprised of lessons that have walkthroughs and a short quiz. Each chapter also has case studies that are a little bit more involved. When you buy the physical book or an electronic copy from O’Reilly you’ll also get a CD (or the files) that contain the code samples used in the book as well as a practice test engine from MeasureUp.

This book, like the exam, covers mostly loading a data warehouse using Integration Services. Most of the concepts are the same as they were in earlier versions of SQL; control flows, data flows, and the different transforms available to each, error handling, using variables, logging, transactions and checkpoints are all covered. The new features, like using the new SSISDB catalog, are also well covered

What I like about this training kit is the 25% or so of the exam that’s not directly related to SSIS. The first section covers the design of a data warehouse. It’s a good introduction to the concepts of dimension and fact tables, of star and snowflake schemas. You’ll see how data compression and columnstore indexes are helpful. You’ll also learn about creating SSIS package templates, something I didn’t learn about as part of the 70-448 test.

So far I find the training kit to be well written and easy to follow. I like the examples presented in the case scenarios, they make me think a little about the question. They’re closer to the kind of questions you may find on the exam. (NOTE: I haven’t taken the test yet so I have no knowledge of the actual questions or exam format. I’m basing my opinion solely on past Microsoft exams I’ve taken). I haven’t taken any of the included practice exams yet as I want to wait until I’m ready to take the exam. My past experience is that if I take the practice exams too early I tend to remember the question and not the concepts.

So if you plan on taking Microsoft’s 70-463 exam I recommend you use the MS Press Training Kit as part of your studies. If you have no other experience with SSIS you may want other resources. I feel that with my past experience I can learn enough about the 2012 version from this book.

Strata RX Conference 2012 Video – A Review

Posted on by
Reply

This post is my review of the video set from the Strata RX Conference, held in San Francisco on October 16th – 17th 2012.

I’m a SQL DBA for a company in the healthcare field, and as such I try to keep current with trends that affect patient medical records. Leverage the Power of Big Data in HealthcareI’m not as familiar with “big data” as I’d like to be. So when I saw the Strata RX Conference 2012 I thought it was one that would be beneficial to me.Unfortunately I wasn’t able to attend. But I found that O’Reilly has made the complete conference video set available on the O’Reilly Media site.

This full set contains over 29 hours of content from the 2 day conference, including the keynotes and panel discussions. There’s over 50 files you can either watch online or download the .mp4 to watch when you’re not connected to the internet. Most of the videos are short, they probably average about 30 minutes each. And like every video I’ve ever seen from O’Reilly, the audio and video quality are superb. Watching these videos is like having a front row seat at each session. Sometimes questions from the audience can be hard to make out. But that happens if you see the session live as well. It’s up to the presenter to repeat the question so everyone can hear it.

While the set, subtitled Leverage the Power of Big Data in Healthcare, does talk about healthcare and big data I found the most interesting sessions to be on the future of handling medical records. In particular John Kansky from the Indiana Health Information Exchange had a very interesting session on where health information exchange is heading. He talks about some of the issues setting up ACOs. Another favorite video is a panel discussion with moderated by DJ Patil and Tim O’Reilly, Benjamin West, John Mattison talking about where healthcare will be in 2020. Believe it or not, there were electronic health records as far back as the 1970’s. Tim O’Reilly had the best line of the conference when he stated that you won’t be calling your doctor, but rather your doctor will be calling you.

I encourage anyone who works with medical data to watch these videos. The price is steep, at $299.99. But your company would probably be willing to pick up the cost as there is a lot of information that’s beneficial to all healthcare companies, whether they deal with patient care or insurance. There’s a lot of the changes of healthcare data here, through HITECH, HIPAA  or just to see where we’re going. These videos will help you and your company do just that. Just tell your manager that $299.99 is a lot cheaper than sending you to San Francisco for a few days!