A Getting Started PostgreSQL Tutorial (2020-09-28)

 SQLServerCentral Newsletter for September 28, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [A Getting Started PostgreSQL Tutorial]( - [Solving the Provisioning Problem in Database Development using Clones]( - [3 Ways to Run DBCC CHECKDB Faster]( - [From the SQL Server Central Blogs - [MSSQLTips] Visual Studio 2019 Configuration for the Production DBA]( - [From the SQL Server Central Blogs - Converting a Datetime to UTC]( Question of the Day - [Limiting Access on Startup]( [Redgate SQL Prompt]( The Voice of the DBA  Daily Coping Tip Remind yourself that you are loved and worthy of love 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 Do You Know What the Settings Should Be? One of the [research areas at the Redgate Foundry]( is in estate management, trying to better understand how people manage an estate of servers. These could be physical servers you own, VMs in a hosted or cloud situation, or even a platform service like Azure SQL Database or AWS RDS. In today's world with a myriad of choices, it's easy to lose control of your estate of servers. I saw a quote recently from someone that was struggling with their estate. They said: "The moment it goes red, if you don't know what it should be, then you're clutching at straws." This particular person was struggling with rebuilding systems after a failure. If one of your VMs dies or gets removed, something that is easy to do in the cloud, do you know all the settings to rebuild it? Not just the CPU and RAM, but all the SQL Server configuration settings you might have changed? These days there are lots of database settings, which ought to be in those backups, but there are plenty of other items that could be hard to recover. Most of us don't experience large disasters in our instances, but we do get regular calls, tickets, and complaints about performance. We might even find out that settings get changed in a team environment that we are not aware were made. Monitoring systems might catch this, but not necessarily every little setting that we care about. Building your own system is complex, and more importantly, I find that ensuring all new instances and databases that get deployed are in your system is hard. I didn't think much of this project when it started, but I realized this is more of a problem for people when I attended a session at [SQL in the City London]( in 2019, where our Foundry presented on a few projects. I had assumed that most people would be thrilled with the [Spawn]( project, but most were more interested in estate management. Lots of interest in having software to ensure you not only know what your settings are, but when they might change and how to get them back. Part of building software, especially with DevOps, is ensuring you know how well it is, or isn't, performing and if the things you change are useful and valuable. Certainly this is important for those DBAs and system administrators, but I think it's also important to ensure you share those settings with developers. Having all your systems configured in the same way through the software process helps ensure more consistent performance. Some sort of estate management is important, and no matter how you might monitor systems, ensure that you are including the various configuration settings as a part of that. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [A Getting Started PostgreSQL Tutorial]( Shivayan Mukherjee from SQLServerCentral Introduction to PostgreSQL PostgreSQL is a free and general purpose open source object-relational database system that uses and extends the SQL language. Though originally designed to run on UNIX platforms, PostgreSQL is eligible to run on various platforms such as Linux, macOS, Solaris, and Windows. PostgreSQL databases provide enterprise-class database solutions and are used by […] [Solving the Provisioning Problem in Database Development using Clones]( Additional Articles from Redgate When database development is described, the details often get vague when the data gets beyond spreadsheet-size. There is 'hand-waving' talk of providing databases for each developer, but little detail of how you would provision all the databases that would be needed, at the correct version and with the correct development data, and then keep them all in sync with the source code, as developers commit changes. This article explains the requirements, and how SQL Clone can meet them. [3 Ways to Run DBCC CHECKDB Faster]( Additional Articles from Brent Ozar Unlimited Blog In my last post, I talked about how you can get 43%-67% faster backups. This time, I’m using that same SQL Server setup to discuss how throwing more CPU cores might help you check for corruption faster if you’ve already got modern fast storage. From the SQL Server Central Blogs - [[MSSQLTips] Visual Studio 2019 Configuration for the Production DBA]( gbargsley from GarryBargsley Happy Tuesday everyone… Today I am honored to have my first article published on MSSQLTips.com. As a Production SQL Server Database Administrator, you do not usually find yourself in... From the SQL Server Central Blogs - [Converting a Datetime to UTC]( Koen Verbeeck from Koen Verbeeck I was in a need of converting some datetime values (of which I know the actual timezone) to UTC dates. A quick Google search showed me that most results...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Limiting Access on Startup I want to limit access to the SQL Server on startup to just sqlcmd. How can I do this? 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) Creating a new branch in Git I am on the main branch of my git repository. $ git branch * main I want to start work on a feature branch, and need to create and checkout the feature/stevesalesorder branch. What command does this easily? Answer: git checkout -b feature/stevesalesorder Explanation: The git checkout with the -b option will create and checkout a new branch right away. Ref: git checkout - [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 - Administration [Key Lookup to Clustered Index]( - Hey all, Been a bit since I've been down this road. Clearly I'd assume this is as expected and I'm just old and rusty. Attached is the execution plan I'm looking at. I have an index seek happening with a key lookup. The key lookup is happening on the clustered index. I attempted to add […] [Restart SQL server from CMD]( - We have a issue in SQL Server 2017 (RTM-CU21) with SSISDB when a server restart happens SQL server will go into a stopped state and I cannot restart it from the configuration manager have to run the following command from cmd to start the service Net Start MSSQLSERVER /T902  how can I make this […] [Replication issue]( - my table is getting replicated from server 1 to server 2. I got a request to delete the records in table in server 1 and insert new set of records. does replication take care of the records in server 2 or should i do something to make both in sync. SQL Server 2017 - Development [Cross Apply, sub query or something else.... I\'m stuck]( - need help on a query please. given the following table; CREATE TABLE #T (CATEGORY bigint, NESTEDSETLEFT bigint, NESTEDSETRIGHT BIGINT) INSERT INTO #T ( CATEGORY, NESTEDSETLEFT, NESTEDSETRIGHT ) SELECT 5637145852,1526,1541 UNION SELECT 5637145310,392,407 UNION SELECT 5637145515,814,837 I need to be able to pass NESTEDSETLEFT and NESTEDSETRIGHT into this query without using a cursor.... SELECT CATEGORY, ITEMID […] [Cannot restore DB using T-SQL script]( - Hello, I had a database called Training_01 and did a backup of it. It is now in 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\RodierTrainingFood.bak' I did a File liste of it and saw that the logical name was OS_OS_Training_01 so it is a different name than the name of the original database. The goal is to know how […] SQL Server 2016 - Administration [SCOM/SCCM alert when Always On High Availability dashboard is green]( - Hello experts, Does anyone know why SCOM (or is it SCCM?) would send the following alert when everything is OK and green in the Always On High Availability > Show Dashboard? The Metric is: Microsoft.SQLServer.Windows.Monitor.Database.DBStatus (UnitMonitor) MSSQL on Windows: Database is in offline/recovery pending/suspect/emergency state The databases in each AG are also online and […] SQL Server 2016 - Development and T-SQL [Storing File in SQL database - Best option]( - What do I tell a .net developer is the best approach for storing various file type in a SQL db? PDF, TIF, JPG... Administration - SQL Server 2014 [Migration of Server]( - We are looking to move the SQL server to different datacenter. Since it's a standalone instance so it can be moved using V2V or other different options. However, the IP address might change after the move? I am thinking changing from configuration manager would be suffice the IP Address Listened by the SQL Server. Do […] [Transaction log used space]( - It's a little weird for me. I have a database (AdventureWorks2014; SQL Server 2014) in full recovery model. Executing this script 3 times: declare @i int = 0 declare @date datetime while @i <= 1000 begin set @date = getdate() begin tran update Production.TransactionHistory set TransactionDate = getdate(), ActualCost = @i + TransactionID where TransactionID […] SQL Server 2019 - Development [Size of table]( - Hi This probably a silly question but..... If I query the size of a table and it shows 20 gigs in size, when I do a select * from that table from another server will I bring back 20 gigs over the network? thanks in advance [Validate field in STaging before loading]( - I have a STaging table where I have defined all fields as Varchar(255), now I'm trying to load the Prod table, and one of my fields(order_open_qty) is defined as Decimal(18,10), but it fails as data from STaging fails converting Varchar to Dec. Is there any pre-validation I can run to change those bad records to […] Reporting Services [SSRS and SharePoint]( - We currently have SSRS 2017 and are looking at adding SharePoint to our environment. The question has come up as to whether we should implement SharePoint with Reporting Services or without and keep our SSRS 2017 separate as it is. I'm just starting out with Reporting Services and have no experience with SharePoint. I'm not […] [SSRS Query Designer not showing newly added measures]( - We have recently added new measures to our existing cube (that was originally developed in SSAS 2012 and then we stood up SSAS 2019) in dev and test. When I go into my SSRS project using VS 2015, then the query designer and refresh my connection to SSAS 2019, the measures that were developed in […] Analysis Services [Azure Log Analytics or SQL Profiler for AAS?]( - I think you can use both SQL Server Profiler or Azure Log Analytics for Azure Analysis Services logging. But which one is the better option? Integration Services [Running SSIS package on Always On and getting error]( - Hello, so My ssis Package i created, does 2 things: 1.it first checks if the Table exists, and if not, create it 2.checks if Store procedure exists, if not creates it using a Dynamic tsql statement so far, i made several ssis packages with it including several Execution SQL Task, and works fine, just this […]   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -