SQL Server Memory Allocation
This article covers SQL server memory allocation best practices to follow while allocating memory.
By default, SQL Server can change its memory requirements dynamically based on available system resources.
SQL Server is designed to use all the memory on the server by default.
The reason for this is that #SQL Server cache the data in the database in #RAM so that it can access the data faster than it could if it needed to read the data from the disk every time a user needed it.
Changing SQL Server's Max Server memory is an online option – you don't need to restart SQL Server.
Though when you do make the change, you can and likely will cause data or procs to leave their caches so things could be a slight bit slower for a short while after you run it.
SQL Server will always give you better performance because the query is executed on the server.
Access on the back-end won't help because your client application will need to pull all the data from the tables, and then perform the join locally.
SQL Server has better indexing options.
'Max server memory' ensures that SQL Server buffer pool will not use more memory than specified in 'max server memory'.
So, SQL Server can use less memory than the value of 'max server memory' and other services can use the remaining memory.
You can monitor memory use at the database level as follows:
1. Launch SQL Server Management Studio and connect to a server.
2. In Object Explorer, right-click the database you want reports on.
3. In the context menu select, Reports -> Standard Reports -> Memory Usage By Memory Optimized Objects.
Perfmon Counters for memory usage
This article will guide you on how to use Perfmon Counters for memory usage. You will also learn some commonly used performance counters which can be used to identify #SQL server memory pressure.
To detect a memory leak using #Performance #Monitor, monitor these counters: The Memory/Available Bytes counter lets you view the total number of bytes of available memory. This value normally fluctuates, but if you have an application with the memory leak, it will decrease over time.
A system performance report is a part of Performance Monitor that details the status of local hardware resources, system response times, and processes on the local computer. You can use this information to identify possible causes of performance issues.
SQL performance tuning consists of making queries of a relation database run as fast as possible.
To Check Detailed #Memory #Usage with Performance Monitor:
1. To open up Performance Monitor type: perfmon into the Run window (Windows Key + R).
2. In the window that comes up, click the Performance Monitor under Monitoring Tools in the left pane.
Reporting services configuration manager
This article will guide you on how to setup reporting services configuration manager. Report Manager is Web based interface that can be used to perform different tasks such as. Administer the Report Server,View the Reports and Manager the Report, and so on. You need to have the URL to access the Report Server by using Report Manager interface.
SQL Server Reporting Services (#SSRS) is a reporting #software that allows you to produce formatted reports with tables in the form of data, #graph, images, and charts. These reports are hosted on a server that can be executed any time using parameters defined by the users.
Report server items are always stored in libraries or in a folder within a library.
When you access the SharePoint site, you see the Browse page and the Library Tools tab.
In #Reporting Services, URLs are used to access the Report Server Web service and the web portal. A test link of the URL appears on page when you define the URL so that you can immediately confirm that the settings you specified result in a valid connection.
To connect to Reporting Services Configuration Manager:
1. Click Start, then click #Programs, then click #Microsoft #SQL Server, then click Configuration Tools, and then click Report Server Configuration Manager.
2. The Report Server Installation Instance Selection dialog box appears so that you can select the report server instance you want to configure.
Install SQL Server 2019 on Linux
This article will guide you the process to install #MSSQL Server 2019 on #CentOS 7 and Ubuntu 16.04.
SQL Server 2019 #Developer is a full-featured free edition, licensed for use as a development and test database in a non-production environment.
SQL Server 2019 allows us to integrate data from structured and unstructured data sources.
We can now process diverse big data and relational data sources using Transact-SQL from SQL Server using PolyBase.
We can see below PolyBase support external databases.
SQL Server Express can be used in production (beware of the limitations, like the 10GB cap), but according to this link Express is available for Linux. SQL Server Express is available to use in Production.
Starting with SQL Server 2017, SQL Server runs on Linux. It's the same SQL Server database engine, with many similar features and services regardless of your operating system. It's the same SQL Server database engine, with many similar features and services regardless of your operating system.
To run a SQL script in #Linux terminal:
1. Open Terminal and type mysql -u to Open the MySQL command line.
2. Type the path of your #mysql bin directory and press Enter.
3. Paste your #SQL file inside the bin folder of mysql server.
4. Create a #database in MySQL.
5. Use that particular database where you want to import the SQL file.
Configure and Use Microsoft SQL Server Management Studio
This article will guide you on how to setup SQL server management studio. SQL Server Management Studio is a free #Windows #application to configure, manage, and administer Microsoft SQL Server (#MSSQL).
SSMS is available only as a 32-bit application for Windows.
To get SQL Server Management Studio:
1. Go To Start Menu>Programs>Microsoft SQL Server Tools 18> Microsoft #SSMS 18.
2. Below 'Connect to Server' screen will appear.
3. Server Name defaults to the name selected while installing MS SQL server.
4. Server type: This is an option to select one out of four available MS SQL services option.
To Enable remote connections to your SQL Server:
i. Open #SQL Server Management Studio.
ii. Right-click your server's name and select Properties.
iii. Tick the checkbox Allow remote connections to this server.
iv. Select OK.
MSSQL replication setup
This article will guide you on how to setup #MSSQL replication and fix related errors. The MSSQL replication is a bit long procedure to configure replication.
Through embedded SQL database replication technology, #SQL Server supports three types of #replication: snapshot, transactional, and merge replication.
#Snapshot replication sends the entire data set to the subscriber.
Transactional replication only sends modifications to the data.
Merge replication items are modified at both the publisher and subscribers.
Heterogeneous replication allows access to other database products.
To enable replication in SQL Server:
Using SQL Server Management Studio (#SSMS)
1. On the Publication Databases page of the Publisher Properties - <Publisher> dialog box, select the Transactional and/or Merge check box for each database you want to replicate.
2. Select Transactional to enable the database for snapshot replication.
3. Click OK.
