In this article I will talk about the process of updating databases Microsoft data SQL Server and offer a software product that can be used in interaction between the customer and the contractor.
First of all, why is this necessary?
Our company is developing software 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 the graphical interface of the update program.
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 is not so critical; programmers can write a script themselves to bring the structure to the target.
The software package is distributed across license agreement Freeware, you can download the installer from the link
: How to properly update 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 cumulative update will be installed only on the service package preceding 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.
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 value "Apple" in the Manufacturer field 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 manufacturer Apple, then the update will affect only the first two of them.
In this article we will show how upgrade the trial (Evaluation Edition) or Express version of SQL Server 2008 R2 to a full working version of SQL Standard or Enterprise. You may encounter a similar problem when there is no one to test/develop software product The trial version of SQL Server is installed. It often happens that after the end of such testing/the end of the evaluation period, a decision is made to transfer the system into production operation, for which it is necessary to switch to a full-featured edition of SQL Server, but you do not want to reinstall the DBMS.
Note. The instructions also apply to Microsoft SQL Server 2012 and R2.
SQL Server SQL 2008 R2 (and newer versions) trial period is – 180 days. After graduation trial period The SQL Server service stops starting. In the case of SQL Server Express, there is a limit on the size of the database.
You can upgrade your license license without having to reinstall SQL Server only from a junior edition to a higher edition, for example: Evaluation Edition / Express Edition with Advanced Services -> Standard or directly to Enterprise.
After the testing period ends, when you try to open SQL Server Management Studio, an error window appears:
Evaluation period has expired. For information on how to upgrade your evaluation software please go to http://www.microsoft.com/sql/howtobuy
MS SQL Server 2008 R2 edition update
In the first part, we will figure out how to update the version of SQL Server using a license key purchased from Microsoft or partners.
Let's launch the SQL Installation Center (Start -> All Programs -> Microsoft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Installation Center (64-bit)).
Advice. The same window can be opened by running setup.exe with installation disk SQL Server
Let's go to the service section ( Maintenance) and select the item EditionUpgrade(Editorial update). The Edition Upgrade Wizard will launch.
And, following the wizard’s prompts, enter the license SQL key Server in the appropriate field.
Then you need to select which copy (instance) of SQL Server you want to update.
The final report will indicate the status of the version upgrade and a link to a detailed text log.
You can check which edition of SQL Server you are using using SQL Server Management Studio by right-clicking on the instance name. In our example, this is SQL Server Enterprise.
The same operation can be performed from command line by running the setup.exe file from the SQL Server distribution disk with the specified parameters.
Setup.exe /Q /ACTION=editionupgrade /INSTANCENAME=
Advice. When upgrading from SQL Server Express, you must also enable the SQL Server Agent service and using SQL Server Configuration Manager set service settings account for the SQL Server Agent service.
Upgrade to the corporate Enterprise version of SQL Server (Volume License)
If you want to upgrade your current version of SQL Server under the Microsoft Volume Licensing program, you will be surprised that the Microsoft Volume Licensing Service Center (MS VLSC) does not have keys for SQL Server.
The fact is that the key is integrated directly into the distribution. The most interesting thing is that VLSC support recommends uninstalling the installed SQL Server and reinstalling it using the iso file with the SQL distribution downloaded from VLSC.
Fortunately, it is still possible to obtain the correct key directly from the distribution
Updating data in a database means changing values in existing table records. In this case, it is possible to both change the values of fields in a group of rows (even all rows of the table), and edit the value of a field in an individual row.
In SQL, you can change a record in a database table using the UPDATE command. In its most minimal form, the data update command looks like this:
UPDATE table SET field = value
Here, UPDATE– a command indicating that the request is to update data;
table– the name of the table in which changes will be made;
SET– a command followed by comma-separated fields with assigned values;
field– table field to which the change will be made;
meaning– a new value that will be entered into the field.
For example, if you need to set a field in all rows of a table to zero, you can run the following query:
UPDATE goods SET price = 0
In this case, the price field in absolutely all available rows of the table will take the value 0.
Changing one value
Changing the values of all fields in a table is extremely rare. Most often it is necessary to change the value of a specific entry. To do this, at the end of the line with the UPDATE command, a WHERE directive will be added, which specifies a condition that determines which line the update operation should be performed on.
There is a table:
For example, we need to update the cost of a product with its value known to us num. To do this, run the following query:
UPDATE goods SET price = 150 WHERE num = 2
Now, before the operation of changing fields, a row will be selected that satisfies the condition num = 2. There is only one such row in the table. In this stock the price will be changed to the value 150. As a result, we will get a table with the changed price of the product.
Making changes to multiple lines with a selection condition
If you remember all the variety of conditions in the query, you can imagine how diverse the samples can be. Therefore, update queries can be executed either with one row, or with a group of rows, or with all rows of the table. It all depends on the task you are facing, as well as on which table rows you need to perform update operations on.
For example, we want to halve the price of all goods that currently cost 100 or more. Request:
UPDATE goods SET price = price / 2 WHERE price >= 100
Condition WHERE here contains a rule according to which only products with a price equal to or more than 100 will be selected, and those products with a price below 100 will not be affected by the request.
price = price / 2– the formula by which the new price of goods will be calculated. New price will be written equal to old price divided by two.
As a result of executing such a query, we will obtain a table with changed records:
Updating values in multiple row fields
If it is necessary to update several fields at once, all fields with their values are indicated after the SET directive, separated by commas. For example, you need to change the name and price of a product with code 2 to “iron”, costing 300:
UPDATE goods SET title = "iron" , price = 300 WHERE num = 2 !}
This query will assign each matching field in a row its value. And the condition will indicate in which line the changes will be made.
The following are the main types of update operations. Based on them, queries are generated to solve most data modification problems in development using SQL.
