Category Archives: SQL Server

Renaming SSDT project brakes build

I thought I would do some cleaning up in my SSDT solution which turned out a bad idea.

In my solution I have three database projects which all started with the name “Database”. It seemed a good idea at the time because I also have several unit test projects having the same name as the database projects so I started those projects names with “UnitTestAdventureWorks” and “DatabaseAdventureWorks”.

I changed my mind as the icons for the database projects and unit tests are different and it just looked a lot more cleaner without the names Database and UnitTest in their project names.

Once I got rid of the prefixes and started to build…BAM, could not found project file “DatabaseAdventureWorks”. Of course not, I changed it a minute ago.

My first idea was to rename the project again to “DatabaseAdventure”. Visual Studio asked me to change the database in localDB as well. I said yes hoping it would rollback everything. Not only did it change the database names with the database prefix but also the project settings. I’m fine with the project settings but not the database changes.

Changing the name of the database in SSOX (SQL Server Object Explorer) back to normal (who wants their database named DatabaseAdventureWorks anyway? We know it’s a database!); Visual Studio did not ask me to change the project settings.

I have the impression that settings between project settings, SSOX and Solution Explorer are not automatically updated. Also building the project does not help.

If anyone has some good advice or a workaround, shoot!

 

 

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.

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

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.

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.

SQL Server, ears, noses and throats

I grew up between resistors, capacitors, solder and oscilloscopes. It was obvious I followed my old men footsteps so I went to a high school where I could choose to study electronics. (Later on I studied clinical chemistry but did not finish it because of illness).


Although I was not a bad student I never became good at it (electronics that is). But it learnt me to see and understand the interaction between hardware and software. A few days ago someone told me I am desperately in need of a vacation because I dreamt of an electric shaver with sensors, a data logger with an embedded SQL Server which pushed data about rotations and so on to another SQL Server when connected to his cradle to power up. And from there on import the data in a mini datawarehouse with Reporting Services reports on top. Now I can see it”s somewhat ridiculous (but not really).


This is where my next “project” comes in I dream about…I wonder what hospitals do with the data from the patients personal charts like temperature, blood pressure and so on. Does it get logged into a database? Let”s say it would be in a database, what do they do with that data in a perspective view of all patients? That keeps one thinking huh? I mean, reports would be nice to have in a hospital with +500 beds to figure out which floor e.g.: hart diseases, ottorhinolarynologie (ear, nose,and throat). Even more,what about connecting all those databases nationwide? What about data mining?


Maybe I do need a vacation


Steve

I so totally suck

My girlfriend is currently attending a yoga teacher training in Los Angeles. This was planned way back in 2007.
She asked me to come as well but since I”m not really fond of yoga I said: what on earth am I going to do over there if you”re in training 12 hours a day for 10 days long?!


It turns out the Heroes Happen Here event takes place in LA during that period!!!
I think I”ll crawl in small corner space now and wheep a little bit.


Please do not comment. Can”t handle it now