Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Tips #SQL Server 2016 Database Mail does not work on a computer that does not have the .NET Framework 3.5 installed or stops working after applying SQL Server update

In the past we faced this problem:

https://support.microsoft.com/en-us/topic/kb3186435-fix-sql-server-2016-database-mail-does-not-work-on-a-computer-that-does-not-have-the-net-framework-3-5-installed-or-stops-working-after-applying-sql-server-update-3480beb6-1329-74d6-0f3a-e8e3d893326c

This is official Microsoft KB that fix problem:

https://learn.microsoft.com/en-us/troubleshoot/sql/releases/sqlserver-2016/servicepack1-cumulativeupdate2

Posted by
Labels: ,

Vmware Cloud Director &Microsoft Failover Cluster & Named Disks

To setting up Microsoft Failover Cluster there are some specifics requirements. (in our case related to SQL)
First of all allow all cluster nodes must access shared disk sending iSCSI commands.
Here they are complete requirements.

https://techdocs.broadcom.com/us/en/vmware-cis/vsphere/vsphere/8-0/setup-for-windows-server-failover-clustering/getting-started-with-wsfc/clustering-configuration.html

To allow this configuration on Vmware Cloud Director it is necessary to create Named Disk.
Here it is official documentation that confirm that this configuration is feasible

https://techdocs.broadcom.com/us/en/vmware-cis/cloud-director/vmware-cloud-director/10-5/map-for-vmware-cloud-director-tenant-portal-guide-10-5/using-named-disks-reviewing-storage-policies-and-migrating-storage-policy-entities-tenant.html

https://www.bdrsuite.com/blog/vmware-cloud-director-named-disks/

Finally here they are some screenshots:









Posted by
Labels: , ,

SQL - Antivirus Exclusions

I am taking note about SQL required Antivirus Exclusions:

https://support.microsoft.com/it-it/topic/come-scegliere-il-software-antivirus-in-esecuzione-su-computer-che-eseguono-sql-server-feda079b-3e24-186b-945a-3051f6f3a95b

Process exclusions

%ProgramFiles%\Microsoft SQL Server\<Instance_ID>.<nome istanza>\MSSQL\Binn\SQLServr.exe

%ProgramFiles%\Microsoft SQL Server\<Instance_ID>.<nome istanza>\Reporting Services\ReportServer\Bin\ReportingServicesService.exe

%ProgramFiles%\Microsoft SQL Server\<Instance_ID>.<nome istanza>\OLAP\Bin\MSMDSrv.exe

%ProgramFiles%\Microsoft SQL Server\1xx\Shared\SQLDumper.exe

 

Directory (and sub-directories):

D:\Program Files\Microsoft SQL Server

 

File extentions:

SQL Server data files:

.mdf

.ldf

.ndf

 

SQL Server backup files

.bak

.trn


Posted by
Labels: , , ,

SQL - Dbatools SQL Server PowerShell module

Here it is an interesting Tutorial about SQL Server powershell module named Dbatools used primarly about disaster recovery. (it is free dbatools.io/download)

Estensive documentation:

dbatools.io/Test-DbaLastBackup

dbatools.io/Set-DbaSpn

Some videos:

dbatools.io/youtube

https://channel9.msdn.com/Shows/Data-Exposed/How-to-Automate-Disaster-Recovery-in-SQL-Server-On-Prem

<iframe src="https://channel9.msdn.com/Shows/Data-Exposed/How-to-Automate-Disaster-Recovery-in-SQL-Server-On-Prem/player" width="960" height="540" allowFullScreen frameBorder="0" title="How to Automate Disaster Recovery in SQL Server On-Prem - Microsoft Channel 9 Video"></iframe>

Have a look to below screenshots:









Posted by
Labels: , , ,

Tips - SQL Server 2016 Evaluation Expired

In case you installed SQL evaluation edition and trying to upgrade it to a licensed copy you will probably face to service is not staring with:

 error code 17051, Severity: 16, state: 1 
SQL Server evaluation period  has expired 

 (viewing Errorlog)

 Here is relative article that will help you.

 https://www.codykonior.com/2017/11/30/upgrading-an-expired-sql-server-2016-evaluation-edition/
Posted by
Labels: ,

Microsoft - Windows 7, SQL 2008, 2008 R2 Server, Exchange 2010, Office 2010 EOL - End of life date

Just a quick post taking note about several Microsoft Products where End Of Life date is 2019 or 2020, the first important effect would be related about product and security patches unavailability.

