Rename a computer name that serves as a SQL Server – how much work it requires

Goal

To know up front the work that needs to be performed when we need to rename a computer that has SQL Server installed on it.

My way of achieving it

Using some of the code that other people already wrote and modifying it together with the Microsoft recommendations when it comes to renaming the computer name. I tried to have this overview in order to estimate how long or how hard it will be to modify the computer name depending on how many instances we have there and what features are already enabled. The script is far from being complete but as I built on top of other scripts we can use it as base for other checks and tests and so on.

The output of the script will look similar to this

renamecomputersteps

The script can be downloaded fromĀ here

Balance tables across SQL Server database files in the primary filegroup or other filegroups

Goal

To have an overview of the tables that can be moved across the database files in a database and see if they can be moved or not

My way of achieving it

Using tsql. The script is checking the edition of SQL Server and the biggest tables from our database and will check if these tables contains LOB data and how much from the table is LOB and how much is not. Tests showed that even if you have a column that is of LOB type, if the value can fit in the page then you can still move that page to other locations in the database files. I tried to find a way to map the table to database files but I couldn’t find a solution that will work in a fashionable time. Searching on internet revealed that I can use:

  • DBCC IND(0,’tablename’,1)
  • SELECT sys.fn_PhysLocFormatter(%%physloc%%) as rowlocation FROM tablename

The problem with first one is that for huge tables is taking a lot of time which makes it unusable while the second one although is faster for a 100GB table it is still takes minutes to run. I would have wanted to have this information in order to estimate how much space a table occupies on each of the database files. Also the script is ignoring the tables that are partitioned already.

The script is nothing more than a procedure that can help us to provide a quick answer to the question – Can I move some tables to other files or not?

Like with everything else from the internet please try it first and check if the information provided are accurate or not. Below is an example of the script output.

The script can be downloaded fromĀ here