MSSQL on Linux datafiles with C prompt

Installing MSSQL on Linux is fairly straightforward.

Installation 

https://www.microsoft.com/en-us/sql-server/sql-server-vnext-including-Linux

For MSSQL on Ubuntu simply follow this link.

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-ubuntu

SQL Server installation do enforce the 3.25Gig memory size. So, prior to stat the installation make sure you environment or VM do have around 4gig of memory to work with. I allocated 60Gig on my VMs. After the Ubunto Server 64 bits deployment and MSSQL installation, I still have 52gig left.

The main point of this blog is not to guide user to deploy MSSQL on Linux but to show one of the interesting behavior I noticed.

administrator@ubuntu:/var/opt$ sqlcmd -U SA

Password:

1> select @@version;

2> go

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Microsoft SQL Server vNext (CTP1.1) - 14.0.100.187 (X64)

Dec 10 2016 02:51:11

Copyright (C) 2016 Microsoft Corporation. All rights reserved.

on Linux (Ubuntu 16.10)

(1 rows affected)

1> select name from sys.databases;

2> go

name

--------------------------------------------------------------------------------------------------------------------------------

master

tempdb

model

msdb

(4 rows affected)

1>

As you can see here, the datafile path starts with C:\ prompt.

SELECT cast(DB_NAME(database_id) as varchar(20)) AS "Database Name",

       cast(Name as varchar(25)) AS "Logical Name",

       cast(Physical_Name as varchar (100)) as "Datafiles Path",

       (size * 8) / 1024 as "Size in MB",

       (size * 8) / 1024/ 1024 as "Size in Gig"

FROM sys.master_files;

Database Name        Logical Name              Datafiles Path                                                                                       Size in MB  Size in Gig

-------------------- ------------------------- ---------------------------------------------------------------------------------------------------- ----------- -----------

master               master                    C:\var\opt\mssql\data\master.mdf                                                                               4           0

master               mastlog                   C:\var\opt\mssql\data\mastlog.ldf                                                                              2           0

tempdb               tempdev                   C:\var\opt\mssql\data\tempdb.mdf                                                                               8           0

tempdb               templog                   C:\var\opt\mssql\data\templog.ldf                                                                              8           0

model                modeldev                  C:\var\opt\mssql\data\model.mdf                                                                                8           0

model                modellog                  C:\var\opt\mssql\data\modellog.ldf                                                                             8           0

msdb                 MSDBData                  C:\var\opt\mssql\data\MSDBData.mdf                                                                            13           0

msdb                 MSDBLog                   C:\var\opt\mssql\data\MSDBLog.ldf                                                                              0           0

User can access the physical datafile with sudo.

administrator@ubuntu:/var/opt$ sudo ls -las /var/opt/mssql/data/

total 53320

    4 drwxr-xr-x 2 mssql mssql     4096 Dec 19 10:54 .

    4 drwxrwx--- 7 mssql mssql     4096 Dec 19 10:55 ..

 4096 -rw-r----- 1 mssql mssql  4194304 Dec 19 11:06 master.mdf

 2048 -rw-r----- 1 mssql mssql  2097152 Dec 19 12:06 mastlog.ldf

 8192 -rw-r----- 1 mssql mssql  8388608 Dec 19 11:00 modellog.ldf

 8192 -rw-r----- 1 mssql mssql  8388608 Dec 19 11:00 model.mdf

13632 -rw-r----- 1 mssql mssql 13959168 Dec 19 10:55 msdbdata.mdf

  768 -rw-r----- 1 mssql mssql   786432 Dec 19 10:55 msdblog.ldf

 8192 -rw-r----- 1 mssql mssql  8388608 Dec 19 10:55 tempdb.mdf

 8192 -rw-r----- 1 mssql mssql  8388608 Dec 19 11:00 templog.ldf

Interesting that SQL Server still keeping the Windows behavior.

Unable to access VCSA appliance after initial setup

After everything's deployed, ssh, ping, curl works from within the appliance but access through web browser or anything remote will not worked..

At this point, only either firewall or gateway would be the issue. Sure enough, gateway was not even listed (default). Adding temporary gateway works immediately. I am able to see the Web Client.

Checking the IP Route, I was not finding my default Gateway at all.

Running the following command fixed my webclient issue. This is a temporary solution. After reboot of the appliance, the gateway will disappeared. One can implement a permanent solution to the network entries.

route add default gw 10.131.15.254

The Gateway can be found at the host - Configuration but for some reason, it did not get picked up during deployment. 

VCSA: Where does vCenter Appliance stores its Performance Chart connect string.