End Of life Product and date


 Windows 7 --> January 14, 2020
Office 2010 EOL --> October 13th, 2020
SQL Server 2008 and 2008 R2 Extended Support --> will end on July 9, 2019
Windows Server 2008 and 2008 R2 Extended Support --> will end on January 14, 2020.
Exchange Server 2010 --> January 14, 2020
Products Reaching End of Support for 2019
Products Reaching End of Support for 2020 
Here they are some interesting information:

 - Windows 7 Embedded editions follow different EOL dates, you could use following official link.
- You could upgrade compatible Windows 7 PCs based on these requirements with a full license. 
- About SQL 2008 and W2008 R2 please review this article.
- Here it is indicated how Microsoft threat all its products since first release about Mainstream Support and Extended Support.
- About Windows Server Microsoft propose Microsoft Server upgrade center.
- To have visibility about all Microsoft Product EOL here it is correct link.

 [Update 2019.08.14]

 Here it is new Microsoft Article that refer to some interesting documents/links:
  1. Download the Migration Guide for Windows Server
  2. Storage Migration Service overview
  3. Prepare for Windows Server 2008 end of support
  4. Azure Migration Center
Posted by
Labels: , , , , ,

Freeware - SolarWinds All Free Network/System/Security/DB/clients monitoring tools



Today I would like to mention several SolarWinds Free tools. 

 They have limited options against full versions but have interesting features useful for day by day System and Network administration tasks/monitoring/troubleshooting.

System Administrations tools

  1. Solar-PuTTY Connect to any server or device in your network with Solar-PuTTY for Windows. This is an interesting Putty or PuttyCM alternative.
  2. VM Monitor Hyper-V: monitors your VMware vSphere or Microsoft Hyper-V host and associated virtual machines in real-time.
  3. Server Health Monitor Monitor the health, status, and availability of server hardware.
  4. Exchange Monitor  Monitor and alert on performance, availability, and capacity of your Microsoft Exchange servers.
  5. Remote Execution Enabler for PowerShell Streamline PowerShell tasks across all of your servers.
  6. Admin Bundle for Active Directory Keep your Active Directory tidy with this trio of management tools
  7. Permissions Analyzer for Active Directory Get instant visibility into user and group permissions.
  8. VM Monitor Continuously monitor a VMware or Microsoft Hyper-V host and associated virtual machines.
  9. Diagnostic Tool for the WSUS Agent Test WSUS connections and validate Windows Update Agent configuration.
  10. Web Transaction Watcher Easily record and capture the results of a single web transaction.
  11. WMI Monitor  Monitor real-time performance metrics on Windows servers and applications.
  12. SNMP Enabler for Windows Remotely configure SNMP on Windows servers and workstations.
  13. VM Console Bounce problematic VMs without ever logging into VMware vCenter.
  14. Storage Response Time Monitor Track your sluggish VMs and pinpoint exactly where you have high storage latency.

Security and various tools

  1. Free IT Security Tools:  Manage all end-user trouble tickets and track service request lifecycle, from ticket creation to resolution, from one centralized help desk management web interface.
  2. Event Log Forwarder for Windows Forward Windows events based on event source, event ID, users, computers, and keywords in the event to your syslog server in order to take further action.
  3. Firewall Browser Verify firewall rule changes and perform unlimited configuration searches.
  4. Event Log Consolidator  Consolidate logs from up to five Windows servers or workstations Graph events over time to find patterns and troubleshoot issues Send desktop alerts for specific events or export events to CSV.
  5. Free IT Help Desk Tools Manage all end-user trouble tickets and track service request lifecycle, from ticket creation to resolution, from one centralized help desk management web interface.
  6. Database Performance Analyzer Free Reveal performance bottlenecks deep inside your SQL Server, Oracle, DB2 and SAP ASE instances. No other database monitoring tool focuses on response time and Multi-Dimensional Performance Analysis™ like DPA does.

Network Management Tools

  1. Traceroute NG Perform accurate network path analysis.
  2. Port Scanner  Scan available IP addresses and their corresponding TCP and UDP ports to identify network vulnerabilities.
  3. Network Device Monitor: it work on any devieces with snmp credentials enabled there
  4. NetFlow Configurator Quickly and easily activate NetFlow on your network devices with NetFlow Configurator.
  5. GNS3 Network Emulator Construct and test your network in a risk-free virtual environment.
  6. Real Time AppFlow Analyzer Investigate, troubleshoot, and quickly resolve application and network slowdowns.
  7. Response Time Viewer for Wireshark Quickly analyze Wireshark® packet capture files to troubleshoot performance issues.
  8. Network Analyzer & Bandwidth Monitoring Bundle Monitor network bandwidth usage with our free network analyzer and bandwidth monitor.
  9. TFTP Server Free TFTP server for reliable and secure network file transfers.
  10. IP Address Tracker Scan, track, and manage IP addresses and obtain detailed IP histories and event logs.
  11. Real-Time Bandwidth Monitor Keep a close eye on bandwidth usage with this free bandwidth monitor from SolarWinds.
  12. Real-Time NetFlow Analyzer See what types of traffic are on your network with SolarWinds free netflow analyzer.
  13. Call Detail Record Tracker The free SolarWinds tool that lets you monitor VoIP performance.
  14. Network Configuration Generator Automatically create template-based configuration scripts for network devices.
  15. IP SLA Monitor Analyze IP service levels for network-based apps and services with our free IP SLA Monitor.
  16. Advanced Subnet Calculator Help ensure that your IP addresses don’t conflict with one another, and save time managing DHCP, DNS, and IP addresses.
  17. Wake-On-LAN Power up your network PC remotely.
  18. Kiwi Syslog Server Free Edition View and archive syslog messages and SNMP traps in real time.
  19. FTP Voyager FTP Client for Windows FTP, FTPS, and SFTP secure file transfer and automation with a full-featured scheduler.
  20. SFTP/SCP Server Free SFTP server for reliable and secure network file transfers.
Posted by
Labels: , , , , , , , , , , , ,

Linux - SQL installation MVA tutorial

As mentioned in earlier blog post SQL could be installed on Linux server as Well.

 Microsoft released, last year, SQL binaries for Linux too.

 Linux - How to install MS SQL and .NET on several Linux distributions
http://www.alessandromazzanti.com/2017/03/linux-how-to-install-sql-server-on.html

 Due to this fact I would like to mention this MVA Microsoft video tutorial that go deeper on this topic:

 https://mva.microsoft.com/en-US/training-courses/installing-configuring-and-managing-microsoft-sql-server-on-linux-18305?l=Z4MuSOc2E_2113214697
Posted by
Labels: , , ,

Scripting - Silent install SQL Studio Management Console Studio 17.2

If you need to silent install SQL Studio Management Console Studio 17.2 you can do that through command line and using properly command switches.

 I took note on this quickly post about this settings for future purposes.
Install Program – “SSMS-Setup-ENU.exe” /install /quiet /norestart

 Uninstall Program – “%programfiles(x86)%\Microsoft SQL Server\140\tools\binn\managementstudio\ssms.exe” /uninstall /quiet

 Download SQL Server Management Studio (SSMS)

 https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

 direct Links:

download Download SQL Server Management Studio 17.27
download Download SQL Server Management Studio 17.2 Upgrade Package (upgrades 17.x to 17.2)
Posted by
Labels: ,

SQL- How to determine which version and edition of SQL Server Database Engine is running


If you want to determine which SQL version and edition is running on your server/client you can use this complete Microsoft article:

 https://support.microsoft.com/en-us/help/321185/how-to-determine-the-version,-edition-and-update-level-of-sql-server-and-its-components

 Here they are some articles parts that I copied and pasted below:
<--------------->

To determine the version of SQL Server, you can use any of the following methods.

Method 1: Connect to the server by using Object Explorer in SQL Server Management Studio. After Object Explorer is connected, it will show the version information in parentheses, together with the user name that is used to connect to the specific instance of SQL Server.

Method 2: Look at the first few lines of the Errorlog file for that instance. By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files. The entries may resemble the following:
 
2011-03-27 22:31:33.50 Server      Microsoft SQL Server 2008 (SP1) - 
10.0.2531.0 (X64)

                March 29 2009 10:11:52

                Copyright (c) 1988-2008 Microsoft Corporation

Express Edition (64-bit)
on Windows NT 6.1  (Build 7600: )
This entry provides all the necessary information about the product, such as version, product level, 64-bit versus 32-bit, the edition of SQL Server, and the OS version on which SQL Server is running.

 Method 3: Connect to the instance of SQL Server, and then run the following query:

 Select @@version 

 Here it is a sample screenshot:

 Example of changed output

Method 4: Connect to the instance of SQL Server, and then run the following query in SQL Server Management Studio (SSMS):

 SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition') 

 Method 5

