duplicate sql databases in bulk

This method can be used to create backups and restore databases (with a new name). This is usefull when you need to quickly setup a test version of multiple databases or in case you want to intigrate it as a build step during development. I’ll be using it to setup copies of sitecore databases before upgrading to a new version.

First make sure the logical file names of your databases are the same as the database name. For instance the Sitecore_Analytics database should have logical name Sitecore_Analytics for the mdf file and logical name Sitecore_Analytics_log for the ldf file. This allows the stored procedure to find the files to copy.

Check the logical filenames in a backup:

1
restore filelistonly from disk = 'D:/Data/backup/Sitecore_Analytics.bak'

Stored procedure

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE DuplicateDatabase
@sourceDb nvarchar(50),
@removeChars nvarchar(45),
@postfix nvarchar(5),
@backupPath nvarchar(400),
@sqlServerDbFolder nvarchar(100)
AS
BEGIN
DECLARE @sourceDb_log nvarchar(50);

DECLARE @destDb nvarchar(50);
DECLARE @destMdf nvarchar(100);
DECLARE @destLdf nvarchar(100);

SET @sourceDb_log = Replace(@sourceDb, @removeChars,'') + '_log'
SET @backupPath = @backupPath + @sourceDb + '.bak' --ATTENTION: file must already exist and SQL Server must have access to it

SET @destDb = @sourceDb + @postfix
SET @destMdf = @sqlServerDbFolder + @destDb + '.mdf'
SET @destLdf = @sqlServerDbFolder + @destDb + '_log' + '.ldf'

BACKUP DATABASE @sourceDb TO DISK = @backupPath

RESTORE DATABASE @destDb FROM DISK = @backupPath
WITH REPLACE,
MOVE @sourceDb TO @destMdf,
MOVE @sourceDb_log TO @destLdf
END
GO

Execute stored procedure for each DB

1
2
3
4
5
6
exec DuplicateDatabase 
'Sitecore_Analytics' -- db name
,'_V6.5' -- chars to remove from original db name
,'_V6.6' -- postfix for new db name
,'D:\data\backup\' -- backup location
,'D:\data\' -- new db files location

You can use the second parameter to remove characters from the original database name. This allows you to copy a xxx_V6.6 database as a xxx_V6.7 database. To create the next version all you need to to is replace the postfix and the database names. Since this may take a few minutes per DB, I would recommended to execute this one DB at a time to avoid any timeouts and more easily detect errors.

The Sitecore Upgrade 6.5 to 8.2 - part 1

I will take the ring Sitecore to Mordor 8.2 – though I do not know the way.

My newest project consists of taking an outdated Sitecore 6.5 project, dust it off and prepare it for the future as a Sitecore 8.2 project. Since a complete rebuild is out of the question I’m left with two options; migrate or upgrade. In case the analytics data can be dropped, Sitecore recommends migration. This would mean creating a clean 8.2 solution and bringing in the code, items, config and other good stuff.

The other, longer, way is to upgrade. Since the Express Migration Tool only supports projects in version 7.2 and up we’ll have to upgade manually. At least until we reach 7.2 and can use the tool.

Below you may find my preliminary plan to tackle such an upgrade. The first part consists mainly of mapping out the current situation and determining risks and breaking changes in between versions.

