Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Daily Coping Tip

Appreciate nature around you, wherever you are

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

Removing SA from Azure SQL Database

Due to a email sending issue, this editorial from last week is being resent in the newsletter.

One of the recommendations from Microsoft SQL Server is to use Windows authentication. This has been in the docs for years, and I've heard many MS consultants and employees note this. Many customers and clients have tried to use Windows Authentication only, but often in a cross platform environment with Java or Linux clients, one usually has needed SQL authentication with a user and password. Client libraries have been enhanced so this isn't necessary, but still some people prefer SQL authentication, especially with clients outside their organization. It's simple, easy, and developers can make it work in seconds.

With Azure SQL Databases, some companies defaulted to a username and password, as their Active Directory (AD) wasn't extended to Azure. That has become easier to do, and many people are taking advantage of it. In fact, some customers are so integrated, they want to do away with usernames and passwords in Azure.

Microsoft has listened, and is giving them the option. The feature is in preview, but if you enable this, SQL auth is turned off, which means whatever administrative account you set up for the server with a name and password will not work. That's essentially the "sa" account, though with your own custom name.

While this feature won't be useful for everyone, it's a good option to have. As more companies look to tighten security and limit the attack surface area, being able to make this choice is important. It's also something that architects and administrators should be aware of and consider in their decisions on how to implement applications in Azure.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
 Featured Contents

Reading Stored Procedure Output Parameters in Azure Data Factory

diponkar.paul from SQLServerCentral

This article will help you to work with Store Procedure with output parameters in Azure data factory.

3 steps to get started with Database DevOps

Additional Articles from Redgate

Once you’ve committed to changing your culture in order to automate your database deployments, what’s next? You’ve already done the hard part, making the decision to shift the culture. In this blogpost, Grant Fritchey explores three steps you can take next to begin your Database DevOps journey.

Where I See Databases Going in the Next 10 Years

Additional Articles from Brent Ozar Unlimited Blog

This month, to mark the 20th anniversary of BrentOzar.com, Brent steps back and looks at the big picture.

From the SQL Server Central Blogs - Issues When Using Temporary Tables in Nested Stored Procedures

John Morehouse from John Morehouse | Sqlrus.com

SQL Server offers a lot of flexibility when working with objects like stored procedures and temporary tables.  One of the capabilities is to allow for stored procedures to call...

From the SQL Server Central Blogs - Control Flow Limitations in Data Factory

Meagan Longoria from Data Savvy

Control Flow activities in Data Factory involve orchestration of pipeline activities including chaining activities in a sequence, branching, defining parameters at the pipeline level, and passing arguments while invoking...

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

LocalDB Install

I have a brand new laptop and I have only installed SQL Server 2019 Developer Edition. I want to work with localdb for a project. How do I install it?

Think you know the answer? Click here, and find out if you are right.

 

 

 Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Replacing Startup Options

I have an old SQL Server 2012 instance that I am upgrading to SQL Server 2019. This is an in-place upgrade, and I see that there is a startup parameter: -h. This was used to reserve memory for Hot Add. What should I do with this parameter after the upgrade?

Answer: Remove this as it no longer applies in SQL Server 2019

Explanation: This parameter reserved memory under AWE for hot-add, but this is for 32 bit applications. SQL Server 2019 is only available as a 64 bit application. Ref: Database Engine Service Startup Options - https://docs.microsoft.com/en-us/previous-versions/sql/2014/database-engine/configure-windows/database-engine-service-startup-options?view=sql-server-2014&preserve-view=true

Discuss this question and answer on the forums

 

 

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.


SQL Server 2017 - Development
Dynamic parameter transfer in msdb.dbo.sp_send_dbmail - Hi all. I have a problem with email sending via SQL Server. I need to count the number of minutes in the previous month of the running year. The problem is that the e-mail should be sent every month and it should be determined dynamically (in April it should be for January, February, March). In […]
SQL Server 2016 - Administration
SQL Server 2016 Encrypted Connections - Good day all, I am assisting our DBA team by conducting some investigations into making use of SQL Server encrypted connections for one of our older (VB6-based, yes I know!) applications.  I have successfully applied a certificate to the SQL server, restarted the SQL Server service and all looks well.  Running the following shows me […]
replication error - Job: expired subscriptoin clean up job failed due to the Could not find server ' VEN' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. [SQLSTATE 42000] (Error 7202). The step failed. I checked in linked server and did not find […]
SQL Server 2016 - Development and T-SQL
DATEDIFF() for times over a day long - I've got a T-SQL statement which I use for comparing the duration of an SSIS package against previous executions (to see if they're slowing down) but it doesn't work if the duration is longer than 24 hours. The problematic SQL is: -- THIS DOES NOT WORK DECLARE @today [varchar](12) = '44:10:27.766', -- OVER ONE DAY […]
Copy data from Source Sql Server to another destination Sql Server DB Table - Hi, I want to copy from source sql server table to destination sql server table. Requirement: Source Sql Server EU551 and DB Table tblEMP Destination Sql Server EU661 and DB Table tblEMP Both source and destination db table tblEMP have same schema. I want to check if source table has the data for current day […]
Administration - SQL Server 2014
Encrypted Database copying sometimes works - I have had a process to move a production database to a test server going for a few years now. The developers wanted a second copy of the same database created so we setup a new database and then restore the same backup to the new database. After the restore sometimes it works fine and […]
Development - SQL Server 2014
Problem when copying from a table into another table - Hi All.I have a wired situation here. I have 2 tables: table 1 with let say 5 records on it and an empty table2 (same schema), Trying to copy records from table1 into table2. When my script says(i am trying to make my script simple) with x as (select * from table1 ) insert into […]
SQL 2012 - General
How to get spare no from categories 1 table where not exist per same spare no on - How to get spare no from categories 1 table where not exist per same spare no on table categories 2? so i need to make select statement query select spare no from table categories 1 that have different categories on table categories 2 per same spare no as example spare no 1350 have category 5902 […]
How to get Part Id that have part level 0 and not have map from ? - How to get Part Id that have part level 0 and not have map from ? I work on SQL server 2012 I face issue I can't get Parts that have map to and not have map from for part level 0 so Firstly I get parts that have part level 0 then secondly if […]
SQL Server 2019 - Administration
SQL Server 2008 R2 Audit Log - In my sql server instance I renamed 'sa' account and disabled. I confirmed no sa account.  I configured server side audit log and I can see some sa activities. How is it possible?  
There and back again: From a partition number to a filegroup and vice versa -   I've been trying to navigate the DMVs concerning partitions and filegroups and find my way between them. I might have a partition number and want the filegroup name(s) (and ultimately the files in the group). Or, I might have a filegroup or filename and want the partition(s) that it holds. Here's my test case: USE master; DROP DATABASE IF EXISTS TestParts; CREATE DATABASE TestParts ALTER DATABASE TestParts ADD FILEGROUP FG1 ALTER DATABASE TestParts ADD FILEGROUP FG2 ALTER DATABASE TestParts ADD FILEGROUP FG3 ALTER DATABASE TestParts ADD FILE (NAME=File1, FILENAME = 'C:\temp\File1.ndf', SIZE = 1MB) TO FILEGROUP FG1 ALTER DATABASE TestParts ADD FILE (NAME=File2, FILENAME = 'C:\temp\File2.ndf', SIZE = 1MB) TO FILEGROUP FG2 ALTER DATABASE TestParts ADD FILE (NAME=File3, FILENAME = 'C:\temp\File3.ndf', SIZE = 1MB) TO FILEGROUP FG3 Now, create a partitioned table: USE TestParts […]
SQL Server 2019 - Development
filter by WHERE has no effect - SQL WHERE clause not working trying to filter by date "WHERE [dtmDocDate] >= '2020-09-01'" has no effect Issue is related to a fairly complex query that includes: DECLARE @orderByClause nvarchar(MAX) = '' DECLARE @whereClause nvarchar(MAX) DECLARE @tableHasIdentity bit DECLARE @sql nvarchar(MAX) DECLARE @columnList nvarchar(MAX) DECLARE @valueList nvarchar(MAX) DECLARE @Column_ID int DECLARE @Column_Name varchar(128) DECLARE @Data_Type […]
How to ALTER COLUMN without SELECT and UPDATE permissions - I try to ALTER a column by changing the datatype from integer to NUMERIC(35,10).  IMPORTANT : The user that execute the DDL has db_ddladmin role and that's allright because we only want it to do DDL instructions and no be able to extract data for security reasons. With this query : ALTER TABLE tablename ALTER […]
must be drop table ? - Hello, i'm an Italian developer (excuse me for my english). I have a Store Procedure like this BEGIN TRY BEGIN TRANSACTION myTransaction DECLARE @TempMrc TABLE (idServizio int, idRgp int, Salita bit, Data Date) DECLARE @TempReport TABLE (Data Date, Salita smallint, Discesa smallint) DECLARE @crs CURSOR SET @crs = CURSOR LOCAL FAST_FORWARD READ_ONLY FOR .... OPEN […]
Analysis Services
Azure Analysis Services Memory Usage - Hi there. I'm trying to identify what users or processes/queries are consuming the cube server memory. An example is seen here from the azure portal metrics graph. I know you can do the 'discover sessions' in management studio but all those metrics don't particularly help much. Does anyone know a good tool that can provide […]
 

 

RSS FeedTwitter

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -