Skip to main content

Installing SQL Server Express to set up an Azure database locally


Installing SQL Express:

  1. Download SQL Express installer
  2. Switch to an account with full local admin permissions (our regular accounts don't have these so I had to use a global admin account instead) and install SQL Express with that account
  3. Once installation completes, make a note of the connection string you're given on the final page of the installer
  4. Open SSMS and connect to the database - default name is localhost\sqlexpress
  5. Navigate to Security -> Logins -> New Login
  6. Add the regular (non-admin) domain account that you'll be using most of the time
  7. Once added, open that account under the Logins section and then open the "Server Roles" page
  8. Add the account to the "sysadmin" role

Obtaining the Azure database:

  1. Open the database and select the "Export" option at the top of the "Overview" page
  2. Choose a storage account and container to drop the export file in and enter the server admin creds
  3. Once the file has been created, access that storage container and download the db export file

Restoring the export file

  1. Back in SSMS, connect to your local SQL express server instance again (if it isn't still connected), right-click on the "Databases" node and select "Import Data-tier Application"
  2. Select the db export file, give the database a name, click "Next"
Don't forget to update connection strings in your applications ;)  If they're running under your normal account (i.e. not a separate app pool account etc), it should look something like "Server=localhost\sqlexpress;Database=[your database name];Trusted_Connection=True;"

(This is obviously for local development and testing only; if you set up a real server like this, you're insane)

Comments

Popular posts from this blog

Using Log4Net to use both event log and a rolling log file

Here's the config section, note that the applicationNameproperty in the EventLogAppender needs to be the same as the event source in the windows event log that you want to log to.  If the event source doesn't exist, that appender won't work.  In this particular project I create that during install using WiX (which is covered in another post)

  <log4netdebug="true">
    <appendername="RollingLogFileAppender"type="log4net.Appender.RollingFileAppender">      <filevalue="log.txt" />      <datePatternvalue="dd-MM-yyyy" />      <appendToFilevalue="true" />      <locationinfovalue="false" />      <rollingStylevalue="Size" />      <maximumFileSizevalue="1MB" />      <maxSizeRollBackupsvalue="10" />      <staticLogFileNamevalue="true" />      <layouttype="log4net.Layout.PatternLayout">        <conv…

Using WiX to create an event source during install of a .NET framework project

Edit: so I guess I wasn't the only one confused with this stuff, as it's been my most popular post by far!  If I've helped you out or saved you some time, please let me know in the comments :)

In order for this to work, you have to add references to WixUtilExtension and WixNetFxExtension to your WiX project.  Once that's done, add this inside a <Component> element:

<Util:EventSourcexmlns:Util="http://schemas.microsoft.com/wix/UtilExtension"Name="EVENTSOURCEGOESHERE"Log="Application"EventMessageFile="[NETFRAMEWORK40FULLINSTALLROOTDIR]EventLogMessages.dll" />
Obviously replace EVENTSOURCEGOESHERE with your event source name.  NETFRAMEWORK40FULLINSTALLROOTDIR is a property set by the WixNetFxExtension which stores the path to the .NET framework v4 directory, but you can replace this with the corresponding property for the directory containing the relevant EventLogMessages.dll file.  So if you're using the .NET framewo…

How to make yourself a Dynamics CRM 2011 Deployment Administrator

Today I needed to deactivate one of our Dynamics organisations, but when I opened the Dynamics Deployment Manager, I received the following error:

"Only the Deployment Administrators are able to use Deployment Manager. You are not a Deployment Administrator."
Bummer. I did a bit of Googling and found this post by Ronald Lemmen (thanks for pointing me in the right direction!).  Since the Dynamics Deployment Manager is obviously checking the MSCRM_CONFIG database for this information I attached a database trace to it and found that it's executing these queries (among many others):

exec sp_executesql N'SELECT  Id, [DefaultOrganizationId], [IsDisabled], [Name]   FROM [SystemUser]   WHERE ((([Name] = @Name0)) ) AND (IsDeleted = 0) ', N'@Name0 nvarchar(41)',@Name0=N'{My windows domain account}'
exec sp_executesql N'SELECT  Id, [Name], [UniqueifierId]   FROM [SecurityRole]   WHERE ((([Name] = @Name0)) ) AND (IsDeleted = 0) ', N'@Name0 nvarchar…