Changing SQL dB from Single User Mode

Recently, a CRM SQL Server 2005 dB was turned into Single User mode. This means that only one user may connect to the dB at a time, and if a service is connected, then you can't connect.

It took some research and elbow grease, but this is how I changed it back to multi-user mode.
(I am using 'database_MSCRM' as my database name for my examples.)

I ran this SQL query to find the active connection to the dB that is not my own:
select spid from master..sysprocesses where dbid = db_id('database_MSCRM') and spid <> @@spid

I got the ID, which was 52. Then I ran this query

Kill 52
ALTER DATABASE database_MSCRM SET MULTI_USER

That killed the connection, then changed the setting to multi-user. I tried to run them separately, but the service was reconnecting so quickly that the Alter command wasn't valid.
I decided to run them together, and it worked great!

Good luck,
Mark

reference: http://msdn2.microsoft.com/en-us/library/aa933230.aspx
reference: http://www.kodyaz.com/articles/alter-single-user-multi-user-mode.aspx

Phone Number on Windows Mobile 6 Phone

Not at all related to CRM:

Someone was asking how to find your number on a Windows Mobile 6 phone...I believe if you go to
Start
Settings
Personal
Phone

you should see it listed in the titlebar. Correct me if this is wrong, but I think that is where it is. Just FYI.

RDP to Console

In our line of work, we have to utilize RDP (Remote Desktop Protocol) access to client servers. With the limitation of 2 RDP sessions and a console session, it can be hard to juggle access. A trick that was told to me a while ago (and I thought I should pass along) is the ability to RDP to the console of a Windows 2003 Server.

If you "run" this (Start Run) and replace servername with the (surprise...) server name:

mstsc /v:serverName /console

For more information on connecting to a console of shadowing a console session, see:
http://support.microsoft.com/kb/278845

Thanks,
Mark

Data Integrity: Mass editing text to picklist

Recently, we migrated data from a source to CRM 4.0. The client's previous field was a textfield but they wanted to control the data in a picklist in their new CRM system. In order to keep all of the information, we migrated the data from a textfield into a textfield. (If you try to map values from a textfield into a picklist, you can run into many errors if the data isn't scrubbed well).

In order to update the picklist in an efficient manner, I instructed the client to create an Advanced Find that displayed the picklist and textfield, filtered where the picklist was null and ordered by the textfield. This way you can look at what should be similar (in not the same) values in the textfield, can highlight the records and then mass edit the picklist to equal the correct value. When you have done several records, you can refresh the search result and it will only return the records that still need to be updated.

I have seen people do similar updates in more taxing ways and wanted to post my thoughts on what should be an easy (although slightly manual) way of keeping the integrity of your data.

Thanks,
Mark

Determing the SQL Server 2005 version and SP

Hello,

I find that I need to figure out what version of SQL build and SP is applied to a client's SQL Server. For 2005, running either of these two queries is extremely helpful:
SELECT @@Version
or
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

I found excellent information on this Microsoft page:
http://support.microsoft.com/kb/321185

In general, I have been looking to see if the build is: 9.00.3054 or 9.00.3159 to figure out if the installation is SQL Server 2005 with SP2.

Thanks,
Mark

CRM 4.0: Trouble with redeploying existing organization

So this week was very interesting. We had a client who wanted to go from the hosted Dynamics CRM 3.0 to an on-premise Dynamics CRM 4.0 Professional edition.

Since they wanted to keep their hosted 3.0 system functional, I built out a new server with:

• Windows Server 2003, w/ SP2
• SQL Server 2000, w/ SP4
• All windows updates, including .NET FMWK 3.0

We redeployed CRM 3.0 to the new server, then upgraded the SQL installation from 2000, SP4 to 2005, SP2. We ran the SQL update script to change the buildversion of the CRM database to comply with CRM 3.0 On-Premise. The SQL script is listed below:

select *from buildversion
update buildversion set buildnumber = 5300
update buildversion set revision = 0
update buildversion set minsupportedclient = '3.0.5300.0'

After you run the SQL script to upgrade the database, it will be able to appear in the CRM 3.0 On-Premise installation (when you connect to an existing installation).

After installing CRM 3.0, we upgraded to CRM 4.0 and backed up the database to transfer to the client's new CRM 4.0 environment.

Redeployment for CRM 4.0 has been designed to be system admin friendly.

• You install CRM 4.0 on the new server
• Rrestore the existing CRM 4.0 Org_MSCRM database to SQL 2005
• Access the MMC snap-in Deployment Manager
• Import Organization (and you are done.)

I walked through all of the steps on my own test server before attempting it on the client's environment. My test worked perfectly smooth.

The actual execution wasn't quite so picture-perfect. Yeah...we ran into different issues while trying to restore the database to the new SQL Server.

One of the errors we received was: Error 3154: The backup set holds a backup of a database other than the existing database.

After doing some research, I found that it was a semi-common problem in SQL and that you had to use a REPLACE clause when executing this statement. Since I couldn't execute the restore through the GUID, I decided to try to execute the restore manually through a SQL script. This is the script that I ended up writing. (oh the client wanted the sql database files to exist on a different harddrive, hence the MOVE clause):

Query:
RESTORE DATABASE CRMName_MSCRM
FROM DISK = 'E:\InstallDBs\CRMName_MSCRM-Full Database Backup.bak'
WITH REPLACE,
MOVE CRMName_MSCRM' TO 'E:\sqldata\CRMName_MSCRM.mdf',
MOVE CRMName_MSCRM_log' TO 'E:\sqldata\CRMName_MSCRM_log.ldf'

Results:
Processed 59816 pages for database CRMName_MSCRM', file CRMName_MSCRM' on file 1.
Processed 4 pages for database CRMName_MSCRM', file CRMName_MSCRM_log' on file 1.
Processed 17 pages for database CRMName_MSCRM', file 'sysft_ftcat_documentindex_eacc0dc2b9b94e2c98d072e33598dfe6' on file 1.
RESTORE DATABASE successfully processed 59837 pages in 32.453 seconds (15.104 MB/sec).

Using the above script, it appeared to Restore correctly. The importing of the organization failed in a later step. Since that didn't work, we had to figure something else out.

The solution:

I mentioned that I had restored this database to my own test server. I stopped all of the SQL Services that were running, physically copied the mdf and ldf (SQL database and log) from my test server to the client server. The mdf and ldf files were ATTACHED to the client's SQL Server and then we executed the Import Organization against that attached database. The import took about 10 minutes, and WORKED! (That was the most nerve-wracking progress bar I have ever watched.) I think we could have also used a copy of the mdf and ldf from the upgraded database (from 2000 to 2005). So if you can't restore your database into the target SQL Server, you may want to try our workaround:

• Install CRM 4.0 on target CRM Server
• Stop all SQL services on upgraded CRM/SQL Server (source)
• Copy MDF and LDF files from source to target server
• Attach MDF and LDF files to target SQL Server
• Use Deployment Manager to Import Organization

Good luck in your upgrades! We worked until almost 3AM in order to land upon this solution ...hopefully your chances are improved by this post!