DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
The Latest "Software Integration: The Intersection of APIs, Microservices, and Cloud-Based Systems" Trend Report
Get the report
  1. DZone
  2. Data Engineering
  3. Databases
  4. SQL Clone on Your Laptop

SQL Clone on Your Laptop

Learn how to SQL clone on your laptop.

Phil Factor user avatar by
Phil Factor
·
May. 03, 19 · Tutorial
Like (2)
Save
Tweet
Share
4.85K Views

Join the DZone community and get the full member experience.

Join For Free

Phil Factor provides a PowerShell script to disconnect your laptop without risking error 21 if you're working with SQL Clone and need to go offline. The same script will then bring the clone database back online smoothly, once you're reconnected.

For some reason, the idea of running SQL Clone on a laptop seems odd to me. Why would you use a tool like this on a partially-disconnected device when it is so dependent on specific local network services? Why use a laptop at all for database development when a relational database is, by its very nature, a multi-user system?

The answer is that many people are doing development work on a laptop at work and including the database. They use SQL Clone because means they can have a behemoth of a database on their laptop without any fear of running out of space, and one they can easily and quickly refresh when doing tests.

They then want to take their laptop home, sometimes working with it offline or on an unreliable connection. This article is about how you can do this without upsetting SQL Clone or SQL Server.

The Problem

SQL Clone uses disk-based virtualization. This requires, and assumes, a permanent network link to the source file that represents the VHD. If the image becomes inaccessible, both SQL Clone and SQL Server will react to this 'disturbance in the force.' What happens depends on which one gets there first. If SQL Clone reacts first, it will automatically detach the clone database. If SQL Server reacts first and tries to access what it thinks is the local hard disk, but is, in fact, the image (parent VHD), the disk driver gives back error 21 (the device is not ready). It will then assume, reasonably, that your hardware is failing, so it then locks down the database and won't touch it. The Clone agent will keep trying to resolve the problem and keep failing because SQL Server won't let it. Once you hit error 21, you'll need to restart the SQL Server service.

Disconnecting

If for any reason you wish to disconnect a device temporarily from a SQL Clone network, you need to stop the SQL Clone Agent service on the machine and then take the database offline; it's that simple. You may wish to copy your SQL Clone database locally first via conventional backup followed by restore under a different name if you want to continue to work on it during a flight or whatever. The clone itself must remain offline because it no longer really exists whilst bereft of its associated image file. The data is no longer there.

Naturally, you can do this manually. You first stop the SQL Clone Agent service from the services.msc plug-in (Control Panel | System and Security | Administrative Tools).

Right-click on the SQL Clone agent and click on the 'stop' item in the context menu that appears. Then you need to use SSMS to log into your local SQL Server instance (well, every local instance if you are using your laptop to the max) and execute this code in Master database.

USE [master]
  IF OBJECT_ID('tempdb..#MyExtendedProperties') IS NOT NULL DROP TABLE #MyExtendedProperties
  CREATE TABLE #MyExtendedProperties(TheDatabase sysname)
  Declare @command NVARCHAR(4000)= '
  use ?
  insert into #MyExtendedProperties (TheDatabase)
  SELECT DB_NAME()
   FROM sys.extended_properties ep where class=0 
   and ep.name=''IsSQLCloneDatabase'' --and Value=1'
  EXECUTE sp_msforeachdb @command
  DECLARE @SetEmOffline NVARCHAR(4000)=''
  SELECT @SetEmOffline=@SetEmOffline+'
  ALTER DATABASE ['+TheDatabase+'] SET  OFFLINE WITH
  ROLLBACK IMMEDIATE;
  '
   FROM #MyExtendedProperties Clones
   INNER JOIN 
   sys.databases D  ON d.name=Clones.TheDatabase
   WHERE state_desc = 'online'
   EXECUTE  (@SetEmOffline)
  GO

Now you can snap the clamshell shut and run for the train. Once your laptop is undocked from the network, that clone database is out of bounds. You can see it in SSMS marked as 'offline.' Just don't try setting it online while the Clone agent is stopped.

Connecting

Once you get back to the office, the first thing you do is to run this:

DECLARE @SetEmOnline NVARCHAR(4000)=''
  SELECT @SetEmOnline=@SetEmOffline+'
  ALTER DATABASE ['+TheDatabase+'] SET  ONLINE;
  GO
  '
   FROM  
   sys.databases D  
   WHERE state_desc = 'offline'
   EXECUTE  @SetEmOnline
  GO

Note that we can't check the extended property that says that the database is a clone because once the database is taken offline, the extended property is taken offline as well. We just assume that all databases that are offline are clones. This is unsafe, but the only way left to us without querying SQL Clone GUI or by using PowerShell to identify the databases on this server that are clones. If you're not a risk-taker, then you can check on SQL Clone's GUI for confirmation and create the SQL accordingly.

Then we can restart the Clone agent service. This is just like the process I've already described, except that you need to click on the 'start' menu item.

At this point, you will probably be shaking your head and wondering if there is a better way. Fortunately, there is. We can use PowerShell.

A Scripted Approach

Only one script is required since the disconnect procedure is similar to the connect procedure. It must be executed when running PowerShell as administrator; this is stopping a service after all, which is not something you'd want to do accidentally. It can be executed within an ordinary script, but the start-service and stop-service cmdlets require execution as administrator.

One script is used for both in this design. You can turn it into a function and call it from Cmdlets you create, called something like Start-LocalClone and Stop-Localclone. The $disconnecting variable must be $true if you are disconnecting and $false if you are connecting. You might, alternatively, just want to execute two separate scripts, each permanently set to do the one job.

$Disconnecting = $false;
  $ServerInstance = 'Philf01' #'Localhost' # server name and instance
  $SQLUserName = 'PhilFactor' #leave blank if Windows auth
  set-psdebug -strict # to catch subtle errors
  $ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
  # Load sqlserver module
  $popVerbosity = $VerbosePreference #remember current verbosity setting
  $VerbosePreference = "Silentlycontinue"
  # the import process is very noisy if you are in verbose mode
  # now whatever we do we stop the service first
  Import-Module sqlserver -DisableNameChecking #load the SQLPS functionality
  Get-Service |
    Where { $_.Name -like 'SqlCloneAgent*' -and $_.status –eq 'running' } |
      Stop-Service 
  #get our credentials
  $VerbosePreference = $popVerbosity
  # get credentials if necessary
  if ($SQLUserName -ne '') #then it is using SQL Server Credentials
  {
      $SqlEncryptedPasswordFile = `
      "$env:USERPROFILE\$($SqlUserName)-$($SQLInstance).txt"
      # test to see if we know about the password in a secure string stored in the user area
      if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf)
      {
          #has already got this set for this login so fetch it
          $Sqlencrypted = Get-Content $SqlEncryptedPasswordFile | ConvertTo-SecureString
          $SqlCredentials = `
          New-Object System.Management.Automation.PsCredential($SqlUserName, $Sqlencrypted)
      }
      else #then we have to ask the user for it
      {
          #hasn't got this set for this login
          $SqlCredentials = get-credential -Credential $SqlUserName
          $SqlCredentials.Password | ConvertFrom-SecureString |
          Set-Content $SqlEncryptedPasswordFile
      }
  }
  #now start up SMO
  $ms = 'Microsoft.SqlServer'
  $My = "$ms.Management.Smo" #
  if ($SQLUserName -eq '') #dead simple if using windows security
  { $s = new-object ("$My.Server") $ServerInstance }
  else # if using sql server security we do it via a connection object
  {
      $ServerConnection = new-object "$ms.Management.Common.ServerConnection" (
          $ServerInstance, $SQLUsername, $SqlCredentials.Password)
      $s = new-object ("$My.Server") $ServerConnection
  }
  If ($Disconnecting -eq $true)
  {
      $ClonesByName = $s.Databases |
        Select ExtendedProperties |
          Foreach{ $_.ExtendedProperties } |
            Where name -eq 'IsSQLCloneDatabase' |
              Select parent
      $ClonesByName.parent.name |
        foreach {
          Write-Verbose "now taking  $($_) offline"
          $TheDatabase = $s.Databases[$_]
          $TheDatabase.SetOffline()
      }
  }
  else
  {
      #if we are connecting the clones
      $s.Databases | where { $_.Status -ne 'Normal' } | foreach{
          $_.SetOnline()
          Get-Service | # make certain that the clones are running
            Where { $_.Name -like 'SqlCloneAgent*' -and $_.status –eq 'stopped' } |
              Start-Service
      }
  }

Summary

I don't think that the Redgate team who developed SQL Clone imagined that it would be useful to have a clone on a partially-connected device, such as a laptop, but plenty of people try it. The trouble, of course, is disconnecting the clone from its image in a way that doesn't upset the system. I guess one day there will be an elegant way of doing this, using the GUI or maybe a special cmdlet. In the meantime, this is a fix that does the job.

One thing is clear, though; it is going to be tricky to devise a cloning system that is geared to mobile and partially-disconnected devices until internet speeds get close to local network speeds and until MS Windows supports such a network for Virtual hard disks. In the meantime, you can take the laptop anywhere, but that cloned database must stay offline until the laptop is back on your work network!

Clone (Java method) sql

Published at DZone with permission of Phil Factor. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Getting a Private SSL Certificate Free of Cost
  • A Gentle Introduction to Kubernetes
  • Asynchronous Messaging Service
  • What Is Advertised Kafka Address?

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: