| Protocol | Default port | Sample Command | Percentage of Commands |
| SQL Database Engine | 1433 | Get-DbaDatabase | 62% |
| WS-Management | 5985 or 5986 | New-DbaClientAlias | 25% |
| SQL WMI | 135 | Enable-DbaAgHadr | 4% |
| SMB | 139 | Invoke-DbaDbLogShipping | 4% |
| SMB over IP | 445 | Get-DbaPfDataCollectorCounterSample | <1% |
Category: Uncategorized
What are few ways to fix locking and blocking in SQL Server?
- Have enough indexes to make queries go fast but not so many that they make our queries go slow. That wont fix the 5000 rows problem. SQL server will escalate to table level lock if it needs to access/lock more than 5000 rows.
- Keep your transactions as short as possible. That helps but you have modify the query to make that happen.
- Last is changing your default isolation level -> RCSI. This is a server level setting and you need account for extra tempdb usage to keep track of all those transactions.
As long as you de-dupe and eliminate the indexes, then you add indexes that are desperately needed to help the queries go faster. Just the above talked point almost eliminates blocking problem.
Typically, you see users have blocking issues, they either had no indexes whatsoever or they had dozens of indexes.
Proxy (1) is not allowed for subsystem “SSIS” and user “xxxUser”
ERROR:
Proxy (1) is not allowed for subsystem “SSIS” and user “xxxUser”. Grant permission by calling sp_grant_proxy_to_subsystem or sp_grant_login_to_proxy.
EXPLANATION:
In fact, the content of the error message is clear. The reason for the error is that Login does not have the right to use the appropriate Proxy account. I got this error when trying to create a Job.
SOLUTION:
| 1 | EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N”, @login_name=N’xxxUser’ |
Connect from R Studio to SQL server
Recently, I have had to assist one of our newly added Data Science team to connect R with SQL server. This is the first time I had to deal with R server.
One of the things I found out was that R studio did not use LDAP for authentication. After much effort, we settled with granting users to access using native SQL authentication.
To install and load the RODBC package, do the following:
- Open the RStudio console (make sure the R version is at least 3.1.3: If it isn’t, then use the updateR() function)
- Run the following command: install.packages(“RODBC”)
- Run the following command: library(RODBC)
You will need to load the following libraries:
library(rstudioapi)
libary(RODBC)
After this, run the following command to connect to the SQL server
con <- DBI::dbConnect(odbc::odbc(),
Driver = “SQLServer”,
Database = “enter_db_name“,
UID = “enter_user_name“,
PWD = rstudioapi::askforPassword(),
Server = “enter_IP_Address“,
Port = 1433)
The rstudioapi::askForPassword function will prompt the user for a password and reduce the need to store passwords in code.
Thats it! After doing this, users were able to connect to SQL server from R studio and run queries.
Credit: https://support.rstudio.com/hc/en-us/articles/214510788-Setting-up-R-to-connect-to-SQL-Server-
Database Blog 