Slipstream is a new installation method in SQL Server where SQL Server RTM + Service Pack + Hotfixes can be packaged together. This feature has been introduced from SQL Server 2008 SP1.
Just sharing the Supported Command Prompt Installation Scenarios below :
Installing, upgrading, or removing an instance and shared components of SQL Server on a local computer by using syntax and parameters specified at the command prompt.
Installing, upgrading, or removing a failover cluster instance.
Upgrading from one SQL Server edition to another edition of SQL Server.
Installing an instance of SQL Server on a local computer by using syntax and parameters specified in a configuration file. You can use this method to copy an installation configuration to multiple computers, or to install multiple nodes of a failover cluster installation.
This Command Prompt Installation is very helpful on Windows Server Core Machines where we have only Command prompt Available to see after logging to Server , as the Server Core installation eliminates services and other features which are not required for the support .
Below is how we should do to SLIP STREAM SQL 2014 and higher versions
Keep it ready with SQL Server 2014 media
Keep it ready with Needed SPs, CUs and Hotfixes that you want to Include
Keep all of the .exe files in one directory
There are two types of slipstream methods. 1) Simple Slipstream
2) Advanced Slipstream
Simple Slipstream (2014): This is command based installation which requires bit knowledge on installation parameters. These are the parameters we need to know before going with this installation.
– /Action =Install
/UpdateEnabled= True or 1 or False or 0
/UpdateSource=MU or UNC path or .\Myupdates
Advanced Slipstream (2014)
As you guys helped by copying the SQL 2014 RTM Media and SP3 + KB on Server . I have created a New Folder called UPDATES inside SQL 2014 Media and copied both SP3 , KB [ here I think we need to follow Proper Naming Format for .exe files like SQLServer20XX-kbxxxxxx-Architecture.exe ( Hotfix ) and SQLServer20XXSPX-KBXXXXXXX-x64-ENU.exe ( Service Pack ) ]
After performing above we need to do one more Important step is to Update DefaultSetup.ini File ( which exists in SQL 2014 Installation Media ) by providing the Location of Patches ( here we need to give Updates Folder Location )
In below Screen Shot we are Specifying the location where SQL Server setup will obtain checking in Product Updates TAB in Installation
UpdateSource = ” path of patches ” — It can be a local path or UNC shared path or if the patch folder is in Media folder it self we can give it like .\FolderName
Now Once we Start Installation by initiating setup.exe and going through next steps we can see the difference at Product Update TAB like below
By seeing this above can say that we have Embed all the 3 ( Media + SP + Hotfix ) to make SLIP STREAM Installation ..
Note : This SLIP STREAM Installation was introduced from SQL 2008 onwards but the process of doing Slip Stream in SQL 2008R2 is a bit more complicated compared with the latest releases
Currently I am running an older version of SQL Server 2008R2 and want to perform an in place upgrade to SQL Server 2014.
Upgrades from Earlier Versions to SQL Server 2014 :
SQL Server 2014 supports upgrade from the following versions of SQL Server:
SQL Server 2005 SP4 or later
SQL Server 2008 SP3 or later
SQL Server 2008 R2 SP2 or later
SQL Server 2012 SP1 or later
Now the first thing to do is to download SQL Server 2014 media directly from Microsoft. Once the downloading is finished, double click the file to begin the installation.
After launching the SQL Server setup, the SQL Server Installation Center window will display. Clicking on Installation TAB on the left side will display the different installation options. For this tip, we will click on “Upgrade from SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012”. This will launch the Upgrade to SQL Server 2014 application.
The Upgrade to SQL Server 2014 application lists the steps it will follow on the left side of the window. Enter the product key and click “Next >”.
Review the license terms, click on “I accept the license terms”, and then click “Next >”.
The Global Rules TAB will check if there any Rules failed to stop this Installation or not
Here I have SLIP STREAMED SQL 2014 Media with SP3 and HotFix So, that we can eliminate Multiple Reboots of Server and also SAVING TIME. From below ScreenShot we can observe the difference of Include SQL Server Product Updates checkbox ENABLED ( Service Pack 3 and HotFix which was added to Installation Media )
The upgrade application will download, extract and install files needed to carry out the installation process. Click on “Next >” to continue.
Select the SQL Server instance to modify. Click on “Next >” to continue.
The next step is the feature selection. In the example shown below, all of the features will be upgraded. Click on “Next >” to continue.
The Instance Configuration screen allows the installer to specify the instance ID. This screen will also display other installed instances. In this example, the MSSQLSERVER instance on SQL Server 2008R2 will be upgraded to SQL Server 2014.
The Server Configuration screen shows the services to be installed, the service account name and the Startup Type. If you are installing SQL Server 2014 on a device with limited resources then the Startup Type should be set to Automatic (Delayed Start) instead of Automatic. Click on “Next >”.
The Full-text Upgrade screen provides three Full-Text upgrade options (import, rebuild and reset).
The Feature Rules screen will execute a rule check to make sure the upgrade process should successfully.
The Ready to Upgrade screen displays all of the features and prerequisites to be installed or upgraded. At this point, the installer can still go back to make changes or quit the process. Click on “Upgrade” to begin the upgrade of the SQL Server 2014 components.
The upgrade progress bar tracks the status of the upgrade.
Upon completion of the installation, click on “Close” to exit the Upgrade to SQL Server 2014 application.
As you will be instructed that you will eventually need to restart your computer for the changes to take place
Verify that all Features show as Succeeded in the Status column, then click Close.
Finally, restart your computer. Once your computer is restarted, Connect to your Instance and see the Upgraded SQL Server 2014.
During daily operations with SQL Instances, DBA/Application teams frequently encounter SQL Connectivity issues while connecting from the applications. In this blog, I am covering few checklist steps to troubleshoot SQL Connectivity issues.
When a client connects to SQL Server, it goes through authentication and authorization stages. If the connectivity issue is because of authorization, client will be able to establish a physical connection with SQL Server, but the issue would be while authorizing with SQL Server like Login errors because of permission issues etc. If the issue is because authorization, SQL Server Error log is the starting point for further troubleshooting.
Very important step in addressing SQL Connectivity issues is isolating which component is causing the issue: Broadly, we can classify the the connectivity issues in below categories:
Network issue,
SQL Server configuration issue.
Firewall issue,
Client driver issue,
Application configuration issue.
Authentication and logon issue.
Some of the common error messages thrown by the application while connecting to SQL Server are:
Error 1:
Test connection failed because of an error in initializing provider. [DBNETLIB] [ConnectionOpen (Connect ()).]SQL Server does not exist or access denied.
Error 2:
[Microsoft] [SQL Server Native Client 11.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
[Microsoft] [SQL Server Native Client 11.0] Login timeout expired
[Microsoft] [SQL Server Native Client 11.0] A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information, see SQL Server Books Online.
Error 3:
An error has occurred while establishing a connection to the server. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)
Error 4:
[Microsoft] [SQL Server Native Client 10.0] TCP Provider: An existing connection was forcibly closed by the remote host.
CHECKLIST STEPS:
1.Get the details of the servers involved in the connectivity.
How many servers are involved?
Is the application a web based/thick client application?
Is the client connecting to application server which then connects to SQL Server?
Is the connectivity issue happening only on one client box or multiple clients are not able to connect as well?
Collect server names and IP addresses of all the servers involved.
2. Check if the server is SQL Server is reachable. Simple test is to perform network ping to check if the server is reachable.
Ping <SQLServername> or Ping <IP address of SQL Server> If Ping itself fails to locate the SQL Server box, this indicates that client is not able to locate SQL Server machine itself and we need Network team assistance to debug this further.
3.Check if the server is SQL Server is reachable. Simple test is to perform network ping to check if the server is reachable.
4.Local connectivity on SQL Server box works on Shared memory protocol. Next step is to check the protocols enabled on SQL Server and ensure that TCP/IP and Named Pipes protocols is enabled for remote connections:
5. To check if the client is able to reach the SQL Server port or not: We can use telnet tool to test if the SQL Server port is listening to incoming connections.
For example: if the SQL Server Host name is: SQLprod and the port on which SQL is listening is: 1433 telnet sqlprod 1433 If the client box is not able to reach the port on SQL is listening on, we get the error as mentioned below:
6. If the telnet fails as well, this is a clear indication that some device (Firewall) is blocking the port. To verify if the port is blocked or not, we can use the tool portqueryUI. PortqueryUI tool can be downloaded from: https://www.microsoft.com/enin/download/details.aspx?id=24009
If you see the output as Filtered, this indicates that the port is blocked. For example, if the SQL hostname is aoindia and SQL Port is 1433:
Next step is check with Windows/networking team to allow the port. Ensure that the Inbound and outbound rules are created to allow the connection for SQL port at operating System level.
7. Once the port is allowed, run the PortqueryUI program again and ensure that the status shows as LISTENING.
Telnet tool can be used only to check if the TCP port is blocked or not. But the PortQueryUI tool can be used for both TCP and UDP to check if port is blocked.
8. Collect the connection string details of the application. If the application is web based, web.config file will generally have the connection string details. Gather the following details: a. Data Source b. Initial Catalog c. Integrated Security d. Provider For example: Provider=SQLNCLI10; Server=myServerName\theInstanceName;Database=myDataBase; Trusted_Connection=yes; Few important details to be studied from connection string are: Type of authentication used (Windows/SQL), provider used (OLE DB or ODBC), Data Source field.
9. To isolate if the issue is caused by application or not, simple test would be to connect to the SQL instance using SQL management studio from the client box. But in most scenario’s, SSMS tool may not be installed on the client machines. In this case, we can create a universal data link tool (UDL) to test the connectivity. From the client machine, create a text document and rename the extension as .udl. For example: test.udl Based on the provider used by the application (Microsoft OLE DB for SQL Server), or SQL native Client provider, select the appropriate provider list in udl file and click on next.
Enter the SQL Server name and default database and click on test connection:
If the connection succeeds through udl, this indicates that some connection string configuration is incorrectly formed in the application.
Try to simulate the connectivity issue using the same settings mentioned in the connection string and check if you are able to reproduce the issue or not. If the connectivity works from UDL, then check with application team if the connection string can be replaced with the one created by udl. [Open the udl file in notepad to get the dynamically generated connection string]
10. If the issue is client provider specific, check if different provider can be used to connect to SQL. From udl file, select different providers of SQL and check connectivity works or not. For example, if the issue is specific to Microsoft OLE DB for SQL Server, check if SQL native client can be used.
11. If the application connection string is using data source name (commonly known as DSN), then create a user DSN using ODBC Data Source Administrator tool (odbcad32) and check the connectivity.
To launch ODBC administrator tool in 32-bit mode: C:\Windows\SysWOW64\odbcad32.exe
12. On the client machine: at the below registry location, Last SQL Server connect entry is stored: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client
Clear the entries listed for lastConnect entry for the SQL Server in question and delete these entries. [Ensure that the registry backup is taken before making any modifications]
13. Check if there are client side aliases present using SQL Server Client network utility:
To launch the tool:
In alias tab, check if there are any orphaned alias entries. Take the backup of the aliases present and delete the entries if there are any aliases created for the SQL Server instance we are troubleshooting.
To launch the SQL Client config tool in 32-bit mode: C:\Windows\SysWOW64\cliconfg.exe
14. Check if you are able to test the connectivity by forcing the protocols like TCP and Named pipes.
By forcing the protocol, if the connectivity works, check with the application team if the connection string can be modified to force the protocol in the data source field.
To check if the connection established with SQL is going over TCP/named pipes: execute the below query:
select session_id, net_transport from sys.dm_exec_connections
If no modifications can be done on connection string and if the connectivity works by forcing a protocol, check if a client side alias can be created. Example: If the connectivity works only on Named pipes protocol, create a named pipe alias using SQL Server client network utility:
15. Check if you are able to connect to the instance by forcing the SQL port. For example:
If the connectivity works while forcing the port number, this indicates an issue with SQL Browser. SQL Browser is responsible to resolve the Instance name with port registered on SQL Server. Then the option is to check if SQL UDP browser port 1434 is blocked or not and troubleshoot browser issues
16. Check if the connectivity works by specifying the IP address of the SQL Server instead of the hostname. For example:
If the IP address of instance hosting SQL is 10.16.17.18 and 5223 is the port on which SQL is listening on:
If the connectivity works by hardcoding the IP address in the Server name, then as a temporary resolution, create a host file entry with the Server name and the IP address mapping to avoid DNS name resolution and involve the DNS team for name resolution issues.
17. Even after trying out the above steps, if the connectivity issue is still not resolved, next step is to collect the Network traces using Network Monitor tool.
Hope the above steps mentioned will help you in troubleshooting SQL Connectivity issues.
Below screenshot summarizes an approach to isolate SQL high CPU issues:
Below are the CHECKLIST POINTS which Microsoft Recommends :
1. From Windows task manager check the overall CPU utilization. Collect the details of number of logical processors present on the box.
2. From task manager, check the SQL Server process CPU utilization. Is the SQL CPU constantly above 70%?
3. Gather the following details:
How do you typically become aware that CPU is the bottleneck?
What is the impact of the problem? Are there particular errors that your user application?
When did the problem first occur? Are you aware of anything that changed around this time? (Increased workload? Change in table size? App upgrade? SQL upgrade?)
Can you make new connections to the server during the problem period?
How long did the problem last? Have you been able to do anything that seemed to help resolve the problem?
What system-level symptoms have you observed during the problem periods? For example, is the server console slow or unresponsive during the problem periods? Does overall CPU usage increase? If so, what %CPU is observed during the problem? What is the expected %CPU?
4. If the High CPU is causing by process other than SQL Server process (sqlservr.exe) engage the team which takes care of that process.
5. Open Perfmon and add the below counters:
Process (sqlservr):
% Privileged Time
% Processor Time
% User Time
Processor
% Privileged Time
% Processor Time
% User Time
6. If Processor Privileged time is above 25%, engage the Windows team
Processor Time = Privileged Time + User Time.
7. Confirm that SQL is consuming high CPU on the box by validating the below counters:
Process (sqlservr): % Privileged Time
% Processor Time
% User Time
Divide the value observed with the number of logical processors to get the CPU utilization by SQL Process.
If (Process (sqlservr)% Privileged time/No of Procs) is above 30%, ensure that KB 976700 is applied for Windows 2008 R2
This step, gives an indication of if SQL Server is causing the high privilege time on the server. If SQL privilege time is high, as per the above calculations, engage the Windows team.
8. Check the below configurations from sp_configure and make sure they are as per the best practice recommendations:
Follow KB 2806535 for Max DOP recommendation settings.
9. If you are unable to connect to the SQL instance locally using SSMS, try connecting to SQL instance using Dedicated Admin connection (DAC) using:
ADMIN: Servername
10. Get the top 10 queries consuming High CPU using below query:
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time desc
11. Check the wait type of the queries returned from the above output. If CPU is the major bottleneck, most of the sessions will have the below waits:
SOS_SCHEDULER_YIELD
CXPACKET
THREADPOOL
If most of the queries are waiting on CXPACKET, revisit sp_configure setting for “Max degree of parallelism” and “Cost degree of parallelism” and check if they are set as per best practice recommendations.
12. Run the SQL Standard report to get the list of Top CPU queries:
Right Click on the instance, go to reports> Standard reports
Check the Top CPU queries obtained in the report. Compare the report with the Top CPU consuming queries obtained from above step.
13. Once the top CPU queries are identified, get the list of all the SQL tables involved using statement_text and command_text column output obtained from step 10.
Check the following:
Index Fragmentation on the top CPU driving tables
Last Statistics updated information
If the Index fragmentation is above >30 %, rebuild the index. If the statistics are not updated on the table, update the statistics.
From the Top CPU queries, if there are only few set of tables which are responsible for high CPU, share the tables list with the application team and share the statistics report and fragmentation report.
Check if there are any select queries which are causing high CPU, check with application team if they can be stopped temporarily on high OLTP servers.
14. Once the database maintenance activity is performed (like Index rebuild and Stats update), if SQL is still using high CPU, execute the query mentioned in Step 10.
Check if the Top CPU query has changed. If the query has changed, then follow the action mentioned in Step 13. If the query is still the same, then go to next step.
15. Collect the estimated execution plan of the top CPU consuming queries involved using:
Query 1: Get the Top CPU consuming session ID’s from the output of query mentioned in step 10.
Collect the Plan handle and SQL handle information from below query:
select sql_handle,plan_handle from sys.dm_exec_requests where session_id=<session_id>
Get the text of the query:
–replace the SQL Handle with the value obtained from above query.
select * from sys.dm_exec_sql_text (sql_handle)
Get the estimated execution plan of the query:
–replace the Plan handle with the value obtained from above query.
select * from sys.dm_exec_query_plan (plan_handle)
Query 2: The below query captures the Total CPU time spend by a query along with the plan handle. Plan handle of the query is needed to get the estimated execution of the query.
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
16. Share the estimated execution plan obtained with the application team.
Check for the operator which has high Cost.
Check the Indexes used for the operator and number of rows estimated.
Revisit the statistics and Indexes on the table reported for the operator which has high cost and make sure that there are no stale statistics.
Check if the estimated execution plan is recommending any new index to be created. If the plan reports an index recommendation, share the missing index details with the Application team.
17. “Convert Implicit” function in execution plan can result in High CPU utilization of SQL Server as well.
Review the execution plans of the High CPU consuming queries and review the Operator with High Cost and check if there are any Convert_Implicit function is called.
In the above screenshot, CONVERT_IMPLICIT function is implicitly converting the column “NationalIDNumber” to integer whereas in the table definition its defined as nvarchar (15). So, share the report with application team and ensure that the data type passed and stored in database are having the same data type.
18. Run the missing index query on the database which has reported high CPU and check if there are any missing indexes recommendations. Share the Index recommendation report with the Application team
19. Tune the Top CPU consuming queries with the Database Engine Tuning Adviser to see whether database engine recommends index recommendation/statistics creation.
20. Check for Compilations/Re-Compilations in SQL Server:
The supported version for the Transactional replication and the rules for the supported versions are pretty simple and clearly written on the Replication Backward Compatibility.
Rules for the Transactional Replication are :
–> A Distributor can be any version as long as it is greater than or equal to the Publisher version (in many cases the Distributor is the same instance as the Publisher.
–> A Publisher can be any version as long as it less than or equal to the Distributor version.
–> Subscriber version depends on the type of publication:
>> A Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example: a SQL Server 2012 (11.x) Publisher can have SQL Server 2014 (12.x) and SQL Server 2016 (13.x) Subscribers; and a SQL Server 2016 (13.x) Publisher can have SQL Server 2014 (12.x) and SQL Server 2012 (11.x) Subscribers.
>> A Subscriber to a merge publication can be all versions equal to or lower than the Publisher version which are supported as per the versions life cycle support cycle.
In another way which is Simple and in short we can say
In Short and Simple T-replication supported VERSIONS
Coming to our Environment the Client is asking to Setup Hight Availability between SQL 2012 with Read\Write operation and SQL 2016 with Read Operations
Initially we are thinking to configure AlwaysOn but that was not something would work out due to licensing issues of the version which the client had available. Additionally, the need for the data was near real-time hence we can’t do the traditional backup/restore or log shipping( As the Readonly mode is not supported because of two mixed Versions ).
After carefully thinking we decided to go with Replication .So, the setup will looks like :
Our client was using SQL Server 2012 as a Publisher. We can use either SQL Server 2012, 2014, 2016, 2017 or 2019 as a Distributor and SQL Server 2008/R2, 2012, 2014, or 2016 as a Subscriber.
