SQL Server Consolidation Frequently Asked Questions and Answers

The goals of the SQL Server Consolidation project are:
– Minimize licensing cost by consolidating servers.
– Reduce operational and maintenance costs.
– Modernize SQL Server version.
First step of the consolidation is to collect data(current situation), with regards to the applications that are dependent on SQL Server databases and the latest version which they support and process and effort of migrating applications to use new SQL Server on different system. To get this information, will need to involve application team or vendors, asking them right questions.

Some of the high level and common questions asked related to SQL Server Consolidation include:
– What is the latest SQL Server version this application supported by application or Vendor? like SQL Server 2008 R2, 2012, 2014, 2016.
– Is the application using any specific feature that is dependent of the Edition? Like transparent data encryption or online reindexing?, etc
– If possible, what are the recommended hardware values for this application? for CPU, Memory and disk IOPS?
– If we migrate to a newer version of SQL Server, do we also need a change in the application? like a service pack or update?
– If we migrate the database to a newer version, are there any backward compatibility requirement? Examples could be older SSIS packages, old syntax requiring us to run in an old compatibility mode.
– If we decide to place the database in a high available environment using Always ON, is there any specific requirement from the application side?
– Is there any specific recommendation or pointer for running the application in a virtual environment?
– Is this application using external code in the database in the form of CLR?
– Are there any specific database properties that we need to know of? Examples could be cross database chaining, read committed snapshots, etc.

Any considerations for or additional data needed before moving with consolidation?
– Performance baseline of the existing servers and application response times. This can be achieved by running performance monitor counters for a week or so and prepare some graphs showing the current resource usage.
– Although it is always preferred to go with latest SQL Server version, but due to application requirements, it is not possible in which case, we can go with multiple SQL Server instances installed side by side and each running different version. It is important to keep in mind that this will cause additional complexity to the consolidation as we need to keep in mind some challenges related to having a common collation suited for the databases, performance of one instance can impact other instances on the server, having different versions will need separate license for each version, shared components failing from time to time, OS patching or other tools updated on the server can impact other instances or applications, etc.

Can all older databases be consolidated?
Technically yes, but depends on migrating to which version of SQL Server to which version, but we can use stage servers to migrate first and then to final version. Important thing is to check with the application team or vendors regarding the support to the SQL Server version and edition. From operations side, we can run upgrade advisor or upgrade assistent to get analysis of the current database and see which objects will be impacted due to migration. It is always advised to run tests in a UAT environment just to make sure the application is working as expected.

Is is suggested to implement high availability? Is it feasible or beneficial without causing too much additional admin or support overhead?
Having High Availability like AlwaysON Availability groups or other technologies is always a good practice, and this will also depend on the SLA. We could create set of databases, the ones that need HA and the ones that don’t High availability and may be we can consider have 2 servers, 1 standalone with Standard Edition and 1 cluster with Enterprise edition. The SLA will determine how much time you have to recover incase of an error. Having HA allows you to recover in minutes, not having it might force you to recover in hours and doing manual work, it would be good to align the strategy with the business need. There are small applications that can be down for 8 hours without causing a big issue, for these you could just resort to take backups in a remote location and also to tape and then use a RAID with hot swapping, but if the application can’t be down for more than 30 minutes, then HA might be an obligation.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Updates About SQL Server 2016 on Linux

It is an exciting news which already every one knew is Microsoft is bringing SQL Server on Linux—another towards making SQL Server industry-leading data management and business analytics platform, for any data, to run any application, anywhere. SQL Server support for linux will be built on SQL Server 2016, so SQL Server on Linux gives the users the power to build and deploy intelligent applications on a single data management and business analytics platform. The advantage of SQL Server’s low total cost of ownership, supporting existing tools and skills and new innovations battle-tested in the cloud, will make the SQL Server more interesting. Microsoft trying to make it easier for us to work with data of any type, size, and speed using the tools, languages, and frameworks we want to use in a trusted cloud, hybrid, or on-premises environment. It’s another step to make SQL Server simpler and more accessible. If you want to have a quick look on how SQL Server works on linux, then see the video. However, still there are many outstanding questions from community regarding which SQL Server features will not be supported when released, which features that are planned to be supported , licensing information, performance difference of running on Server 2016 on windows vs Linux, remote management support with PowerShell, availability of pre-built test VMs to download, Azure (in)compatibility, which flavors of UNIX/Linux will be supported, and comparison/contrast with the other popular database products normally used on Linux. Also, it would be interesting to know how the High Availability, Replication, Always On features work with SQL Server on linux.

Recent updates from Microsoft community via blog posts suggest that SQL Server on linux is already developer and is under testing and now also the Microsoft product support team is getting involved with the product and looks like few support team members got access to the setup media and were able to successfully installed SQL Server on linux without any issues and after using it a bit and reading about linux they feel that SQL Server on Linux is an attainable goal. As of now, the testing is going smooth without much issues, which actually is not good because without hitting any issues during initial internal testing, the product will not improve much. If many issues are identified by users or customers, that will bring a bad impression about the product, which in every case Microsoft wants to avoid. Microsoft support team is invaluable in helping improving the product because they have enormous experience is troubleshooting many issues over a period of time and they can try test simulating different ongoing issues which are encountered on Windows to linux and see the behavior and in the process of troubleshooting the problems, they will need to use different tools and the testing will bring support for those tools as well.

So far from the preview videos and blog posts we can see that we can use SQL Server on linux through SQLCMD command that we install on linux environment, so we are to reply on commands and no GUI support yet, which many SQL Server DBAs may not like, as we are very much used to GUI. However, after the release, with growing requests from DBAs and community, we may get a GUI tool as well. Just like any other product, it is expected to have many issues initially, but thing will improve with time. When SQL Server was initially developer, it was not great and was not even in competition with Oracle, MySQL, DB2, etc, but in 15 years, SQL Server was able to beat the competition with DB2, MySQL, Sybase and few other RDBMS products and giving a tough competition to Oracle as well. Also, with new cloud competition heating up, support for SQL Server in linux has become inevitable for Microsoft to be in completion with other RDBMS products.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Details about EXECSYNC Wait Type in SQL Server

SQLServerF1

When a query is submitted to SQL Server for execution, SQL Server will generate a optimal query plan based on the statistics and indexes. Any query will need to read the data from memory or write to memory and in between it has to read from disk or write to disk. In this whole process, there are CPU, Memory, Disk and Network resources utilized and depending up on the amount of data involved and the resource speed, SQL Server will have to wait for the task to be completed by the respective resource and until then SQL Server may end up in waiting for it to complete. SQL Server provides with the wait type it is waiting on, to help us identify where it is waiting on, so that we can look into improving the speed of the processing with by improving the hardware or by tuning the queries. There are many wait types defined in SQL Server and can be found by querying sys.dm_os_wait_stats DMV.

Recently we encountered an issue, where we observed EXECSYNC wait type while running a reindex job. Interestingly, this wait type was encountered when we tried to stop the job, as it was running long and was causing blocking to user queries, but the job did not stop immediately and was waiting on this wait type EXECSYNC. This is not a popular wait type, so checking the description about this wait type, found this Occurs during parallel queries while synchronizing in query processor in areas not related to the exchange iterator. Examples of such areas are bitmaps, large binary objects (LOBs), and the spool iterator. LOBs may frequently use this wait state. If we check from sys.sysprocessess, we can notice that the state of the request was in KILL/ROLLBACK state. We monitored and few minutes, the rollback of the reindex job completed and the wait type and the session ended. Up on further research, found that this wait type can be ignored, instead we need to focus on the reindex job on why it is running long and need to run it during off hours when there are least number of users using the database.

For more information regarding wait types in SQL Server, they are mainly categorized into below types.
Resource waits – Resource waits occur when a worker requests access to a resource that is not available because the resource is being used by some other worker or is not yet available. Examples of resource waits are locks, latches, network and disk I/O waits. Lock and latch waits are waits on synchronization objects
Queue waits – Queue waits occur when a worker is idle, waiting for work to be assigned. Queue waits are most typically seen with system background tasks such as the deadlock monitor and deleted record cleanup tasks. These tasks will wait for work requests to be placed into a work queue. Queue waits may also periodically become active even if no new packets have been put on the queue.
External waits – External waits occur when a SQL Server worker is waiting for an external event, such as an extended stored procedure call or a linked server query, to finish. When you diagnose blocking issues, remember that external waits do not always imply that the worker is idle, because the worker may actively be running some external code.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

.NET Framework execution was aborted Error in SQL Errorlog

SQLServerF1

There are many error messages defined in SQL Server which are returned when certain invalid operation is performed and based on the task performed, appropriate error number, severity and brief error message is sent to the application which run the query like SSMS or SQLCMD. Unfortunately, not all the error messages are understood easily and can become difficult to identify the cause and to find a solution. Especially errors related to performance or memory can be even difficult to diagnose or troubleshoot. One of the error, which I worked on recently was related to .NET Framework execution was aborted by escalation policy because of out of memory. Initial look at the error message, it given an idea that the error message was result of memory problem, as it is “out of memory” in the error message.

Error: 6532, Severity: 16, State: 49.
–> .NET Framework execution was aborted by escalation policy because of out of memory.
[WARNING] System.Threading.ThreadAbortException: Thread was being aborted.
[WARNING] System.Threading.ThreadAbortException:
[WARNING] at System.Data.SqlTypes.SqlChars.get_Value()
[WARNING] at System.Data.SqlServer.Internal.XvarWlobStream.GetCharArray()
[WARNING] at System.Data.SqlServer.Internal.XvarWlobStream.GetString()
[WARNING] at System.Data.SqlServer.Internal.XvarWlobStream.GetSqlString()

Other than the above error, you may also end up seeing some error messages like below.
– AppDomain 2 (xxx) is marked for unload due to memory pressure.
– AppDomain Trio.dbo[runtime].xx was unloaded by escalation policy to ensure the consistency of your application. Out of memory happened while accessing a critical resource.
– Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure.
– Additionally, you may see the above error messages with SQL Server 2008 features which use SQL CLR internally, like DMF (Declarative Management Framework) and spatial data types.

These errors can occur when SQL CLR stored procedures, user defined functions, user defined data types, or user defined aggregates, are used against SQL Server especially on 32 bit SQL instance.
– AppDomain 2 (xxx) is marked for unload due to memory pressure – This error message can be mostly informational message indicating that SQL CLR is responding to the memory pressure on the system. If this is an intermittent error message and is not affecting the execution of SQL CLR objects, the message can be ignored.
– For other errors, ensure that .NET Framework 2.0 is up to date with the latest available patch. Ensure SQL CLR object uses any assemblies other than those documented are not used. SQL Server 32 bit instance virtual memory is limited to 4GB. If we use SQL CLR extensively with a SQL Server 32 bit instance and experience one or more of above errors, then the best thing would be to migrate to a 64 bit SQL Server. This will allow SQL CLR to access more virtual memory and may prevent the above errors from happening in your environment. Other than that when we develop custom SQL CLR applications, we need to use SQL CLR memory carefully, like avoiding caching large amount of data using objects such as DataTables, If required use TSQL to retrieve the data, try avoid using static variables in your code to store large objects, and try create objects as late, and release them as early, as possible. If issue still persists, then check if there are any known issues and patch SQL Server to latest version.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

New Changes to SQL Server 2016 Installation

SQLServerF1

With every new release of SQL Server, there are many changes that comes like new features, enhancement to existing features, etc. Important thing which DBAs look is SQL Server installation, as we need to first get the downloadable and then install the software to be able to explore the new features or play with the product and some enthusiastic users even want to find bugs and report to the product team. Over a period of time, since SQL Server 2000, there have been many changes to the way the installation of SQL Server in standalone or cluster environment. The installation wizard changes were also inspired by the newer versions of operating systems, like Windows Server 2008 lead to different experience of the installation wizards. In SQL Server 2005, install shield was used, and starting SQL Server 2008 R2, installation center has been introduced with various different options and there were major changes when it came to cluster SQL installation. In SQL Server 2005, cluster install was started from one node and automatically it used to install on both nodes using task scheduler, but there were lot of problems with that, so in SQL Server 2008, the cluster installation was changed significantly, where we install on one node and remaining nodes we perform Add node operation, this made life easier with cluster installs.

Now coming back to changes in installation wizard or steps with SQL Server 2016 are mentioned below.
– In previous versions of SQL Server, in the feature selection page, there were many options which include Database Engine services and under that there were sub options for replication, full-text, Data-quality services. Starting with SQL Server 2016, we now have a new option under database engine services, which is PolyBase Query Service for External Data. If you are wondering what is this new feature, it enables using integrated querying across Hadoop data and SQL Server data using standard T-SQL statements. When we select this feature to be installed along with database engine, there will be additional checks performed by the installer which include checking for Microsoft .NET Framework 4.0, and if Oracle Java SE RunTime Environment (JRE) version 7.51 or higher is present or not. If this are not present, then the installation will fail. Also, after the successful install you will find three new databases DWConfiguration, DWDiagnostics, and DWQueue created by default if we choose to install PolyBase Query Service for External Data.

Another change that comes with SQL Server 2016 installation is, we can now select number of tempdb files when want to create along with the SQL Server installation. Previously this option was not present and DBAs had to manually create additional data files for tempdb. Also, there is a recommendation on number of tempdb files to be created based on number of cores on the server. Primarily, there are the tow main changes, other than this there are changes to licensing agreement and feedback to be sent to Microsoft, which is now an option which we cannot opt out off.

Hope this was helpful.

This is applicable for below versions of SQL Server

Microsoft SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Limitations of using Stretch Database in SQL Server 2016

SQLServerF1

New feature which was introduced with SQL Server 2016 is Stretch Database which migrates our historical data transparently and securely to the Microsoft Azure cloud. Stretch Database provides some benefits to the users, but also has its own limitations which make it less likely to be used as of now, unless Microsoft comes up with significant improvements. Some of the benefits to decide on using SQL Server 2016 Stretch Database feature are, Provides cost-effective availability for cold data(historical data which is not accessed much, but still available to support user queries from Azure SQL database). Using this feature does not require any changes to the applications, this feature takes care of it internally and transparently. Moving cold or not frequently used data to Azure SQL database will reduce the maintenance efforts on the production data like less times required for backups, indexing statistics updates, etc.

Although this is great feature and very helpful for many organizations, but at this time of SQL Server 2016 RTM release, there are lot of restrictions which make it less likely to be used in many environments, because stretch database cannot be used with many widely used features. Some of the limitations are mentioned below.
– One of the difficult restriction which stops the usage of stretch database is that uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints in the Azure table that contains the migrated data.
– We can’t UPDATE or DELETE rows that have been migrated to Azure SQL database cloud, or on the rows that meet eligible criteria for migration, in a Stretch-enabled table or in a view that includes Stretch-enabled tables.
– We are not allowed to INSERT rows into a Stretch-enabled table over a linked server.
– We cannot create an index for a view that includes Stretch-enabled tables.
– Filters on SQL Server indexes are not propagated to the remote table.
– Some of the Table properties limitations for stretch database include, Tables that have more than 1,023 columns or more than 998 indexes, FileTabless or tables that contain FILESTREAMM data, Tables that are replicated, or that are actively using Change Tracking or Change Data Capture, Memory-optimized tables, etc.

– Data types that are not support in a table to be part of stretch database include, text, ntextt and image
timestampp, sql_variantt, XML, CLR data types including geometry, geographyy, hierarchyidd, and CLR user-defined types
– Column types that are not supported with stretch database include COLUMN_SETt, Computed columns, Constraints,
Default constraints and check constraints, Foreign key constraints that reference the table. In a parent-child relationship (for example, Order and Order_Detail), you can enable Stretch for the child table (Order_Detail) but not for the parent table (Order).
– Indexes which are not supported on stretch database include
Full text indexes, XML indexes, Spatial indexes, Indexed views that reference the table

Local database. The on-premises SQL Server 2016 Release Candidate (RC3) database.
Remote endpoint. The location in Microsoft Azure that contains the database’s remote data.
Local data. Data in a database with Stretch Database enabled that will not be moved to Azure based on the Stretch Database configuration of the tables in the database.
Eligible data. Data in a database with Stretch Database enabled that has not yet been moved, but will be moved to Azure based on the Stretch Database configuration of the tables in the database.
Remote data. Data in a database with Stretch Database enabled that has already been moved to Azure.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 
1 2 3 35