SQL Server Configuration Manager --> SQL Server Services --> Right-click SQL Server (PROFXENGAGEMENT), and click Properties.
Click on the Advanced tab.

Browse to Stock Keeping Unit Name and Version.

 https://support.cch.com/kb/solution.aspx/sw1873

 Method 6 This is a very well done sql version related blog:
http://sqlserverbuilds.blogspot.fr/

 I copy and paste a quickly summary:
 RTM (no SP)SP1SP2SP3SP4
↓ SQL Server vNext
     codename Helsinki		not yet released
 		    
↓ SQL Server 201613.0.1601.513.0.4001.0
or 13.1.4001.0
↓ SQL Server 201412.0.2000.812.0.4100.1
or 12.1.4100.1		12.0.5000.0
or 12.2.5000.0		  
↓ SQL Server 2012
     codename Denali		11.0.2100.6011.0.3000.0
or 11.1.3000.0		11.0.5058.0
or 11.2.5058.0		11.0.6020.0
or 11.3.6020.0		 
↓ SQL Server 2008 R2
     codename Kilimanjaro		10.50.1600.110.50.2500.0
or 10.51.2500.0		10.50.4000.0
or 10.52.4000.0		10.50.6000.34
or 10.53.6000.34		 
↓ SQL Server 2008
     codename Katmai		10.0.1600.2210.0.2531.0
or 10.1.2531.0		10.0.4000.0
or 10.2.4000.0		10.0.5500.0
or 10.3.5500.0		10.0.6000.29
or 10.4.6000.29
↓ SQL Server 2005
     codename Yukon		9.0.1399.069.0.20479.0.30429.0.40359.0.5000
↓ SQL Server 2000
     codename Shiloh		8.0.1948.0.3848.0.5328.0.7608.0.2039
↓ SQL Server 7.0
     codename Sphinx		7.0.6237.0.6997.0.8427.0.9617.0.1063
Posted by
Labels:

Linux - How to install MS SQL and .NET on several Linux distributions

Microsoft released SQL version that can be installed on Linux environment.

 Here they are more details with step by step installation guides

 Microsoft started with .NET and now SQL, I guess their mantra now is “if you can’t beat them, join them..."

 About .NET on linux you can quickly review this base Microsoft article:

 https://www.microsoft.com/net/core#linuxredhat

 https://docs.microsoft.com/en-us/aspnet/core/publishing/linuxproduction

 indeed about SQL on Linux:

CTP 1.4 (March 2017)

Supported platforms

PlatformFile SystemInstallation Guide
Red Hat Enterprise Linux 7.3 Workstation, Server, and DesktopXFS or EXT4Installation guide
SUSE Enterprise Linux Server v12 SP2EXT4Installation guide
Ubuntu 16.04LTS and 16.10EXT4Installation guide
Docker Engine 1.8+ on Windows, Mac, or LinuxN/AInstallation guide

More information can be found here:

 SQL Server on Linux Documentation

Posted by
Labels: ,

Manual - Data Science with Microsoft SQL Server 2016 free Microsoft e-Book

Data Science with Microsoft SQL Server 2016
Published: October 2016|90 pages

Data Science with Microsoft SQL Server 2016

Buck Woody, Danielle Dean, Debraj GuhaThakurta, Gagan Bansal, Matt Conners, Wee-Hyong Tok
The world around us—every business and nearly every industry—is being transformed by technology. SQL Server 2016 was built for this new world and to help businesses get ahead of today’s disruptions. With this free ebook, learn to install, configure, and use Microsoft’s SQL Server R Services in data science projects. R is one of the most popular, powerful data analytics languages and environments in use by data scientists|Less
Download


Posted by
Labels: ,

SQL - How to backup SQL database and run query from command line

If you need to backup SQL database and you have not SQL Management Studio you can do that through command line:

 SQLCMD -S SqlServerName\SqlInstanceName

 BACKUP DATABASE VeeamBackup TO DISK = 'C:\BackupDB2016.06.09.bak'

 quit

 If you need to run sql script you can proceed in this way:

 SQLCMD -S SqlServerName\SqlInstanceName -i C:\temp\sqlscript.sql -o

C:\temp\sqlscript.txt

 SQL management Studion download you can review this veeam article where I copied and paste text:

 https://www.veeam.com/kb1761
Posted by
Labels: , ,
Subscribe to: Posts (Atom)