Upgrade SQL Server Evaluation edition to license Edition of SQL Server

The SQL Server Evaluation Edition is a great way to get a fully functional and free instance of SQL Server for learning and developing solutions. The edition has a built in expiry of 6 months from the time that you install it.

One of my client doesn't have licensed key for SQL Server and they have planned to add key later. To apply licensed key to an existing evaluation edition of SQL Server is different from windows. Follow the below procedure.

In below screenshot, SQL Server is Enterprise evaluation edition.

Upgrade SQL Server Evaluation edition to license Edition of SQL Server

Step 1:

 Mount SQL Server licensed setup file to the server.

 Launch SQL Server "setup.exe", select maintenance tab and select edition upgrade.

Step 2:

Enter the licensed product Key and click next

Step 3:

Continue with running Edition Upgrade Rules

Step 4:

Select the instance name that you want to upgrade

Step 5:

Select upgrade to proceed with upgrade process and close it once successfully upgraded

In below screenshot, SQL Server Edition is successfully upgraded.

Editions Of SQL Server

There are different editions of SQL Server with different features. Which will provide wide range of options to choose from to match our business requirements.

• Enterprise: This is the high-end edition with the full features. It supports systems up to 2 TB RAM. The maximum size of the database is 524 PB.

• Standard: It has less features than Enterprise, but it is a good choice when advanced functions (such as data compression, compressed backups, indexed views, etc.) are not required for the business. It supports systems up to 4 CPU and 64 GB RAM.

• Workgroup: it is suitable for remote offices of a larger company. It supports systems up to 2 CPU and 4 GB RAM.

• Web: it is designed for web applications. It supports 4 CPU without memory limitations.

• Developer: similar to Enterprise, but licensed to only one user for development, testing and demo. It can be easily upgraded to Enterprise without reinstallation.

• Express: free entry-level database. It can utilize only 1 CPU and 1 GB memory, the maximum size of the database is 10 GB.

• Compact: free embedded database for mobile application development. The maximum size of the database is 4 GB.

Log File Architecture in SQL Server

 Whenever any query is processed, the data will be passed to Data file. Below is the process how a query is processed in SQL Server/ Importance of Log File Architecture:

• Database has Data file and Log file.
• The query statement is passed to the buffer cache and log cache.
• The data in the buffer cache is called as Dirty Data or Dirty Blocks.
• Buffer cache contains the dirty data (the updated data corresponding to the query given in the application).
• The process of writing the data from buffer cache to the data files of the database in the form of chuncks is
  called as Checkpoint Process.
• Each chunck contains 512 KB.
• The query is written into the log file from the log cache.
• If any type of failure occurs while writing data to the data file, then the query in the log file is executed at the last commit transaction processed (refer commit process down) and the remaining data is written to the database whenever we start the server.
• This process of writing data to the database after a failure from the log file is called as Recovery.
• Procedure Cache contains the execution plan.
• Context Cache contains the data of the stored procedure.
• Server Level Data Structure contains the Server level information.

Commit Process:

• As soon as commit statement is written to the log file it throws a token to the user that commit is completed successfully (Ex. 1 row affected), this process is called as Commit Process.

What is an execution plan?

Every day, out in the various discussion boards devoted to Microsoft SQL Server, the same types of questions come up again and again: Why is this query running slow? Is my index getting used? Why isn’t my index getting used? Why does this query run faster than this query?. The correct response is probably different in each case, but in order to arrive at the answer you have to ask the same return question in each case: have you looked at the execution plan? An execution plan, simply put, is the result of the query optimizer’s attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted.

Execution plans can tell you how a query will be executed, or how a query was executed. They are, therefore, the DBA’s primary means of troubleshooting a poorly performing query. Rather than guess at why a given query is performing thousands of scans, putting your I/O through the roof, you can use the execution plan to identify the exact piece of SQL code that is causing the problem. For example, it may be scanning an entire table-worth of data when, with the proper index, it could simply backpack out only the rows you need. All this and more is displayed in the execution plan.

The aim of this chapter is to enable you to capture actual and estimated execution plans, in either graphical, text or XML for ­mat, and to understand the basics of how to interpret them. In order to do this, we’ll cover the following topics:

• A brief backgrounder on the query optimizer – execution plans are a result of the optimizer’s calculations so it’s useful to know at least a little bit about what the optimizer does, and how it works.
• Actual and Estimated execution plans – what they are and how they differ
• Capturing and interpreting the different visual execution plan formats – we’ll investigate graphical, text and XML execution plans for a very basic SELECT query
• Automating execution plan capture – using the SQL Server Profiler tool

Shrink Operation- Internally what happens?

Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can be deallocated and returned to the file system.

Limitations and Restrictions:

• The database cannot be made smaller than the minimum size of the database. The minimum size is the size specified when the database was originally created, or the last explicit size set by using a file-size-changing operation, such as DBCC SHRINKFILE. 
• 
  
• For example, if a database was originally created with a size of 10 MB and grew to 100 MB, the smallest size the database could be reduced to is 10 MB, even if all the data in the database has been deleted.
• 
  
• You cannot shrink a database while the database is being backed up. Conversely, you cannot backup a database while a shrink operation on the database is in process.

• DBCC SHRINKDATABASE will fail when it encounters an xVelocity memory optimized columnstore index. Work completed before encountering the columnstore index will succeed so the database might be smaller. 
• 
  
• To complete DBCC SHRINKDATABASE, disable all columnstore indexes before executing
•  
• DBCC SHRINKDATABASE, and then rebuild the columnstore indexes.

Recommendations:

• To view the current amount of free (unallocated) space in the database.
• Consider the following information when you plan to shrink a database: 
• A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
• Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
• A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
• Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.