October 24, 2016 at 1:25 am
do I have to install backward compatibility after installing SQL server 2014.
And what is backward compatibility used for.
And Can I restore databases back to a 2008 instance from 2014 , even if backward compatibility is not installed.
The only backward compatibility installation I have seen is from 2008 to 2005.
October 24, 2016 at 2:31 am
sqlserverDBA2016 (10/24/2016)
do I have to install backward compatibility after installing SQL server 2014.
I guess that it depends, without further information it is impossible to tell.
And what is backward compatibility used for.
And Can I restore databases back to a 2008 instance from 2014 , even if backward compatibility is not installed.
The only backward compatibility installation I have seen is from 2008 to 2005.
No, you cannot restore to an earlier version.
😎
October 24, 2016 at 2:40 am
sqlserverDBA2016 (10/24/2016)
do I have to install backward compatibility after installing SQL server 2014.And what is backward compatibility used for.
And Can I restore databases back to a 2008 instance from 2014 , even if backward compatibility is not installed.
The only backward compatibility installation I have seen is from 2008 to 2005.
Within a database you can set the compatibility level. This controls what commands are available and how certain commands behave within that database. For example, on a SQL 2014 instance you could set a database to 'behave' like a 2008 R2 database by setting the compatibility level to 105.
https://msdn.microsoft.com/en-us/library/bb510680.aspx
Once a database has been opened on a particular version of a server it cannot be opened on an earlier version of sql server. So you can have a database on a 2014 instance, set to a compatibility level for 2008 R2 but you cannot restore that database on 2008 R2. Compatibility level alters the behaviour, not the schema.
You can open a 2008 R2 database on a 2014 sql server but in doing that certain properties within the database are altered and it now cannot be opened again on a 2008 R2 server.
The only way to move a database from a higher version of sql server to a lower version is to script the database and run the scripts on the lower server, then move the data across.
October 24, 2016 at 2:46 am
I need to know whether I need to install backward compatibility for 2014 or not
October 24, 2016 at 3:01 am
sqlserverDBA2016 (10/24/2016)
I need to know whether I need to install backward compatibility for 2014 or not
Can you please elaborate further on this, what makes you think you might need any backward compatibility? Are you connecting from that server to servers with earlier versions of SQL Server? Are there earlier version SQL Servers connecting to the new instance? Are there applications in the mix? Reporting Services? Integration Services? Anything else worth mentioning?
😎
October 24, 2016 at 3:09 am
Thanks Guys
The reason is i'm asking cos ive seen it in one of the documents for a migration between 2005 and 2008 (by means of installing 2008 and then exporting DB).In the doco it says that an installation for backward compatibility to 2005 is required after installing 2008 sql server.
I have installed sql server instances and never had to install another software for backward compatibility.
can you please explain whether this is needed or not when installing 2014.
October 24, 2016 at 3:13 am
The applications will be in eventually using analysis services and integration services and reporting services.
but i dont think thwould make any difference.
Basically i think that migrating to a new server only requires an installation of the vopy ersion which is here 2014 and just and export import of the DBs and copy of the jobs and logins , ive never heard of a backward compatibility download until i saw it in this stupid document.
October 24, 2016 at 3:18 am
Eirikur Eiriksson (10/24/2016)
sqlserverDBA2016 (10/24/2016)
I need to know whether I need to install backward compatibility for 2014 or notCan you please elaborate further on this, what makes you think you might need any backward compatibility? Are you connecting from that server to servers with earlier versions of SQL Server? Are there earlier version SQL Servers connecting to the new instance? Are there applications in the mix? Reporting Services? Integration Services? Anything else worth mentioning?
😎
I haven't had any backward compatibility issues on any of my SQL Server 2014 or 2016 instances, haven't even checked if such backward compatibility packs exist.
😎
For the 2005 to 2008 migration, the backward compatibility pack is not necessary unless you are connecting from 2008 to the 2005. Upgrading by either detach/attach or backup/restore do not require the backward compatibility pack.
October 24, 2016 at 3:27 am
thanks mate
Does that mean that for me to migrate to sql server 2014, i just need to install 2014 sql server and export the dbs only .
I will be moving all my dbs to 2014.
does that mean there is no need to install any packs after the sql server 2014 installation?
October 24, 2016 at 3:43 am
sqlserverDBA2016 (10/24/2016)
thanks mateDoes that mean that for me to migrate to sql server 2014, i just need to install 2014 sql server and export the dbs only .
I will be moving all my dbs to 2014.
does that mean there is no need to install any packs after the sql server 2014 installation?
That is correct, no need for any additional installations.
😎
For a side by side server upgrade I normally do the following:
1) Script all login (transferable) on the source server.
2) Script files and file locations for all databases (use it to generate both attach and detach scripts)
3) Run the detach scripts
4) Copy the files to the new server
5) Run the attach scripts on the new server
6) Run the transferable login scripts on the new server
7) Run a full backup of the new server
8) Set the compatibility of the databases to match the source (earlier version)
9) Rebuild all statistics etc.
10) test,test,test
October 24, 2016 at 3:47 am
Its not a side by side upgrade and a detach attach is risky because the files can get corrupted.
I'm moving like i said from 2008 to 2014.
So i need to change compatibility to sql server 2014.
I know about the rest but I just need to confirm the backwards compatibility pack i need to download , which i dont think i do
October 24, 2016 at 3:54 am
i'm moving to a higher version which is 2014
and detaching and attaching can risk corrupting the files
export and import is safer.
so i'm just asking whether i need to install any backward compatibility packs after the SQL server 2014 installation
I don't think i do, but the only reason why im asking cos i saw it in a 2005 to 2008 migration document.
October 24, 2016 at 4:17 am
sqlserverDBA2016 (10/24/2016)
detaching and attaching can risk corrupting the files
Done this hundreds if not thousands of times on almost all versions of SQL Server ever since the dinosaurs turned into oil, haven't seen a single file corruption in the process yet.
😎
Are you thinking of using the SSMS Copy DB or Import Wizard?
October 24, 2016 at 4:28 am
i'm gonna be using ssms to run the export and import.
but i'm just asking if whether after the installation of sql server 2014 , is there a backward compatibility software which needs to be installed.
October 24, 2016 at 4:43 am
sqlserverDBA2016 (10/24/2016)
i'm gonna be using ssms to run the export and import.but i'm just asking if whether after the installation of sql server 2014 , is there a backward compatibility software which needs to be installed.
There is no backwards compatibility pack beyond 2008.
"Microsoft SQL Server 2005 Backward Compatibility Components
The SQL Server Backward Compatibility package includes the latest versions of the Data Transformation Services 2000 runtime (DTS), SQL Distributed Management Objects (SQL-DMO), Decision Support Objects (DSO), and SQL Virtual Device Interface (SQLVDI). These versions have been updated for compatibility with both SQL Server 2005 and SQL Server 2008 and include all fixes shipped through SQL Server 2000 Service Pack 4 (SP4) and SQL Server 2005 SP2."
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply