April 25, 2018 at 3:46 am
Apologies if I'm asking in the wrong place and let me firstly say I know next to nothing about SQL server!
My situation is this: I have a transport system running on an old 2003 SBS - this sever is pretty much on its last legs and the business has no budget for replacing the server. Our software provider wants to charge us 10k to move to the lastest and greatest version of the software but its an outlay we just cannot justify. The current software whilst old works perfectly well for us. I have purchased a new desktop computer and installed SQL server Express 2012 and now need to move all the databases from the old server to the new one. I was hoping, very naively, that I would just be able to backup the databases and restore them in the new software, change the local machine ODBC's to point to the new server and everything would work. Obviously that hasn't happened and there is much more work involved but I am now out of my depth.
So I guess my question is - is there an idiots guide that can guide me through the process? Any assistance/guidance would be greatly appreciated.
April 25, 2018 at 4:11 am
Copying the databases using the SQL backup and restore method should work from SQL2005 to SQL2012. I expect the only thing to take into account during the restore is the location of the datafiles. That can be specified in the restore command. A basic restore command looks like:RESTORE DATABASE my_database FROM DISK = 'Z:\Backup\my_database.bak'
WITH FILE = 1, REPLACE
, MOVE 'my_database_datafile' TO 'D:\database\my_database.mdf'
, MOVE 'my_database_log' TO 'D:\database\my_database_log.ldf'
When migrating to the Express version of SQL take into account the restrictions of the version. A database within SQL2012 Express is limited to 10GB. Other limitations of the Express version can be found here.
When migrating a database to a higher version I always have used the steps below. This will set and update the database to the current compatibility level of the server. Changing the compatibility also implies depricated features could stop working. So please test your application and check your code if it uses such depricated features.
/********************************************************************/
-- commands to upgrade the compatibility level of a database
-- to the current SQL instance level
--
/********************************************************************/
--==========================================================================================
--
-- change the text "databasename" in the code below to the actual database name
--
--==========================================================================================
-- step 1: change recovery level and page_verify setting
ALTER DATABASE [databasename] SET RECOVERY SIMPLE, PAGE_VERIFY CHECKSUM
-- step 2: change compatability level to the level of the instance itself (taken from [master])
USE [databasename]
DECLARE @cmpt_level INT
SELECT @cmpt_level = compatibility_level FROM sys.databases WHERE name = 'master'
EXEC dbo.sp_dbcmptlevel @dbname=N'databasename', @new_cmptlevel=@cmpt_level
-- step 3: remove existing statistics
USE [databasename]
DECLARE @ObjectName sysname
DECLARE @StatsName sysname
DECLARE StatsCursor CURSOR FAST_FORWARD
FOR
SELECT
'['+OBJECT_NAME(object_id)+']' as 'ObjectName',
'['+[name]+']' as 'StatsName'
FROM sys.stats
WHERE
(INDEXPROPERTY(object_id, [name], 'IsAutoStatistics') = 1
OR INDEXPROPERTY(object_id, [name], 'IsStatistics') = 1)
AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
OPEN StatsCursor
FETCH NEXT FROM StatsCursor
INTO @ObjectName, @StatsName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('DROP STATISTICS ' + @ObjectName + '.' + @StatsName)
FETCH NEXT FROM StatsCursor
INTO @ObjectName, @StatsName
END
CLOSE StatsCursor
DEALLOCATE StatsCursor
-- step 4: update usage information in the database
DBCC UPDATEUSAGE ('databasename') WITH NO_INFOMSGS, COUNT_ROWS
-- step 5: correct settings regarding STATISTICS
ALTER DATABASE [databasename] SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE [databasename] SET AUTO_UPDATE_STATISTICS ON
-- step 6: list and map users to corresponding logins
exec sp_change_users_login 'report'
/*
-- use the command below to correct listed issues
exec sp_change_users_login 'auto_fix', '{reported_user}'
*/
-- step 7: check consistentie of the users with the logins
-- (in case the loginnaam is empty, the mapping isn't correct)
exec sp_helpuser
/*
-- remove any unwanted mapping between user and login and regrant the desired permissions
DROP USER [username]
exec sp_dropalias 'username'
*/
-- step 8 (optional): change the recovery model to FULL
-- REMARK: create a new full backup after executing the step below
ALTER DATABASE [databasename] SET RECOVERY FULL
-- step 9 (optional): change the database owner (alter the name "sa" below to the desired accountname)
alter authorization on database::[databasename] TO [sa]
April 30, 2018 at 11:02 am
One other thing to take into consideration is that I'm not sure that SQL Server 2012 Express Edition is going to be properly licensed if you have more than one user. When SBS was purchased, it included a SQL Server license for some number of users, and I'm doubtful you'd be in compliance with MSFT software licensing by using an Express version of SQL Server. Not sure a sizable cost is avoidable, even if you don't use the vendor to acquire it, and go directly to MSFT instead.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 30, 2018 at 12:26 pm
sgmunson - Monday, April 30, 2018 11:02 AMOne other thing to take into consideration is that I'm not sure that SQL Server 2012 Express Edition is going to be properly licensed if you have more than one user. When SBS was purchased, it included a SQL Server license for some number of users, and I'm doubtful you'd be in compliance with MSFT software licensing by using an Express version of SQL Server. Not sure a sizable cost is avoidable, even if you don't use the vendor to acquire it, and go directly to MSFT instead.
It's not limited to 1 user though. Express doesn't have the same limitations as msde did. In theory both have always had the same limitation as all editions which is 32,767.
MSDE had that workload governor that would fire based on 5 concurrent operations (or workload of 5, something like that) but they got rid of that and the limitations are in hardware, database size.
Sue
April 30, 2018 at 2:39 pm
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/cc645993(v%3dsql.110)
1 socket or 4 cores
1GB ram
10GB db size.
Make sure those limits work for you.
May 1, 2018 at 7:15 am
Also think about how you will schedule your native backups, if required.
May 1, 2018 at 7:28 am
There are some additions to Express and always the Windows scheduler, but good point, Beatrix
May 1, 2018 at 2:37 pm
Sue_H - Monday, April 30, 2018 12:26 PMsgmunson - Monday, April 30, 2018 11:02 AMOne other thing to take into consideration is that I'm not sure that SQL Server 2012 Express Edition is going to be properly licensed if you have more than one user. When SBS was purchased, it included a SQL Server license for some number of users, and I'm doubtful you'd be in compliance with MSFT software licensing by using an Express version of SQL Server. Not sure a sizable cost is avoidable, even if you don't use the vendor to acquire it, and go directly to MSFT instead.It's not limited to 1 user though. Express doesn't have the same limitations as msde did. In theory both have always had the same limitation as all editions which is 32,767.
MSDE had that workload governor that would fire based on 5 concurrent operations (or workload of 5, something like that) but they got rid of that and the limitations are in hardware, database size.Sue
Oh... for whatever reason, I thought Express was a single-user thing just like Developer Edition, from a licensing perspective where keeping it free or low cost is concerned. My bad...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply