Archive for the ‘SQL Server’ Category

Shrinking SQL Log Files During SharePoint Development

I have been developing for SharePoint lately, working on timer jobs. This resulted in tons of deployments and job runs, and before I knew it my SharePoint_Config database was huge! I was working in a virtual machine with disk partitions, and my data disk ran out of space. At least it was on a partition and I didn’t hose my OS….

So it seems to be a simple problem to solve. Backup the database, shrink the log file. Not so fast partner, this is SharePoint. The database is not in Simple recovery mode, it’s in Full recovery mode (for a reason I am sure, I just don’t know what it is). You can truncate the log file and remove the data, but the physical file won’t shrink. My colleague J Wolfgang Goerlich pointed out one of his old blog posts that was just the ticket. Essentially you are changing the mode, shrinking the file and changing the mode back all in one operation, which won’t upset SharePoint. The log file went from about 9 gigs to 600k. Nice.

As noted in the blog post, this is not a good thing to do to a production system, there are consequences. But for a dev system I wouldn’t worry about it.

Posted on:
Posted in SharePoint, SQL Server | Comments Off on Shrinking SQL Log Files During SharePoint Development

Configuring CRM 4.0 for SSRS Without the Data Connector

I’m sure the first question someone would ask me is why do this, if installing the  SRS Connector does the job just fine? And I would answer that it does work, and that’s what we did. For the first install. We were creating two instances of CRM, one for a production environment, and one for a test environment. Each instance of the CRM application was on its own virtual web server, and the databases were to be housed in a single physical SQL Server. In this situation, we wanted both the sets of the CRM databases on the same database server. CRM always names the config database MSCRM_Config, so you can’t install a second instance in the same database server because unlike SharePoint, the configuration database name is always the same, and the second install just simply fails. So the initial solution to the problem was to use a second instance of SQL Server on the same physical server. This worked great, until we had to install the SRS Connector on the SQL Server. During the install of the SRS Connector you configure the URL to Reporting Services. The connector only works for one instance of SSRS on the physical box where it is installed. This left us out in the cold for the other instance in terms of the SRS Connector. Since CRM 3.0 didn’t have the SRS Connector, it’s pretty well-known how to configure Kerberos trusts so that SSRS can work from inside CRM without the SRS Connector, so that’s what we needed to do.

There are two ways to accomplish this:

  • Run the CRM site IIS application pool under a domain account
    • Set the SPN for that account
    • Set up trust for delegation to that account
  • Run the CRM site IIS application pool under a local account
    • Set a trust for the machine hosting CRM

Since it seemed to be fewer steps and less to maintain, we went with the second option, setting up an Active Directory trust for the web server hosting CRM.

It was not difficult to set the trust:

  1. On your domain controller (Server 2008 for me), open Active Directory Users and Computers.
  2. Navigate to (or find) the computer hosting CRM
  3. Open the properties of the computer
  4. Choose the Delegation tab
  5. Select Trust this computer for delegation to specified services only
  6. Select Kerberos only
  7. Click the Add button
  8. Click the Users or Computers button
  9. Enter the name of the CRM web server
  10. In the resulting list, choose http in the Service Type Column.
  11. Click a couple of OK buttons
  12. Reset IIS on the CRM server

It should look something like this when you are done:

Any other DC’s may have to sync, but at this point you should be all set.

Posted on:
Posted in CRM, SQL Server, SSRS | Comments Off on Configuring CRM 4.0 for SSRS Without the Data Connector

Moving Commerce Server 2007 Databases

