: How to update correctly SQL Server
What if I asked, "Are all the updates installed on your SQL Server?"
How to answer such a question? Look into the center Windows updates? This is unlikely to help. Most likely you will see something like this:
But, oddly enough, this does not mean that SQL Server has the latest updates installed!
To track updates specifically for the SQL server, Microsoft has created a special page - . Bookmark it:
But before going there, we need to find out which version we have installed right now. We need an exact number, including the build number. You can get it by looking at the system variable @@Version:
Remember these numbers - only by them will we be able to determine which updates we already have installed and which ones we do not. SQL server, unlike operating system he himself does not tell us about each installed update. Therefore, you will have to work with the number @@Version.
Now we go to the SQL server update center and see a convenient sign there:
Here we need three things:
- First, let's remember the build number corresponding to this update. After completing the update procedure @@Version our SQL server should give exactly this number.
- Secondly, you should definitely look at the history of build numbers corresponding to updates from the latest service package. This is important because the cumulative update will only install on the service pack that precedes it.
- And third, it is useful to look at the list of fixes contained in the rollup package. The administrator must be aware of the changes introduced by this package.
Looking at the build numbers of previous updates, we will see that the gap in numbers compared to ours current version suspiciously large:
This is due to the fact that we do not have SP1 installed. You'll have to install it first.
And now we have an action plan:
- Install service package SP1.
- Install the cumulative update package CU4.
Let's act! After installing SP1, check the number @@Version. From the original 2100.60 it increased to 3000.0.
In this article I will talk about the process of updating databases Microsoft SQL Server and suggest software, which can be used in the interaction between the customer and the contractor.
First of all, why is this necessary?
Our company is engaged in software development and in most cases we use Microsoft SQL Server as a DBMS. To be specific, we will call the development company “ performer", and the client company - " customer».In the process of making changes to the database, the developer's programmers create a set of scripts created manually or in specialized software.
Subsequently, the following problems arise:
- on the executor side, check the execution of a set of scripts in the test database;
- on the customer side, execute scripts in the client’s working database;
- he may not have SSMS installed at all;
- the customer may not have knowledge of working with the database. And by and large it shouldn’t;
- if errors occur when executing scripts, it will be difficult for the customer to assemble them for sending to developers;
How do we solve this problem?
We have developed a software product “Database Update Manager”, which consists of two programs:- program for preparing an update package;
- program for executing the update package;
Program for preparing the update
The program is used by the performer. Programmers use it to create a set of scripts into one file.Of the features of this program, two points can be noted:
- information about the structure of the reference database can be added to the generated update package. If such information is present in the package, then the customer, after completing the update, can compare the structure of his database with the reference one;
- for the customer, the update execution program is transmitted in the form of one EXE file, inside which scripts and the structure of the reference base are embedded.
Program for performing the update
As already mentioned, the program is launched using a single EXE file. In most cases, the user just needs to enter registration information (by the way, these values can be included in the update package) and click “Next” everywhere.The second screen is updated. After this, you can compare the structure of the reference database embedded in the package and the current one (on which the update was performed).
The analysis of discrepancies in the structure is carried out in graphical interface update programs.
Unfortunately, discrepancies are viewed by the performer's programmers in text file magazine, which is subsequently sent by the customer. An interesting approach to solving this problem is proposed in the article Controlling changes in the structure of the database. This task not so critical; programmers can write a script themselves to bring the structure to the target.
The software package is distributed under a Freeware license agreement, you can download the installer from the link
Microsoft® SQL Server 2005 - Express Edition free product and comes with software Parsec 2.5 and 3. Performs the function of storing all system data. The limitation of this product is the database size of 4 gigabytes. After reaching this threshold, new data stops being recorded in the system, which leads to the loss of relevance of the stored information. To solve this problem, you need to upgrade to a paid premium version of SQL Server (it has no restrictions on database size) or upgrade to Microsoft SQL Server 2008 R2 SP2 Express. SQL Server 2008 R2 SP2 Express is a free database with a database size limit of 10 gigabytes.
1. First, you need to download Microsoft® SQL Server® 2008 R2 SP2 - Express Edition from www.microsoft.com, following the link: http://www.microsoft.com/en-US/download/details.aspx?id= 30438 file size 110.4 MB
IMPORTANT: To update, you need a 32-bit English version!
2. On Windows XP, Vista, 2003, 2008, you may need software platform NET.Framework 3.5 SP1. You can download by following the link http://www.microsoft.com/ru-ru/Download/details.aspx?id=22 (Download this platform before upgrading SQL Server)
On Windows 7, 2008 R2 Server, 8, 8.1, 2012 and 2012 R2 Server. NET Framework 3.5 is Windows component and installed via Control Panel\All Control Panel Items\Programs and Features "Turn Windows features on or off"
3. Run the downloaded file.
4. Wait for extraction installation package and start installation.
5. In the installation dialog that opens, you must select the menu item "Upgrade from SQL 2000, SQL Server 2005 or SQL Server 2008 Lauch a wizard to upgrade SQL 2000, SQL Server 2005 or SQL Server 2008 to SQL Server 2008 R2"
6. In the next window you must Accept the terms license agreement- "I accept the license terms."
7. In the "Select Instance" installation window, you must select the database that you plan to update. "Instance to upgrade" (PARSEC3 - for ParsecNET 3, PARSECDB - for ParsecNET 2.5)
8. Wait for the new installation window "Error Reporting", the suggested checkbox in this menu you don't have to put it.
9. In the “Upgrade Roles” window, click “Show details”, a window will open in which you can see the process of installing components, and you can also see errors if they suddenly arise.
10. Wait until the process of installing and updating the SQL server components is completed - at the end a message will appear that the update was successful - “Your SQL Server upgrade completed successfully”.
11. You can close the installation window “Close” and exit using the cross. You have upgraded SQL Server 2005 Express to MS SQL Server 2008 R2 Express SP2 EN.
This article describes the information you need to track updates for Microsoft SQL Server.
Before upgrading, you need to find out which version is already installed. An exact number is required, including the build number. Described in more detail in the "Questions" section.
MS SQL Server
| MS SQL Server (Official builds) | ||||||
| RTM (no SP) | SP1 | SP2 | SP3 | SP4 | Note | |
|---|---|---|---|---|---|---|
| SQL Server 2017 | 14.0.1000.169 | - | - | - | - | - |
| SQL Server 2016 | 13.0.1601.5 | 13.0.4001.0 (13.1.4001.0) | 13.0.5026.0 (13.2.5026.0) | - | - | |
| SQL Server 2014 | 12.0.2000.8 | 12.0.4100.1 (12.1.4100.1) | 12.0.5000.0 (12.2.5000.0) | - | - | When working with replication, SP1 or higher is recommended. |
| SQL Server 2012 | 11.0.2100.60 | 11.0.3000.0 (11.1.3000.0) | 11.0.5058.0 (11.2.5058.0) | 11.0.6020.0 (11.3.6020.0) | 11.0.7001.0 (11.4.7001.0) | |
| SQL Server 2008 R2 | 10.50.1600.1 | 10.50.2500.0 (10.51.2500.0) | 10.50.4000.0 (10.52.4000.0) | 10.50.6000.34 (10.53.6000.34) | - | When working with replication, SP2 or higher is recommended. |
| SQL Server 2008 | 10.0.1600.22 | 10.0.2531.0 (10.1.2531.0) | 10.0.4000.0 (10.2.4000.0) | 10.0.5500.0 (10.3.5500.0) | 10.0.6000.29 (10.4.6000.29) | When working with replication, SP3 or higher is recommended. |
| SQL Server 2005 | 9.0.1399.06 | 9.0.2047 | 9.0.3042 | 9.0.4035 | 9.0.5000 | When working with replication, SP4 is recommended. |
Questions
Q: How to determine the version?
A: To determine exactly what version you have, run the request SELECT @@VERSION.
Q: Will upgrading from Express to Standard work or vice versa?
A: The updates are universal and are suitable for all editions of a specific server version.
Q: How to install updates?
A: Only the last one. For example, for SQL 2014: 12.0.2000.8 => 12.0.5000.0. The latest SP on the list contains previous service packs
Q: Will the base be destroyed?
Oh no. But the absence of a backup does not make the automation specialist a specialist!
Q: What happens to replication?
A: Nothing special. Update principle: first the central server, then the subscribers. The exchange must be finalized for all subscribers at this time. In case of 24/7 work it is necessary to ensure that work is switched to another PC (for example, to a publisher or backup subscriber).
Q: How long does it take for updates to be installed?
A: The installation time varies depending on the version. On average, the update procedure takes from 10 to 30 minutes, maybe longer.
Q: Is it possible to work with the database at this time?
A: Absolutely not.
Some Helpful Resources
- Documentation for all products;
- Microinvest technical support forum, where you can quickly get an answer to your question;
- Examples of automation of restaurants and trade based on Microinvest in Russia and the CIS countries;
- Schedule of upcoming trainings in Russia and CIS countries;
Last update: 07/13/2017
To change existing rows in a table, use the UPDATE command. It has the following formal syntax:
UPDATE table_name SET column1 = value1, column2 = value2, ... columnN = valueN
For example, let’s increase the price of all products by 5000:
UPDATE Products SET Price = Price + 5000
Let's use the criterion and change the manufacturer name from "Samsung" to "Samsung Inc.":
UPDATE Products SET Manufacturer = "Samsung Inc." WHERE Manufacturer = "Samsung"
A more complex request - replace the Manufacturer field with the value "Apple" with "Apple Inc." in the first 2 lines:
UPDATE Products SET Manufacturer = "Apple Inc." FROM (SELECT TOP 2 * FROM Products WHERE Manufacturer="Apple") AS Selected WHERE Products.Id = Selected.Id
Using a subquery after keyword FROM retrieves the first two rows containing Manufacturer="Apple". The alias Selected will be defined for this selection. The alias is specified after the AS operator.
Next comes the update condition Products.Id = Selected.Id . That is, in fact, we are dealing with two tables - Products and Selected (which is derived from Products). In Selected there are two first lines with Manufacturer="Apple". In Products - generally all lines. And the update is performed only for those rows that are in the Selected selection. That is, if there are dozens of products in the Products table with the manufacturer Apple, then the update will affect only the first two of them.