Upgrade preparation plan

  • Identify state of all environments (which version/branch of the solution is deployed where
  • If possible deploy latest version everywhere (preferably done with original dev)
  • Identify custom code and which parts of sitecore it plugs in to (together with original dev)
  • Identify configuration coupling: which files need to be changed and which can be used as a whole (together with original dev)
  • Set up a (manual) test plan for smoke testing site in each intermediate version
  • List deprecated functionalities of each intermediate version to determine code to be modified
  • List modules that need to be upgraded/replaced
  • List prerequisites for each intermediate version
  • Choose appropriate windows and SQL server version
  • Run a link checker before any new upgrade to check for any extra problems.
  • Decide which errors can be ignored (but should be documented) until reaching the final version
  • Discuss upgrade strategy with Sitecore (sharing as much info as possible from previous steps), they might have a few tricks up their sleave for this.

High level upgrade strategy

  • Stop development
  • Clear publishing and event queues. set longer timeouts in web.config
  • Shrink and rebuild db indexes
  • Update all environments with production content (take care not to loose not deployed development)
  • Upgrade to version x
  • Backup analysis and upgrade reports and logs
  • Test, compare to x-1 and backup version x
  • Deploy version x to staging or whatever temporary QA site
  • Have key users verify version x on staging or whatever temporary QA site
  • Upgrade to version x+1
  • … repeat until version 7.2 (or whichever version is supported by then)
  • Use Express Migration Tool
  • If possible destroy webforms and replace with MVC

This is only the beginning, a lot of choices have to made and discussed with the client. stay tuned for the next exciting episode of The Sitecore Upgrade

adding a country to existing uCommerce catalog

After creating two different catalogs for two different countries (US and Italy) in our uCommerce solution, I needed to add another country. This time however the Italy store and catalog are changed (manually) into an EMEA store. Therefore the new country is added to this EMEA store, in my case, using the same payment method and currency.

Basically you can use the stored procedure for creating a new store and remove the things you do not need. You will need to change the parameters somewhat to link the new country to the payment method (and perhaps other relations).

Here’s the stored procedure that let’s you do that.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ruben Verschueren
-- Create date: 2016/07/28
-- Description: creates a new uCommerce country store
-- and adds it to an existing store
-- =============================================
CREATE PROCEDURE AddCountryToStore
@createdBy nvarchar(20),
@currencyIsoCode nvarchar(4),
@currenctExchangeRate int,
@countryName nvarchar(25),
@vatRate decimal(5,2),
@priceGroupDescription nvarchar(250),
@cultureCode nvarchar(6),
@paymentMethodId int,
@feePercent decimal(18,4),
@shippingMethodName nvarchar(128),
@productCatalogId int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @currencyId int
DECLARE @pricegroupId int
DECLARE @countryId int
DECLARE @shippingMethodId int
DECLARE @definitionTypeId int
DECLARE @emailProfileId int
DECLARE @orderNumberId int
DECLARE @productCatalogGroupId int
DECLARE @ProductCatalogPriceGroupRelationId int

SELECT @definitionTypeId = definitionTypeId
FROM uCommerce_DefinitionType
WHERE name = 'PaymentMethod'

SELECT @emailProfileId = emailprofileid
FROM uCommerce_EmailProfile
WHERE name ='Default'

SELECT @orderNumberId = ordernumberid
FROM uCommerce_OrderNumberSerie
WHERE OrderNumberName = 'Default'

-- create currency
SELECT @currencyId = currencyid
FROM uCommerce_Currency
WHERE isocode = @currencyIsoCode AND deleted = 0

IF (@currencyId is not null)
BEGIN
PRINT 'currency already exists'
END
ELSE
BEGIN
INSERT uCommerce_Currency(isocode,exchangerate,deleted,guid)
VALUES (@currencyIsoCode, @currenctExchangeRate, 0, NEWID())
SELECT @currencyId = SCOPE_IDENTITY()
END

-- create pricegroup
SELECT @pricegroupId = pricegroupid
FROM uCommerce_PriceGroup
WHERE name = @countryName AND currencyId = @currencyId

IF (@pricegroupId is not null)
BEGIN
PRINT 'pricegroup already exists'
UPDATE uCommerce_PriceGroup
SET deleted = 0 WHERE name = @countryName AND currencyId = @currencyId
END
ELSE
BEGIN
INSERT uCommerce_PriceGroup(name,currencyid,vatrate,description,
createdon,createdby,modifiedon,modifiedby,deleted,guid)
VALUES (@countryName ,@currencyId,@vatRate, @priceGroupDescription,getdate(),
@createdBy,getdate(),@createdBy,0,NEWID())
SELECT @pricegroupId = SCOPE_IDENTITY()
END

-- create country
SELECT @countryId = countryid
FROM uCommerce_Country
WHERE name = @countryName AND Culture = @cultureCode
IF (@countryId is not null)
BEGIN
PRINT 'country already exists'
UPDATE uCommerce_Country
SET deleted = 0
WHERE name = @countryName
AND Culture = @cultureCode
END
ELSE
BEGIN
INSERT uCommerce_Country(name,culture,deleted)
VALUES (@countryName, @cultureCode, 0)
SELECT @countryId = SCOPE_IDENTITY()
END


-- needed?
SELECT @shippingMethodId = ShippingMethodId
FROM uCommerce_ShippingMethod
WHERE name = @shippingMethodName AND paymentmethodId = @paymentMethodId
IF (@shippingMethodId is not null)
BEGIN
PRINT 'shipping method already exists'
END
ELSE
BEGIN
INSERT uCommerce_ShippingMethod(name,PaymentMethodId,ServiceName,deleted)
VALUES (@shippingMethodName, @paymentMethodId, 'SinglePriceService', 0)
SELECT @shippingMethodId = SCOPE_IDENTITY()
END

--map shipping method to country
IF((SELECT count(*)
FROM uCommerce_ShippingMethodCountry
WHERE CountryId = @countryId and shippingMethodId = @shippingMethodId) = 0)
BEGIN
INSERT uCommerce_ShippingMethodCountry(CountryId,shippingMethodId)
VALUES(@CountryId,@shippingMethodId)
END

-- create ProductCatalog pricegroup relation
SELECT @ProductCatalogPriceGroupRelationId = ProductCatalogPriceGroupRelationId
FROM uCommerce_ProductCatalogPriceGroupRelation
WHERE ProductCatalogId = @productCatalogId and PriceGroupId = @pricegroupId
IF (@ProductCatalogPriceGroupRelationId is not null)
BEGIN
PRINT 'ProductCatalog is already linked to pricegroup'
END
ELSE
BEGIN
INSERT uCommerce_ProductCatalogPriceGroupRelation
(productcatalogid,PriceGroupId,SortOrder)
VALUES (@productCatalogId,@priceGroupId,0)
SELECT @ProductCatalogPriceGroupRelationId = SCOPE_IDENTITY()
END
END

Now that we created the stored procedure, we can execute it as many times as we want adding more countries to our store. Obviously you need to change the values of the parameters especially the PaymentMethodId and productCatalogId. In the uCommerce backend you will now see the new country, pricegroup etc. If you open up the catalog, you will now see the new country as an option in the allowed priceGroups. Just enable the checkbox and you’re good to go… after you add all the prices on the products that is.

1
2
3
4
5
6
7
8
9
10
11
12
EXEC AddCountryToStore 
'Ruben' --createdBy
,'EUR' --CurrencyIsoCode
,100 --currentExchangeRate
,'Netherlands' --countryName
,0.21 --vatRate
,'Pricing for NL eCommerce' --priceGroupDescription
,'nl-NL' --cultureCode
,8 --PaymentMethodId
,0.0000 --feePerentage
,'MyShippingMethod' --shippingmethodName
,5 --productCatalogId