MSSQL: Database Diagram V17.9 feature crashed

Trying to map FKs between Dimension and Fact Tables and Database Diagram crashed with error message "Index was outside the bounds of the array". Can't get around it and error sounds like running out of memory. 

Closed everything and reopened get me going again. The issue seems pretty random.

MSSQL: Create a copy of a table and database

Create a copy of a static database.

CREATE DATABASE VCDB_COPY AS COPY OF VCDB

In a way, this is very much creating and restoring from a full backup. 

MSSQL: Instant File Initialization

Enabling Instant File Initialization can potentially improve over performance. It works a similar manner as thick eager provisioning in vCenter than letting it grows as it needs by zeroing the disk.

How To Enable Instant File Initialization
1. Open Local Security Policy and go to Local Policies –> User Rights Assignment.

2. Double click Perform Volume Maintenance Tasks and add your SQL Server database engine service account.
3. Restart the SQL Server service using SQL Server Configuration Manager and this setting should now be enabled.

MSSQL: Using DBCC to gather statistic

Trying to get statistic for performance diagnostic for a bloated vCenter table - VPX_EVENT.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-show-statistics-transact-sql?view=sql-server-2017

dbcc show_statistics ("table_name", index_name);

use vcdb_2016

dbcc show_statistics ("vpx_event", pk_vpx_event);

dbcc show_statistics ("vpx_event", pk_vpx_event) with histogram;

MSSQL: Rebuild Index ONLINE

Comparing Rebuild Online on databases that I am familiar with. 

SQL Server
alter index xxxx rebuild ON VPX_VM REBUILD WITH (ONLINE = ON);
alter index xxxx rebuild ON VPX_VM REORGANIZE;

Oracle
alter index xxxx on VPX_VM online;
alter index xxxx rebuild ON VPX_VM REORGANIZE;

Postgresql
alter index xxx on VPX_VM online;
(No Reorganize feature for Postgresql until Postgresql 10)
Alternatively, performing Rebuild Index concurrently.

The awkward way of SQL Server to rebuild index online.

MSSQL: Export a single table in CSV format

Sometimes, development may request a database to be cloned in-house to look at certain data. Most of the times, the administrator will clone the database to an internal lab where only a single table of interest to be looked at. This is quite waste of the time to backup hundred of Gigs of the database, bandwidth is transferring them, a large amount of space taken to store the database backup, later transfer it to an internal lab and restore it to a SQL Server instance. Storage usage to look at a single table is taken 4 times the size of the database from backup, storage, transfer and restored.

The administrator can easily achieve this in no time by exporting a single copy of the table in CSV. Development may likey not even needing the SQL Server to look at the table since CSV can be opened with Excel spreadsheet.

Importing it back to a SQL Server works the same way. For a smaller table, an administrator can simply perform a query and copy the result out from the output.

sqlcmd that works the same too if the user already knows what s/he wants especially the separator.

sqlcmd -S . -d VCDB_2016 -E -s "^" -W -Q "select * from vpx_event" > E:\vpx_version.csv

MSSQL: Setup SQL Server with None Default Port

Not many sites talk about this and providing screenshots on how to configure and enable non default Port for SQL Server.

Step 1: Configure the SQL Server Network Configuration to enable TCP/IP and specify a non-default port.

Step 2: Enable The TCP/IP

Step 2b: In my example, I am pretending 1433 is a non-default port. If the user desire to change it to anything else, click on the IP Address tab and scroll ALL THE WAY DOWN and change the port of 1433 to something else.

Step 3: Once completed, the service needs to be restarted.

Step 4:  Start Menu - type "ODBC" . Fill in the information on the first page and click Next to the end, then click Finish. At the Finish page, click on Test Connection.

Step 4a: In order to connect SQL Server database with a non-default port. It can simply be done through simply appending the port number after the Server Name with a "," in between.

Test the connections. If the connection is successful, the user will be prompt with a successful page. Otherwise, it will be a Test Failed page.

Test Failed if steps are incorrect.

Other steps may be necessary depending on what you do

- Enable SQL Server Browser for automatic discovery.

- Enable firewall for non default port on the Windows Server

- Enable remote admin
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/remote-admin-connections-server-configuration-option?view=sql-server-2017

sp_configure 'remote admin connections', 1; GO RECONFIGURE; GO