Sql-server – Pros and cons of accessing sql server using ssms locally over on the actual server

Pros - It is the natural key, it makes sense and it will likely be searched on, I presume?

Cons - The default behavior (which is totally changeable) is for a primary key to be the clustered index. An alphanumeric doesn't make the best candidate because inserts can cause page splits because they aren't set on an ever increasing value like an identity column. The Int identity column will take less space (4bytes) compared to the character data (40+bytes for the unicode) . This makes your other indexes larger since the clustered key is part of them. If you ever change how you identify your customers and make customer codes, this all breaks - going with a surrogate insulates you from those type of changes.

In this situation, I tend to optimize for the insert performance and go with an identity column more often than not for the clustered key and primary key. I really like integer clustered indexes. (Now I know your question was not about clustered index, it was about primary key... You could still choose some other column to be the clustered index and make this your primary key, you could also put a unique constraint on this and treat it as a natural key but not make it your primary key).

I would at the very least index this with a unique constraint and treat it like a natural key. I just don't know if you really need to make it your primary key.

Kimberly Tripp is a trusted resource who has a lot to say about primary keys and (more so) clustered keys on her blog - https://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/

This is all just my opinion - YMMV.

So there a couple thoughts here from my end. And I think they probably follow an order:

1. The first principle is - you will never secure your MDF file from being looked into by someone with SQL savvy. There are ways around most of what you can do. If you are deploying this application to a customer, and that customer has enough SQL knowledge and you've given them a data file - they can do what they like with it ultimately.. This principle rules all of the others.
2. Support contracts and license agreements protect you legally here - that is outside the scope of this format. But you have to trust the folks buying your app to honor their support agreements and EULAs. Every vendor out there has to do that same thing.
3. You can, however, do things like encrypt data in tables and encrypt/decrypt it in and out from your application. This can help protect data, but it isn't foolproof. Someone can try and attach a debugger, dig into the traffic and try and get a sense for what is going on.
4. You can encrypt your stored procedures to protect the stored procedure code - again this isn't full proof (remember Principle #1 rules them all here)

So there are things you can do, but the biggest question that I would ask if I were you is:

Why?

What I mean is - what is your goal? Customers see databases all the time. In fact as a DBA and Consultant with 15 years of experience, I can honestly say that 90% or more of the databases I work with are wide open to the folks who purchased the application. If they want to, they can certainly play with the data in their database directly. Most of them don't because they like support contracts to remain valid. Some of them do because their vendors are pains to work with and don't give common sense support and updates.

So you should check your motives. If you've made software that someones wants to buy, and they've paid for it and deploy it locally following your instructions and have entered into an agreement with you - why would they want to build their own app? And if they do? What is the harm? Charge enough money and make your agreement strong enough that if they do it is a non-issue.

Also - Is this a new application? SQL Server 2005 is 3 versions back from the currently released version (SQL Server 2012) and a new release (SQL Server 2014) is right around the corner.

You also said "the application is installed along with an sql server 2005 installer, so I don't have any control on my app when it is deployed/installed in other computers"

With the right installers and install procedures you should have control over how the application is installed. There is actually a lot in your question sort of behind the question that is hard to answer in this forum without a lot of back and forth. Some of the questions that come to mind are around areas like "How does your app install? Is there an installer app?", "What are the instructions for the installation and use of SQL Server?" "Are there configuration files in place to help determine where things get placed and how to connect to the database?", "Do you provide guidance to the customer on best practices for deployment?"

Don't answer those questions here - they are out of scope, but those are some of the questions you should be asking yourself as you go on this quest. I would recommend reading up on SQL Server application development and spending more time learning about deploying applications in general. I can't think of a good reference right now - but perhaps someone can add an answer with some. There must be something on msdn.com that can help here.