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.

Transaction replication: Property LoginSecure cannot be changed or read after a connection string has been set

I was tasked with a simple easy peasy transaction replication setup (is there such a thing?) between two countries.Came in, took a cup of coffee, Rammstein on the headphones and thought it was going to be a smooth morning. What could go wrong…

When I “tried” to configure the distributor, the wizard threw the following error at me:Property LoginSecure cannot be changed or read after a connection string has been set. (Microsoft.SqlServer.ConnectionInfo)
Mmm, almost all answers on the web took me to the following solution:

  • Right click local publication followed by Distributor properties
  • Select the publisher in the list, click the browse button
  • In agent connection mode, select “impersonate the agent process account”

Well yes, I am looking at it. “impersonate the agent process account” seems to be the default setting.
I was totally lost at that moment and I knew it was going to be a long “short morning”.

I wanted to generate scripts for replication although I knew no replication was setup at the moment but I just wanted to try for the heck of it.

The generate scripts wizard came up with a warning saying I had to use the proper server name, no IP address or alias and there it was…SSMS was connected to the instance using an IP address. Once I connected using the server name…no more errors when finishing the new distributor wizard.

Can you disable Full-Text Search on database level?

The answer is no. At least not since SQL Server 2008.

Have a look at your model database on a new instance, the Full-Text check box is checked and greyed out.
Stopping the SQL Full-text Filter Daemon Launcher isn’t going to work either to get rid of the setting.

Even the ALTER DATABASE command on BOL doesn’t say anything about it except changing the default full-text language.

The reason is very simple. Since SQL Server 2008, Full-Text Search or FTS is build in the database engine and therefore, all databases are full-text enabled by default. However, keep in mind Full-Text Search is an optional feature during the setup of SQL Server.

Top 10 mistakes found on SQL Server instances

If you’re like me, the first thing you do when they put you in front of a SQL Server instance you have never worked on before is looking for misconfigurations or simple put: let’s see how they messed up

I keep running into the following bad practices:

  1. Auto-Shrink and Shrink jobs
    Seriously? Probably the holy grail of bad SQL Server practices.
    Read more about it.
  2. The world wide web service is running.
    Why? Give me a reason why you would use the www service on a dedicated SQL Server instance in a production environment?
    Yes, there might be a short list of reasons but let’s be honest, how many instances are out there running this service? Indeed…
  3. Internet access.
    Give me one good reason why you need to have Internet access? Do you check your mail on the production SQL Server? You play Angry Birds during lunch?
  4. RDP usage.
    Yes, you know I’m talking about you. Why do you keep using RDP on the SQL Server instances?
    As a database administrator, I can’t think of any tasks you won’t be able to execute from a remote machine.
    It’s a waste of resources you’re using on a production machine.
  5. Client tools installed on the server.
    Yes go ahead, install SSMS and BIDS on the server so the guys in China can do their development using RDP…
  6. Service accounts.
    Yes, install all instances using the same account so if one breaks down, all services break down.
    Use a dedicated domain account with no rights to run the services instead.
  7. Service accounts with sysadmin privileges.
    Why? An ordinary domain account should do it. It’s a security threat.
  8. Power plan set to balanced.
    Sure, let’s save  some power running this very important heavily queried instance.
    Setting the power plan to high performance actually boosts the performance of the instance.
  9. Dedicated drives.
    It’s improving but I still see a lot of production instances with only one drive.
    If it’s up to me, I would even use dedicated drives for indexes.
    Use dedicated disks for mdf, ldf and TempDB. Don’t forget to put the local backups on a dedicated drive as well. Taking backups is a write process and you don’t want to query (read) data from the same disk which is writing to a backup.
  10. Disk space.
    I try to maintain a 15% free disk space on all drives.
    Disk space is filled up starting at the center of the disk. A longer distance from the center of the disk means a lower speed.

There are plenty more of those but 10 is a nice number.

Join the Private Cloud Roadshow in Brussels (25 April) and Ghent (26 April)

Join experts Kurt Roggen (MVP) and Mike Resseler (MVP) for the Private Cloud Roadshow.

In this half day you will learn more about private cloud infrastructure setup and how you can monitor this. Learn how to create your private clouds and how to deploy standardized applications or services into these clouds. And as a final session you will learn how you can provide automation in your private cloud.

There are 2 options to attend (same content, different location):

25 April 2012 in Brussels

26 April 2012 in Ghent

This TechNet event is free of charge and Microsoft will be giving away one Nokia Lumia 800 Windows Phone in each location.
Detailed agenda:

13:00 : 13:30 Welcome & Coffee

13:30 : 14:30 Building your Private Cloud Infrastructure
Learn how to build your Private Cloud infrastructure, by dealing with Fabric Management (Compute/ Hypervisors, Storage, Network), which will serve as the basis for the Private Cloud that you will be creating. We will discuss how to deploy, configure and manage each of these different elements in your datacenter.

14:30 : 14:50 Break

14:50 : 16:30 Creating, Monitoring & Operating your Private Cloud
Learn how to create your private clouds and how to deploy standardized applications or services into these clouds. Learn how to monitor your clouds and how to can handle change requests. All this key area’s will be addressed to give you an idea of what is happening in a private cloud after it is up, running, and into production.

16:30 : 16:50 Break

16:50 : 18:00 Automating & Delivering Services in your Private Cloud
Learn how you can provide automation in your private cloud. Discover also how your cloud services can be offered and consumed using different self-service portals and what their differences are.

18:00 : 19:00 Networking & Drinks

 

PSR:Problem Steps Recorder

We’ve all been there, an end user sends you an e-mail: “it does not work”….ok, what does not work? And how or what exactly did you do?

I recently found out there is a small nifty tool which comes with Windows 7: Problem Steps Recorder or simply PSR.

It runs from the command prompt or Run box, simply type PSR. You hit Start Record and you start reproducing the error or the test scenario. It takes a screenshot of every click and even adds a description like:

Problem Step 1: (2/04/2012 21:00:02) User left click on “Object Explorer Hierarchy (outline)” in “Microsoft SQL Server Management Studio”.

It even highlights the sections you clicked or changed (see green rectangle in screenshot below).

After stopping the recording, a MHTML file gets zipped and you have the option to save or send the file by e-mail. It’s not only an excellent way of reproducing errors or test scenarios, it can also be used to document.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

TechEd Europe 2012 (26-29 June, Amsterdam)

Another edition of TechEd Europe is coming up on 26-29 June, Amsterdam

Four days of more than 1,000 education opportunities and hands-on training across the full range of Microsoft products and solutions including 400 breakout sessions spread across 12 tracks and 250 self-paced labs created by product experts.

There also an additional day of deep training via the pre-conference seminars (Monday, 25 June)

There is a database and business intelligence track as well including 30 sessions:

  • Auditing in Microsoft SQL Server 2012
  • BI Power Hour
  • Big Data for the Masses: The Future of Big Data with Microsoft
  • BISM: Multidimensional vs. Tabular
  • Building BI solutions with SQL Server PDW AU3
  • Building Scalable Apps with SQL Azure!
  • Building Self-Service BI Applications Using PowerPivot
  • Building Stylish Power View Reports
  • Building the Perfect BI Semantic Tabular Models for Power View
  • Business Insights, BIG Data and the Cloud: Transforming Your Business on Your Terms with the Microsoft Data Platform
  • Business Intelligence and Data Visualization: Microsoft SQL Server 2012
  • Configuring Kerberos for Microsoft SharePoint 2010 BI in 7 Steps (SQL Server 2012)
  • Deep Dive into XQuery and XML in Microsoft SQL Server: Common Problems and Best Practice Solutions
  • Deploying and Managing a PowerPivot for SharePoint Infrastructure Using Microsoft SQL Server 2012
  • Deploying Microsoft SQL Server 2012 Using Windows Server Core
  • Developing and Managing a BI Semantic Model in Microsoft SQL Server 2012 Analysis Services
  • Enterprise Information Management (EIM): Bringing Together SSIS, DQS, and MDS
  • Exploring SQL Server Data Tier Applications
  • Integrating SQL Server Filetables, Property Search, and FTS/Semantc Search
  • Microsoft SQL Server Data Tools: Database Development from Zero to Sixty
  • Microsoft SQL Server Performance Tuning and Optimization
  • Optimizing Microsoft SQL Server Performance in a Virtual Environment
  • Running Reporting Services in SharePoint Integrated Mode: How and Why
  • SQL Server Always On – Backup on Secondary Replicas
  • SQL Server Columnstore Performance Tuning
  • SQLCAT: HA/DR Customer Panel — SQL Server 2012 AlwaysOn Deployment Considerations
  • SQLCAT: SQL Server HA and DR Design Patterns, Architectures and Best Practices using SQL Server 2012 AlwaysOn
  • Tips & Tricks for Microsoft SQL Azure Federations: How to Build Cross-Federation Queries and Other Tips
  • Troubleshooting SQL Server Spatial Query Performance: A Deep Dive into Spatial Indexing
  • Using BISM Tabular in Microsoft SQL Server Analysis Services 2012

http://europe.msteched.com/

 

Partially Contained Databases: Permissions used by contained user vs login with same name

I recently gave a webcast on Partially Contained Databases and one of the attendees had an interesting question:

Hi Steve,

About Contained database, let’s say I have the same username defined at the SQL Server level and at the contained database level.
The 2 users have different roles on the database which also means different rights.
What should I expect in term of rights when I connect to the database using that username ?

Regards,
Cedric

So I said I would try it out. It didn’t work out as expected. The database role got lost during the conversion from non contained to partially contained. I reproduced it like three times and three times it failed until I tried it on another instance on another machine and…it worked just fine(…)

So, a small example script:

Let’s start with creating a simple user database called PCDB (Partially Contained Database)

– Create a Windows login “Heidi”
CREATE LOGIN [KOHERA-KADHeidi] FROM WINDOWS WITH DEFAULT_DATABASE=[PCDB], DEFAULT_LANGUAGE=[us_english]
GO

USE [PCDB]
GO

– Create a sql user with password “Heidi”
CREATE USER [Heidi] WITH PASSWORD=N’?ĹÌÔ´?Ù§9?p?T#ó÷û?ÿP7+£2_s_×’, DEFAULT_SCHEMA=[dbo]
GO

– Assign the db_denydatareader db role to “Heidi”
ALTER AUTHORIZATION ON SCHEMA::[db_denydatareader] TO [Heidi]
GO

— Let’s create a table for some testing
create table MyTable(ID tinyint, Name varchar(12))
insert into MyTable values (1, ‘Heidi’);

I then logged in as “Heidi” (SQL user with password) and executed a simple select statement:

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object ‘MyTable’,database ‘PCDB’,schema ‘dbo’.

So that seems to work. User “Heidi” was assigned the db_denydatareader db role

So now let’s start SSMS as Windows user “Heidi” and authenticate with user “Heidi” in the master db and try to execute an insert stament

insert into MyTable values (2, ‘Heidi’);

Msg 229, Level 14, State 5, Line 1
The INSERT permission was denied on the object ‘MyTable’, database ‘PCDB’, schema ‘dbo’.

Conclusion:
There is no mix-up on the db roles when authentication on instance level or database level. The permissions based on the db roles are respected.

 

 

The Fantastic 12 of SQL Server 2012

I’m excited to be a part of the 12 Hours of SQL Server on February, 24th hosted by Microsoft BeLux.

12 webcasts about SQL Server 2012 by 12 speakers both local and international starting at 12:12 PM.

The speakers:

  1. Steve Verschaeve (B) - Partially Contained Databases
  2. Jennifer Stirrup (UK) - Power View from the Data Visualisation Perspective
  3. Frederik Vandeputte (B) - Building your first Analysis Services Tabular BI Semantic model with SQL Server 2012
  4. Pieter Vanhove (B) - Manageability Enhancements of SQL Server 2012
  5. Nico Jacobs (B) - What’s new in Integration Services 2012?
  6. Karel Coenye (B) - Manageability Enhancements of SQL Server 2012
  7. Frederik Vandeputte (B) - Enhance your Analysis Services Tabular BI Semantic to get the most out of Power View
  8. Dandy Weyn (US) - Performance and High-Availability at Scale with SQL Server 2012
  9. Gert Drapers (US) - Database Schema Management & Deployment using SQL Server Data Tools (SSDT)
  10. Cihan Biyikoglu (US) - Inside SQL Azure: Self Governing, Self-Healing, Massively Scalable Database Service in the Cloud
  11. TBD
  12. TBD

Partially Contained Database – Part 2: Create database, convert non partially contained database and migrate users

Now that we enabled the instance for contained databases, see my post Partially Contained Database – Part 1: Setup, we are ready to create a partially contained database (Partial-CDB) and convert an existing non contained database to a Partial-CDB.

1. Create a Partial-CDB

Create a new user database like you normally do either by using the GUI or Transact-SQL. Notice the Containment type and set it to Partial. There is no fully containment yet in SQL Server 2012. You will have to wait for the next version or migrate to SQL Azure.

Or, doing it using T-SQL:

CREATE DATABASE [PartialCDB] CONTAINMENT = PARTIAL
GO

2. Create a user using the contained database authentication

Partial-CDB lets us choose between a SQL user with password or a Windows user. Let’s go for a SQL user with password as it is specific to a partial-CDB. Notice here that our new user is not linked to a server login which makes it not dependent on the instance. That’s it! All  you have to do is create a user with a password and it’s contained within the database.

Or using T-SQL:

USE [PartialCDB]
GO
CREATE USER [Giselle] WITH PASSWORD=N'xxx'
GO

3. Migrating users

Converting a non-partially contained database to a partially contained database does not mean that the existing database users are automatically migrated as well.

Before we migrate the user(s), let’s have a look at the authentication type of the user(s):

select name, type_desc, authentication_type, authentication_type_desc
from sys.database_principals
where name = 'giselle'

Result:

There is a system extended stored procedure sp_migrate_user_to_contained that takes care of the migration for us.
We will migrate all users using a cursor. I know, we’re not supposed to use cursors but I think it can be done for small administration tasks.

declare @user sysname
declare user_cursor cursor
for select dp.name
from sys.database_principals dp
inner join sys.server_principals sp
on dp.sid = sp.sid
where dp.authentication_type = 1
and sp.is_disabled = 0

open user_cursor
fetch next from user_cursor into @user
while @@FETCH_STATUS = 0
begin
execute sp_migrate_user_to_contained
@username = @user,
@rename = N'keep_name',
@disablelogin = N'disable_login';
fetch next from user_cursor into @user
end
close user_cursor
deallocate user_cursor

Now that the migration is done, let’s check:

select name, type_desc, authentication_type, authentication_type_desc
from sys.database_principals
where name = 'giselle'

Result:

We see that the authentication type has changed from 1 tot 2.

That’s all there is to it!