topic Re: Refresh SQL Server Database in Geodatabase Questions

Refresh SQL Server Database

APANetworks — Mon, 01 Aug 2016 07:11:01 GMT

We have a production SQL Server SDE Database called APA_GIS_PROD and a test environment called APA_GIS_UAT. The data is edited in ArcGIS 10.2.1 with ArcFM. Our production SQL Server is using SQL Server 2008 and the UAT using SQL Server 2014.

We want to refresh the UAT environment with the data from production but have just discovered that we can’t just copy the tables across using SQL Server tools as the database names need to stay the same.

Is there a way to copy all the data from our production environment into the test environment keeping the database names separate?

Re: Refresh SQL Server Database

JakeSkinner — Mon, 01 Aug 2016 15:12:34 GMT

You can accomplish this using geodatabase replication:

Working with geodatabase replicas—ArcGIS Help | ArcGIS for Desktop

For example, you can have a one-way replica from your production to UAT geodatabase.

Re: Refresh SQL Server Database

BirajaNayak — Tue, 02 Aug 2016 01:28:21 GMT

Hi APA,

Can you please elaborate the requirement.

When you say refresh data from production to UAT, do you mean update everyday edits from production to UAT. If that is the requirement then Jake already provided solution using Geodatabase Replication.

Thanks,

Biraja

Re: Refresh SQL Server Database

APANetworks — Tue, 02 Aug 2016 01:36:31 GMT

Thank you for your reply. At this stage we are not wanting the databases linked through replication, although that might end up being the ultimate result, but we want to be able to test business processes in our UAT environment using a copy of the latest data. The data in our UAt environment is getting out dated so I wanted to copy the information from our production database into the test database. We discovered that SDE databases have to keep their original names if you copy the whole database i.e. DatabaseProd can be called DatabaseUAT as their are internal pointers within SDE that mess with the connection. We don't really want a database sitting in UAT called DatabaseProd as this could be confusing.

Re: Refresh SQL Server Database

APANetworks — Tue, 02 Aug 2016 01:38:17 GMT

Thanks Jake, We will ultimate head down the replication path but at this stage I just want to copy all the data that is in PROD into UAT but have discovered that SDE databases can't be copied using a different name and although I could have a database call DatabaseProd in UAT it could get confusing.

Re: Refresh SQL Server Database

BirajaNayak — Tue, 02 Aug 2016 01:39:43 GMT

Hi APA,

In that case, create a new sqlserver instance in UAT and restore backup from production or copy data using sqlserver tools.

Thanks,

Biraja

Re: Refresh SQL Server Database

APANetworks — Tue, 02 Aug 2016 01:42:37 GMT

Hello Biraja, that is what we tried but unfortunately there is an issue with SDE databases changing names, so using SQL Server tools move the data but we are not able to connect to it any more.

Re: Refresh SQL Server Database

BirajaNayak — Tue, 02 Aug 2016 01:55:18 GMT

Hi APA,

As you can not rename geodatabase name to access by SDE, hence I suggested to create another sqlserver instance and create same name geodatabase in UAT. So basically UAT will have 2 separate sqlserver instance and one has UAT instance and another will have production copy geodatabase.

Thanks,

Biraja

Re: Refresh SQL Server Database

APANetworks — Tue, 02 Aug 2016 04:58:19 GMT

Thanks Biraja,

Unfortunately we don't want two UAT databases, but just want the PROD one with a different name sitting in UAT.

Regards

Dean

Re: Refresh SQL Server Database

JakeSkinner — Tue, 02 Aug 2016 11:43:09 GMT

You can create your other database at UAT and then replicate the entire PROD geodatabase. When replicating an entire geodatabase, it's best to use the option 'Register existing data' when creating the replica:

For example, you could perform the following steps:

Export the PROD geodatabase to an XML
Import the XML to the geodatabase at UAT
Create the replica using the option to 'register existing data'