Be reminded that this is going through JDBC instead of ODBC. This piece is responsible for Hourly, Daily, Weekly and Yearly Performance Chart.

/etc/vmware-vpx/vcdb.properties

driver = org.postgresql.Driver

dbtype = PostgreSQL

url = jdbc:postgresql://localhost:5432/VCDB

username = vc

password = w4%wH3@!BybXd

password.encrypted = false

VCSA: Where does vCenter Appliance store the ODBC settings?

It resides in /etc/odbc.ini

[VMware VirtualCenter]

;DB_TYPE = PostgreSQL

;SERVER_NAME = localhost

;SERVER_PORT = 5432

;TNS_SERVICE = VCDB

;USER_ID = vc

Application Attributes = T

Attributes = W

BatchAutocommitMode = IfAllSuccessful

BindAsFLOAT = F

CloseCursor = F

DisableDPM = F

DisableMTS = T

Driver = PostgreSQL

DSN = VMware VirtualCenter

EXECSchemaOpt =

EXECSyntax = T

Failover = T

FailoverDelay = 10

FailoverRetryCount = 10

ForceWCHAR = F

Lobs = T

Longs = T

MetadataIdDefault = F

QueryTimeout = T

ResultSets = T

ServerName = localhost

PortNumber = 5432

Server = localhost

Port = 5432

SQLGetData extensions = F

Translation DLL =

Translation Option = 0

DisableRULEHint = T

UserID = vc

User = vc

Database = VCDB

Logging = 0

QuotedId = Yes

AnsiNPW = Yes

Mars_Connection = No

ByteaAsLongVarBinary = 1

BoolsAsChar = 0

UseDeclareFetch = 1

Fetch = 1024

VCSA 6.5 Failed to complete.

Deployed in various hosts with the same results.

Encountered an internal error. Traceback (most recent call last): File "/usr/lib/vmidentity/firstboot/vmidentity-firstboot.py", line 2018, in main vmidentityFB.boot() File "/usr/lib/vmidentity/firstboot/vmidentity-firstboot.py", line 349, in boot self.configureSTS(self.__stsRetryCount, self.__stsRetryInterval) File "/usr/lib/vmidentity/firstboot/vmidentity-firstboot.py", line 1479, in configureSTS self.startSTSService() File "/usr/lib/vmidentity/firstboot/vmidentity-firstboot.py", line 1141, in startSTSService returnCode = self.startService(self.__sts_service_name, self.__stsRetryCount * self.__stsRetryInterval) File "/usr/lib/vmidentity/firstboot/vmidentity-firstboot.py", line 88, in startService return service_start(svc_name, wait_time) File "/usr/lib/vmware/site-packages/cis/utils.py", line 784, in service_start raise ServiceStartException(svc_name) ServiceStartException: { "resolution": null, "detail": [ { "args": [ "vmware-stsd" ], "id": "install.ciscommon.service.failstart", "localized": "An error occurred while starting service 'vmware-stsd'", "translatable": "An error occurred while starting service '%(0)s'" } ], "componentKey": null, "problemId": null }
Resolution:
This is an unrecoverable error, please retry install. If you run into this error again, please collect a support bundle and open a support request.


Currently, this seem like a known issue or user error.

According to inside information, the DNS for IPV6 was not set, so the workaround is the echo "::1 localhost" >> /etc/hosts right after Stage 1 is completed. ssh into the appliance to append the localhost. This is thanks to my colleague A.S.

Prior to Stage 2, there will be a screen asking if 'ssh' to be enabled. Check that box to enable the ssh and get the following command ready. Upon clicking on Finish, the ssh will be enabled immediately. At this point, ssh into the appliance and quick changing the /etc/hosts file.


echo echo "::1 localhost.localdom localhost" >> /etc/hosts




Note: echo is a standard linux command. In order to execute it, it needs to be in the shell. vcsa entry menu is not in default shell. User will need to switch it.

New Changes in VCSA 6.5 Web installs

VCSA6.5. New features include additional Migrate and Restore.  VCSA no longer offers external database deployment to Oracle. That option screen simply no longer there. I guess default to vPostgresql now. vCenter install probably still have the external feature.

Stage 2 installation has been created and some of the early installation such as SSO and shh enablement has been pushed to that stage. Basically, nothing much changed in that area.

VCSA Web Install seems to be lot faster.

VCSA 6.0

VCSA 6.5 is using VMware Photon OS now.

SQL Server: Transferring ownership from one owner to another in sql server.

Occasionally, this could happen in vCenter where some objects belong to other owners aside from dbo.

Referemce: https://msdn.microsoft.com/en-us/library/ms173423.aspx

