All posts by steve

Steve Verschaeve is a Database Architect at PwC Belgium and former MCT working with SQL Server since 2003. He holds an MCP, MCTS and MCITP certificate . If he’s not working you will find him messing around with Arduino. He is also a member of the Microsoft Extended Experts Team (MEET) for SQL Server and the Belgian SQL Server User Group (SQLUG) co-organizing SQL Server Days since 2009.

Partially Contained Databases

SQL Server 2012, still as RC0 at the time of this writing, comes with a new feature called Partially Contained Databases also known as CDB (contained database).

First of all, what does it mean and what is it good for and secondly, why partially?

Let’s have a look at a none contained database to see the disadvantages opposed to a contained database. As you know, moving a database to another instance comes with a few issues such as orphaned users and SQL Server agent jobs which you need to recreate. Wouldn’t it be great if you just take the db, backup and restore or detach and attach or even script it to the new instance and go? Well, that’s the idea of self contained databases.

With contained databases, which you explicit ally need to create, it is no longer necessary to authenticate to the instance using a login in the master database. The idea is to create a database user either choosing a SQL authenticated user or a Windows principal to authenticate directly to the contained database.

Now, why partially? You should know that not everything is contained in SQL Server 2012. The fully contained databases should be for a next release of SQL Server. Worth mentioning that SQL Azure already supports fully contained databases!

To find out what is contained, look at http://technet.microsoft.com/en-us/library/ff929188(SQL.110).aspx

To find out what is not contained, look at http://technet.microsoft.com/en-us/library/ff929118(SQL.110).aspx

For more information on Partially Contained Databases, check out http://technet.microsoft.com/en-us/library/ff929071(SQL.110).aspx

Partially Contained Database – Part 1: Setup

This is the first post in a series of partially contained databases. In part 1 we will discuss the setup of this feature including the authentication across the instance.

1.A. Enable contained databases using SSMS

  1. Server properties
  2. Advanced
  3. Containment: set Enable Contained Databases to True

 

1.B. Enable contained databases using T-SQL

EXEC sys.sp_configure N’contained database authentication’, N’1′
GO
RECONFIGURE WITH OVERRIDE
GO

2. Restart the SQL Server instance

Copy query from an Excel worksheet

I recently ran a query, copy and pasted the results in an Excel Workbook. The data in the first worksheet and the query in the next Worksheet for reuse in case I need it again.

So today I had to run that query again…copied the query from the worksheet and got an error:

Msg 102, Level 15, State 1, Line 1Incorrect syntax near ‘ ‘.

I double clicked the error and the following line got highlighted:

DECLARE @country_id CHAR(2)

Apparently there is an error at the end of char(2). I removed that line so the following line became the first line of the code:

SELECT @country_id = ‘BE’

Tried to parse once more (I expected another error because the declaration was missing), the same error occurred.  I knew it had something to do with the copy from Excel. After all, Excel is not the right type of application to store queries.

I tried to paste the query in Notepad so all formatting would be gone and it still wouldn’t work.
Paste and copy once more from within Gmail didn’t work either until I actually send an e-mail to myself with the query in the body of the mail. I copied the query to SSMS and it finally worked!!

 

TechDays 2012: My top 5 sessions to attend

TechDays 2012 is coming closer  (14-16 February) and I look forward to attend as a MEET member. This is a list of the top 5 sessions I will attend:

  1. How to achieve a more agile and dynamic IT environment by Ward Ralston
    This is the IT Pro opening session. The other option is for Devs about Windows Azure by Scott Guthrie
    I’m an IT Pro so easy choice.
  2. 12 reasons to love SQL Server 2012 by Dandy Weyn
    The only SQL Server session (but then again, we had SQL Server Days about 2 months ago).
    Easy here as well as I’m a SQL Server guy and I like Dandy Weyn very much.
  3. 10 Deadly Sins of Administrators about Windows Security by Paula Januszkiewicz
    Well, fellow DBAs will know that understanding Windows Server is a must so I’m curious on this one.
  4. The Future of C# and Visual Basic by Bart De Smet
    Ok, I’m not a developer and probably won’t understand halve of it but you got to see Bart De Smet presenting! He really is a genius and probably the smartest guy I have ever met. He would fit right into the Big Bang Theory!
  5. ScottGu Unplugged by Scott Guthrie
    Well, you haven’t been at TechDays if you haven’t seen Scott Guthrie, have you?

See you at TechDays 2012!!!

Transaction rollback in progress without an end

This is a quick post, I hope it’s useful to someone.

I was dealing with a rollback/kill process which I couldn’t kill.
An sp_whoisactive (by @adammachanic) showed a killed/rollback for more then 29 days!
A simple kill of spid 75 didn’t work.

 

This was the result of kill 75:

SPID 75: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.


The result of sp_whoisactive showed me it was an xp_cmdshell command so I knew I had to look at the processes and try to kill it from there. Of course, the Task Manager didn’t give me any details so I fired up Process Explorer from sysinternals and this is what I got:

 

So all I had to do was kill the process from within Process Explorer and voila.

(SQL Server) Consultant To Do list for new client

This list might come in handy for consultants (or anyone else). Feel free to comment so I can update the list.
This is a list I compiled over the years and it has saved me some time. I hope you find it useful.

  1. Physical access to the building. At day one, someone usually picks you up at the gate. Try to get a badge for the following days.
  2. Make sure the client has a domain account ready for you. Most clients don’t want you to connect your laptop to their network. When you can use your own laptop, make sure your anti-virus software is up to date.
  3. Ask for Internet access which you need for e-mail to stay in touch with your colleagues and to search online in case of problems.
  4. Access to the root of the C: drive. I had multiple occasions where I really needed access to the root and lost quite a bit of time because of restrictions.
  5. Access to all necessary SQL Server instances .
  6. Ask for the proper database role on the instances.
  7. Ask for RDP access when necessarily.
  8. Printer mapping.
  9. Where are the toilets, vending machine(s), restaurant(if any) and most important, the coffee machine?
  10. All necessary software must be installed (eg: MS Office, SSMS, …)
  11. What are the local helpdesk procedures?
  12. Timesheet procedure. Most clients have their own timesheet tool (even for consultants).
  13. Name of the project(if any). Might be important to name a project in Visual Studio.
  14. Which is the company standard for naming conventions (servers, databases, database objects, …)
  15. Is there a control versioning tool?
  16. Parking space (any know security issues?)
  17. Company policies (use of Internet, …)
  18. Security (SQL Logins, passwords,…)
  19. When to use your badge (toilet, lunch, smoking break)
  20. How much time do you have for lunch?
  21. Is the use of cell phones permitted?
  22. Is there any documentation of the project?
  23. Report regurlary to the client.

 

I keep a Lacie iamaKey with me all the time with the following utilities on it:

  1. CPU-Z (free)
  2. EventSentry
  3. PowerGUI (free)
  4. Remote Desktop Manager (free)
  5. SQL Sentry Plan Explorer (free)
  6. SSMS ToolsPack (free)
  7. TerraCopy (free)
  8. Sysinternals (free)
  9. RedGate SQLToolbelt
  10. SQLQueryStress (free)
  11. SQLIOSim (free)
  12. SQL Server Internals viewer (free)
  13. RML Utilities (free)
  14. SQLNexus (free)
  15. PAL (free)
  16. PSSDiag-SQLDiag Congiguration Manager (free)
  17. Hex Editor (free)

 

Also in my bag I keep:

  1. I wireless mouse (I prefer my own mouse)
  2. A descent UTP CAT-5 cable (aka network cable)
  3. USB cables
  4. Power Adapter for cell phone
  5. USB memory sticks
  6. External hard disk (includes a SQL script repository)
  7. A Swiss army knife
  8. Business cards
  9. Painkillers
  10. A Kindle
  11. A Moleskine notebook + Pens

1 day course PowerPivot for Business Professionals

About the Course

  • Are you an Excel power user and working as a business analyst, financial controller, …
  • Are you looking for a tool to create your own reports without help from IT …
  • Do you want to combine data from different sources …
  • Do you want to add your own calculations and analysis to existing reports

… well then PowerPivot is the tool you need!

PowerPivot for Excel is a data analysis tool that delivers unmatched computational power directly within the application users already know and love: Microsoft Excel. It’s the user-friendly way to perform data analysis using familiar Excel features you already know, such as PivotTable and, PivotChart views, and slicers. It’s the fast way to generate rich and interactive analysis tools. It’s the right way to achieve deeper business insight and shorter decision cycles.

In this 1 day training we will give you a jumpstart with PowerPivot for Excel. At the end of the day you will be able to combine data from different sources and turn this data into powerful reports and analysis tools without help from your IT department.

The course is bulk loaded with demos and hand-on labs. Students receive a text book and printed course material. More info on PowerPivot can be found on http://www.powerpivot.com/

Topics

  • Introduction to Self Service BI and PowerPivot
  • Importing Data
  • Enriching Data
  • Self-Service Analysis
  • Self-Service Reporting
  • Writing Data Analysis Expressions (DAX)

When: Wed. November 9th 2011, 9h-17h
Price: 385 EUR excl. VAT
Contact: info@kohera.be
Where: Kohera offices, Veldkant 31, Kontich, Belgium

Treat your SQL Server instances as patients in an ER

Just think for a minute, monitoring your SQL Server instances does not differ a lot from monitoring patients in an ER room. When we are dealing with a performance issue, one of the first things we do is check CPU pressure (heart rate), Memory pressure (blood pressure) and I/O pressure.

Just as doctors do, we use a number of quick tools (scripts) to have an idea what the problem might be. Based on those findings, we decide to do some tests (blood analysis/perfmon monitoring).

For over a year now, I have this idea to connect a heart rate monitor to an ill SQL Server. It’s a matter of interfacing but in theory it should be possible.

Now just imagine you have a stack of life monitors near your desk. A nice alternative is a slick layout on a tablet pc connecting to an instance. All I need now are scrubs!

photo by brykmantra under CC

Mr Denny presenting at SQL Server Days 2011

I’m really pleased that we could attract Denny Cherry (WebsiteTwitter) to speak at SQL Server Days 2011.

Mr Denny will be speaking about:

Optimizing SQL Server in a virtual environment
In this session we’ll look over some of the things which you should be looking at within your virtual environment to ensure that you are getting the performance out of it that you should be. This will include how to look for CPU performance issues at the host level. We will also be discussing the Memory Balloon drivers and what they actually do, and how you should be configuring them, and why. We’ll discuss some of the memory sharing technologies which are built into vSphere and Hyper-V and how they relate to SQL Server. Then we will finish up with some storage configuration options to look at.

Indexing Internals
In this session we’ll dig into the internal structors of indexes. We will explore the differences between clustered and non-clustered indexes, what’s layed out within each page of the indexes and how the SQL Server uses the data within the indexes to find rows quickly.

Table indexing for the .Net developer
In this session we will be looking at the best and worse practices for indexing tables within your SQL Server 2008 databases. We will also be looking into the new indexing features that are available in SQL Server 2008 (and SQL Server 2005) and how you the .NET developer can make the best use of them to get your code running its best.
Learn why indexes need to be created
Learn how indexes can help your databases performance
Learn how indexes can hurt your databases performance

Check out the agenda of SQL Server Days 2011 for more details.

SBM MVP Community Roadshow

SQL Server experts are often called in to support Microsoft business products as many of the products use SQL Server as a backend. To name a few: SharePoint, Axapta, Dynamics and Windows Small Business Server all use SQL Server.

Since Windows Small Business Server is a popular product and hence so is the question to support it, it’s always nice to get free training whenever it’s possible. The people behind the curtains of SBS Migration which are on the SBM MVP Community Roadshow take a halt in Belgium, November 29th.

For more details, check out the SBM MVP Community Roadshow website.