Powershell script to create a dev-base from a live-database

Back when I worked for Promod (one of my first client), the dev team had a wonderful toy : each monday, the database we used for building applications was updated to reflect the last data from the live database.Since then, I have always tried to mimic this feature for every project that I worked on.

The trick was not really pretty : every monday, a database backup was restored from live environnement to the dev one. Then a sometime really long script/sql batch was launched to update the schema (every change that we made to the database had to be put in script in a special folder, and all those scripts were executed every monday)

This week, I really wanted to get this thing on a new project but the vbscript-based tool I've used many times before didn't worked for some reason, so I have rewritten the whole thing with Powershell.

The script is split in a few parts :

• first we have to ensure that no one is locking the database. The dirtiest of tricks is used here : I just shutdown and restart SqlServer, remembering to restart all the dependent services (such as Sql Server Agent) that were running before the restart

 $depserv = (get-service mssqlserver).DependentServices 
              | ?{$_.Status -eq 'Started'}
 restart-service mssqlserver -force
 $depserv | start-service

• a restore database is launched thru osql [Updated 05-mar-2007 : As many of you may have guessed, the actual copy of the .bak from the backup server should be done before restarting sqlserver - I didn't put it before in this post only for readability.]

copy \\backupsrv\live\newproject.bak d:\dev\newproject.bak
osql -E -Q "RESTORE DATABASE NewProject from disk=
      'd:\dev\newproject.bak' with replace"
del d:\dev\newproject.bak

• a Team Foundation Server-based refresh of the script directory is then triggered

&($env:tfpath) get newproject\datascripts /r

• for each .sql file in the aforesaid directory, powershell just run osql with this file

 dir ($env:scriptdir)+"\*.sql" 
     |% {osql -E -d NewProject -i $_.FullName}

With less than 10 lines (I had to define the scriptdir & tfpath variables, and to add a little error handling), this script replace an about-150-lines vbscript file, that's pretty amazing !

Visualiser les commentaires(RSS) ou en Ajouter

Lazy-boy server monitoring (1)

They say : there's no better techie than a lazy techie. Keeping that in mind, I've just put together a small Media Center Add-in which enables me to monitor my servers from my TV.

This add-in (as you can see on the screenshot if your french is not too bad) shows a notification whenever one of my server have issues (the issues are collected by a MOM-like - but really smaller - service and sent to a special "maintenance" server). Clicking on "Afficher le détail" (show me the details) displays the list of errors.

With this little project, we will see some of the extensibility features of Vista Media Center. For it to remain as simple as possible (and because I'm not yet fluent in MCML), I'll use an XBAP application to show the details and a small non-visual MCPL add-in for the notification.

XBAP Application

For this post, let's just say that this application exists, is written as a browser wpf application and looks like this : (I know that it is not really good looking and will try to make it prettier before I do another post about it)

If you want to build a XBAP application for Media Center, there are a few things to remember : 

• the layout and font-size should be chosen to be usable at 10-feet
• display resolution and even format is the real unknown : 480p, 720i, 1080p or even a mere 800x600, the sheer number of different resolutions is frightening, so viewbox and dynamic layout are your friends
• most of the time, your user won't have anything but the remote to use your application, so you'd better make it simple to use and forget the idea of complex data-entry
• Media center displays (ugly) on screen-commands when the mouse is moved, so don't forget to make space for them

The notification add-in

The second project is what displays the notification bar. I've used the Windows Media Center Presentation Layer Application (Microsoft really have a way with names...) which came with the SDK. It provides all the necessary files (and much more) for this project :

• an add-in class
• a registration file
• many other things that won't be used in this particular project

As the add-in will run in background, the whole code will be found in the add-in class :

    public class MyAddin : IAddInModule, IAddInEntryPoint
    {
        public void Initialize(Dictionary<string, object> appInfo,
               Dictionary<string, object> entryPointInfo)
        {
        }
        public void Uninitialize()
        {
        }
        public void Launch(AddInHost host)
        {
          ...
        }
    }

I didn't need to do anything when Initializing / Uninitializing, so only the Launch method will be used.

        public void Launch(AddInHost host)
        {
            if (!CheckSingleInstance())
                return;

            _host = host;

            while (true)
            {
                NotiftIfErrors();
                Thread.Sleep(30000);
            }
        }

After ensuring that another instance of the add-in is not running - if you use a mutex to do such a test, remember to use a local mutex. If you don't, and use global mutex, the add-in may not run on an extender - the method simply enter an infinite loop which checks for errors and goes to sleep for 30 seconds. I did it quick and dirty (that while(true) is not the prettiest thing in the universe) but I'll try to find a nicer way. What is important is to know that the add-in - except for the call to Initialize and Uninitialize - only lasts as long as its Launch method. You may think that it's loaded and unloaded with Media Center, but that's not true. So say goodbye to timers and any other asynchronous method, and play it simple. (I'm not suggesting that you can't use any async methods, but remember that when the Launch method returns, the process will be terminated)

The NotifyIfErrors method is not realy difficult to write :

        public void NotifyIfErrors()
        {
            int i = GetUnhandledEventCount();
            if ((i > 0 && (DateTime.Now - _dtLastEvent).TotalHours > 1) 
                 || i > _lastCount)
            {
                _lastCount = i;
                _dtLastEvent = DateTime.Now;

                string imagePath = Path.GetTempPath() 
                      + Guid.NewGuid().ToString("N") + ".png";
                Bitmap b = new Bitmap(1, 1);
                b.Save(imagePath, ImageFormat.Png);

                _host.MediaCenterEnvironment.DialogNotification(
                    string.Format("{0} Evenements à traiter", lastCount),
                    new object[] { "Afficher le detail" },
                    30, imagePath,
                    new DialogClosedCallback(LaunchDialog));
            }
        }

I won't bother you with the details of the GetUnhandledEventCount, which is pretty straightforward and makes web-service-calls. If there's more issues than the last time the add-in checked or if there is more than an hour since the last notification, a new one will be displayed.

The DialogNotification method requires an image, so for the first version of this add-in, I generate an empty picture (when I said, that this was quick & dirty !). The second and last parameters of this method are important : one for specifying the buttons on the notification bar and the other to add a callback method ofr button-press handling.

        private void LaunchDialog(DialogResult dr)
        {
           // TBD
        }

Before we can launch the XBAP, we'll need to put together an xml-file to register the application.

This file contains an application definition and two entry points :

• One for the notification add-in (placed in the Background category)
• The other for the XBAP, registred in "More Program / Tasks"

<application 
  title="MyAddin" 
  id="{EA231D54-38FE-4f8e-969F-077167EDB442}">
  <entrypoint 
     id="{78eb8987-7454-40fe-b7cb-bcc338d13587}" 
     addin="MyAddinPrj.MyAddIn, MyAddinPrj"
              title="..."
              description="..."
              ImageUrl=".\AppIcon.png">
    <category category="Background"/>
  </entrypoint>

  <entrypoint 
  id="{4E6CDC30-7F35-4b48-AE4C-82F0234ECC24}"
  url="http://maintenance/admindeploy/admin.xbap"
  title="Issue Tracking"
  imageUrl="http://maintenance/admindeploy/admin.png">
    <category category="Settings\Tasks" />
  </entrypoint>
</application>

With the informations contained in this registration xml, the parameters to launch the XBAP - the application and entry point guids - are now known.

        private void LaunchDialog(DialogResult dr)
        {
            if (dr != DialogResult.Cancel)
            {
                _host.MediaCenterEnvironment.LaunchEntryPoint(
                    new Guid("{EA231D54-38FE-4f8e-969F-077167EDB442}"),
                    new Guid("{4E6CDC30-7F35-4b48-AE4C-82F0234ECC24}"));
            }
        }

Putting it all together

Deploying this mini-project is not that simple... Due to security concerns - which don't allow a browser application to connect to any server but the one that host its files -, the XBAP goes to my maintenance server. For the MCPL add-in there are two valid destinations : the GAC or the \windows\ehome directory. I'm more in favor of the second, but if you're building a MSI to deploy your project, the GAC is certainly easier.

Visualiser les commentaires(RSS) ou en Ajouter