Transfer vpx_version from dbo to guest.

alter schema guest transfer dbo.vpx_version;

sp_tables vpx_version

select distinct sys.schemas.name from sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id and sys.schemas.name <>'sys';

Transfer vpx_version from guest to dbo.

alter schema dbo transfer guest.vpx_version;

sp_tables vpx_version

To change all objects from none dbo to dbo schema.

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + o. Name

    FROM sys.Objects o

    INNER JOIN sys .Schemas s on o. schema_id = s. schema_id

    WHERE s.Name = 'dbo'

    And (o. Type = 'U' Or o .Type = 'P' Or o.Type = 'V')

SQL Server: Script to reorg vCenter indexes in SQL Server

Script to reorg vcenter indexes above 70% fragmentations.

SQLCMD -i d:\reorf.sql -o d:\reorg.txt

use vcdb

select 'alter index ' +  ind.name + ' on ' + OBJECT_NAME(ind.OBJECT_ID) + ' reorganize;' from sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind  ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id

 WHERE indexstats.avg_fragmentation_in_percent > 70  ORDER BY indexstats.avg_fragmentation_in_percent DESC;

Changed database context to 'VCDB'.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

alter index VPXI_EVENT_EVENT_TYPE on VPX_EVENT reorganize;

alter index VPX_EVENT_ARG_F1 on VPX_EVENT_ARG reorganize;

alter index PK_VPX_DBM_COUNTER_VALUE on VPX_DBM_COUNTER_VALUE reorganize;

alter index VPXI_EVENT_CATEGORY on VPX_EVENT reorganize;

alter index VPXI_EVENT_CREATE_TIME on VPX_EVENT reorganize;

alter index VPXI_EVENT_EXTENDED_CLASS on VPX_EVENT reorganize;

alter index VPX_EVENT_ARG_F2 on VPX_EVENT_ARG reorganize;

alter index PK_VPX_SAMPLE_TIME1 on VPX_SAMPLE_TIME1 reorganize;

alter index PK_VPX_SAMPLE_TIME2 on VPX_SAMPLE_TIME2 reorganize;

alter index PK_VPX_SAMPLE_TIME3 on VPX_SAMPLE_TIME3 reorganize;

alter index VPX_SAMPLE_TIME3_U1 on VPX_SAMPLE_TIME3 reorganize;

alter index PK_VPX_TABLE on VPX_TABLE reorganize;

(12 rows affected)

Setting up nmon and nmon_analyzer

This is one of the most usual tool I use along with RDA/OracChk and OSwatcher.

Download nmon

http://nmon.sourceforge.net/pmwiki.php?n=Site.Download

wget http://sourceforge.net/projects/nmon/files/nmon16e_mpginc.tar.gz

[root@localhost Downloads]# tar -xzvf nmon16e_mpginc.tar.gz

nmon_arm_ubuntu15

nmon_power_32_rhel6

nmon_power_32_sles11

nmon_power_64_kvm2

nmon_power_64_rhel6

nmon_power_64_rhel7

nmon_power_64_sles11

nmon_power_64le_fedora22

nmon_power_64le_rhel6

nmon_power_64le_rhel7

nmon_power_64le_ubuntu14

nmon_power_64le_ubuntu15

nmon_power_64le_ubuntu16

nmon_x86_64_centos6

nmon_x86_64_centos7

nmon_x86_64_opensuse11

nmon_x86_64_opensuse12

nmon_x86_64_rhel6

nmon_x86_64_rhel7

nmon_x86_64_sles11

nmon_x86_64_sles12

nmon_x86_64_ubuntu15

[root@localhost Downloads]# ./nmon_x86_64_rhel6 -f

[-f will put the output into cvs format]

Add-On for nmon.

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/Power%20Systems/page/nmon_analyser

https://www.ibm.com/developerworks/community/wikis/form/anonymous/api/wiki/61ad9cf2-c6a3-4d2c-b779-61ff0266d32a/page/b7fc61a1-eef9-4756-8028-6e687997f176/attachment/a51b993c-a47c-4eee-be12-6ea2d26ff9a2/media/nmon_analyser_v5_0_2.zip

[root@localhost Downloads]# mkdir nmon_analyzer

[root@localhost Downloads]# cd nmon_analyzer

[root@localhost nmon_analyzer]# wget https://www.ibm.com/developerworks/community/wikis/form/anonymous/api/wiki/61ad9cf2-c6a3-4d2c-b779-61ff0266d32a/page/b7fc61a1-eef9-4756-8028-6e687997f176/attachment/a51b993c-a47c-4eee-be12-6ea2d26ff9a2/media/nmon_analyser_v5_0_2.zip