I needed to move the set of Commerce Server databases from one server to another, so I developed and tested this procedure.



  1. Script the database users to file. Our site is using domain users, so I performed this step first to keep the users in sync with a backup/restore of the databases.
  2. Backup each of the Commerce Server databases:

    • Marketing
    • Marketing_Lists
    • ProductCatalog
    • Profiles
    • TransactionConfig
    • Transactions
    • MSCS_Admin
    • MSCS_CatalogScratch

  3. Transfer the scripts and backup files to the new server.
  4. Run user scripts against the new server
  5. Restore each database on the new server
  6. On the web server, open Commerce Server Manager to change connection strings:

    1. Admin Database:

      1. Click the Commerce Server Manager node
      2. Click the Administration Database link in the right panel
      3. Change the Connection String, click OK

    2. Profiles


      1. Expand this node:
        Global Resources/Profiles

      2. Right-click and change the following 3 connection strings:
        s_BizDataStoreConnectionString
        s_CommerceProviderConnectionString
        s_ProfileServiceConnectionString

    3. Profile Database

      1. Expand this node:
        Global Resources/Profiles/ProfileCatalog/DataSources/ProfileService_SQLSource/Partitions
      2. Right-click SQLSource in the right pane and choose properties
      3. Change the Connection String, click OK

    4. Inventory Database

      1. Expand this node:
        Commerce Sites/CSharpSite/Site Resources/Inventory
      2. Right-click Inventory Database in the right pane and choose properties
      3. Change the Connection String, click OK

    5. Marketing Database

      1. Expand this node:
        Commerce Sites/CSharpSite/Site Resources/Marketing
      2. Right-click Mailing List Connection String in the right pane and choose properties
      3. Change the Connection String, click OK
      4. iv. Right-click Marketing Database Connection String in the right pane and choose properties
      5. Change the Connection String, click OK

    6. Product Catalog

      1. Expand this node:
        Commerce Sites/CSharpSite/Site Resources/Product Catalog
      2. Right-click Catalog Database in the right pane and choose properties
      3. Change the Connection String, click OK

    7. Transaction Config

      1. Expand this node:
        Commerce Sites/CSharpSite/Site Resources/Transaction Config
      2. Right-click Transactions Config Database in the right pane and choose properties
      3. Change the Connection String, click OK

    8. Transactions

      1. Expand this node:
        Commerce Sites/CSharpSite/Site Resources/Transaction Config
      2. Right-click Transactions Database in the right pane and choose properties
      3. Change the Connection String, click OK

  7. Change connection strings in the web.config for site (if any using API calls)
  8. Restart IIS

Posted on:
Posted in Commerce Server, SQL Server | Comments Off on Moving Commerce Server 2007 Databases

Use RunAs to Connect to SQL Servers in Another Domain

Doh! Another simple thing I wish I knew (or thought of) previously: Using RunAs to connect to SQL Servers in other domains

Posted on:
Posted in SQL Server | Comments Off on Use RunAs to Connect to SQL Servers in Another Domain

Sql Server Fix Logins

Sometimes when copying databases, for instance from production down to development, the user accounts become dysfunctional. The database login no longer matches the overall server login. To re-synchronize them use:

exec sp_change_users_login auto_fix, ‘someusername’

Posted on:
Posted in SQL Server | Comments Off on Sql Server Fix Logins

More SelfSSL Issues

I blogged previously about some issues with SelfSSL and multiple web sites. A colleague of mine, Charles Medcoff, blogs about a related problem with SelfSSL and SQL Server.

Posted on:
Posted in Security, SQL Server | Comments Off on More SelfSSL Issues

How ADO.Net 2.0 Batch Updates Really Work

An excellent post by Pablo Castro of  the ADO.NET team explaining the mechanics of how batch updates work with ADO.NET 2.0 and Sql Server 2005.

Posted on:
Posted in SQL Server | Comments Off on How ADO.Net 2.0 Batch Updates Really Work

Batch execute SQL files in a directory

Having to excecute hundreds of .sql files in an environment without port 1433 open led me to this nugget of a Usenet post: How to execute multi-file.

Here is the gist of it, the command line for executing all those .sql files with osql:

FOR %i IN (*.sql) DO OSQL -n -U sa -P sapassword -d databasename -i %i

I am sure I could have merged them all into one file or made some sort of batch vbs, but this did the trick nicely.

Posted on:
Posted in SQL Server | Comments Off on Batch execute SQL files in a directory