At the command prompt, do

/nmon_x86_64_rhel6 -f -s2 -c 30

Run the nmon_analyzer and click at "Analyze nmon data" Button. (Note: macros must be enabled)

Search for the nmon file. nmon_analyzer will process and save the file as CVS format.

It is that easy! enjoy.

SQL Server: ..... " is incorrectly formed. SQL Server cannot process this media family "

This means sql server backup and restore not in the same version. SQL Server backup is not backward compatible.

Trying to perform a backup from Windows 2008 R2 SQL Server 2012 and restore it to Windows 2008 R2 Windows 2008. Later, realized the version was not the same. Spun up a new VM with SQL Server 2012 and was able to restore it correctly.

Msg 3241, Level 16, State 13, Line 1

The media family on device 'C:\VCDB\vcdb1\vcdb1.bak' is incorrectly formed. SQL Server cannot process this media family.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Unable to connect to the MKS: Could not connect to pipe

2 of my VMs at the lab were ping-able but cannot ssh or telnet in. Looking at the console in the VMs, it showed the following error. Other VMs in the same ESX Host were fine just these 2 were having ssh issues.

Put the ESX host on Maintanence mode then rebooted the hosts everything came back up fine. I was told, the switch was having issue recently.

SQL Server: Why does my sql server database growth and shrink on its own ?

Got a question from a user today that his SQL Server growing and shrinking on its own. Being shallow in SQL Server for the time being. That's triggered my curiosity and starting to do some reading about it.There are features in SQL Server database for this purpose.

Get a general idea of which DB actually growing or shrinking by monitoring it for a bit.

SELECT

    DB.name,

    MF.physical_name,

    MF.type_desc AS FileType,

    MF.size * 8 / 1024 AS FileSizeMB,

    fileproperty(MF.name, 'SpaceUsed') * 8/ 1024 AS UsedSpaceMB,

    mf.name LogicalName

FROM

    sys.master_files MF

    JOIN sys.databases DB ON DB.database_id = MF.database_id;

SELECT * FROM sys.database_files;

Look at the autoshrinking option

select name, is_auto_shrink_on  from  sys.databases;

From the GUI.

Also likelihood reindexing, large batch jobs and database shrinking. Those do cause transaction logs to grow and shrink.

Also might be DBA performing shrinkfile.

BACKUP LOG VCDB_LOG TO DISK = 'E:\SQLbackup.bak'

GO

DBCC SHRINKFILE (N'VCDB_LOG' , 10)

GO

Impact on Oracle if ESX Host went down.

Here are collection of scenarios if ESX hosts gone down.

Symptom - vmwarning and storagerm logs will spit out tons of the following messages.


Some host is down, need to reset the slot allocation
Number of hosts has changed to 3
Number of hosts has changed to 8
Number of hosts has changed to 7
Some host is down, need to reset the slot allocation

Occasionally, the following would show up. This is when the the storage is impacted.

: NFSLock: 2208: File is being locked by a consumer on host 

As any Oracle DBA already can guess, the Oracle instance will crash and all depends on underlying disk availability. If the disks are down, ASM diskgroup will go down with it.

Alert log might show some or more of the following.


ORA-63999: data file suffered media failure
ORA-01114: IO error writing block to file 20 (block # 392072)
ORA-01110: data file 20: '+ASMDATA/st
ORA-15080: synchronous I/O operation failed to read block 0 of disk 7 in disk group DATA
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 24576
Additional information: 4294967295
NOTE: cache initiating offline of disk 7 group DATA

What can you do about it? If it's single instance, not much. Upon bringing up the ESX Host, it should restore the services (most of the time), otherwise, DBA might need to restore and recover from RMAN. If it's a RAC setup (RAC on multi-hosts), perhaps check the SCSI Controller's SCSI Bus Sharing options is set to NONE, things should failover to the node that residing in the good host. 

Oracle: Unable to access to Oracle 12C EM outside of a VAPP or VM.

I have seen user struggles with this for along period of time. Hence, I am posting this and hopefully, it helps in nailing the issue faster.  However, not all Linux will need to address this. I do not recalled default installation of Oracle 12C on Red Hat needs to address this at all only Centos. I could be wrong. 

Inside the Oracle Database VM.

https://<ip address>:5500/em

Outside of the VM, unable to connect. Pretty obvious, this is an indication that there are some sort of firewalls issues either from the VM itself or the host layer if the VM can be ping-ed and ssh-ed into.

In the VM check the firewalls HTTPS and ports.

Accessible now.